[resolved] tfileinputexcel number format exception on excel2007

One Star

[resolved] tfileinputexcel number format exception on excel2007

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

Accepted Solutions
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

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

All Replies
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

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
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

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
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

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

Re: [resolved] tfileinputexcel number format exception on excel2007

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.
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

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
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

Any updates to this?
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

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
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

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
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

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
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

Bug Fixed in release 5.0.2.
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

One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

Hi Fabio
Thank you for your feedback.
Regards,
Pedro
One Star

Re: [resolved] tfileinputexcel number format exception on excel2007

Hi fnds,
I wanna use talend to execute some query like db.emp.find({}).count(); and to return the result of count.
I tried to use tMongoRow and have given the query in execute command text box.
the job runs fine but i m unable to fetch the result in the connected component (tjava)
please help to retrieve the value.
or guide me in some other approach which can let me to perform the desired operation.
Thanks in advance,
karthi.