[resolved] tfileinputexcel number format exception on excel2007

[Anonymous]

Hi All
I just upgraded TOS from version 4.2.3 to version 5.0.1 and after the upgrade I got an error in tfileinputexcel component. The component reads some numeric columns which contain a currency value: all these columns are set in the schema to be read as string, without any particular format.
In previous version everything was working properly, while in the last version at run time I get the following exception:

Exception in component tFileInputExcel_1
java.text.ParseException: Unparseable number: "? 7.500,00"
	at java.text.NumberFormat.parse(Unknown Source)

at the follow piece of code (extracts from the component):

case org.apache.poi.ss.usermodel.Cell.CELL_TYPE_NUMERIC:
 if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(cell_tFileInputExcel_1)) {
	temp_row_tFileInputExcel_1 = cell_tFileInputExcel_1.getDateCellValue().toString();
 } else {
	temp_row_tFileInputExcel_1 = new BigDecimal(numberFormat_tFileInputExcel_1
                                                              .parse(dataFormat_tFileInputExcel_1
                                                              .formatCellValue(cell_tFileInputExcel_1))
                                                              .toString()).toPlainString;
 }

That is, the cell format (and not the real numeric value) causes the exception (in particular the ? symbol).
I have to check the Excel2007 option because this is the input format of the excel file, consequently some other advanced options are hidden and cannot be used.
Is there any way to get real value of a cell of an xlsx with tfileinputexcel? Or am I doing simply some mistake?
Thank you in advance for any advice.
Regards,
Fabio

[Anonymous]

Hi Fabio
Could you show me the settings and schema of the tFileInputExcel?
I have reproduced this job in TOS5.0.1 and didn't get any error.
So i think i must miss something.
Regards,
Pedro

[Anonymous]

Hi Pedro
the first screenshot shows the excel I used as input (I didn't find any way to attach the file itself). The last three screenshots show settings and schema I used to read the file.
Thank you.
Best regards,
Fabio

[Anonymous]

Hi
This may be a compatibility issue.
Could you email me and attach the export job?
I need to confirm it.
Regards,
Pedro

[Swell]

I'm having the EXACT same issue:
Data being read from my .xlsx spreadsheet contains a column with currency data. The schema is set to read the data as type String, but generates an error: Exception in component tFileInputExcel_3
java.text.ParseException: Unparseable number: "$500.00"
at java.text.NumberFormat.parse(Unknown Source)...
The job was created in TOS 4.1 and has been run successfully many times. I also just installed TOS for Data Integration 5.0.1. and am trying to run this job from the new version.

[Anonymous]

Thank you Pedro.
I will be able to send you the job on Monday morning!
Regards,
Fabio

Hi
This may be a compatibility issue.
Could you email me and attach the export job?
I need to confirm it.
Regards,
Pedro

[Swell]

Any updates to this?

[Anonymous]

Dear All,
I am really really sorry for the incredible delay, the last few days have been really hard. I just emailed to Pedro the required attachment. Hope it will help for finding a solution to the exception.
Regards,
Fabio

[Anonymous]

Hi Fabio
Thank for your feedback.
I have talked with my coworkers about this issue. We need a little time to fix it.
Please report this bug at BugTracker.
Regards,
Pedro

[Anonymous · Accepted solution]

Ok. Thank you Pedro!
I reported the bug with the following issue: http://jira.talendforge.org/browse/TDI-19546.
BRs,
Fabio

Hi Fabio
Thank for your feedback.
I have talked with my coworkers about this issue. We need a little time to fix it.
Please report this bug at BugTracker.
Regards,
Pedro