topic Re: [resolved] tfileinputexcel number format exception on excel2007 in Talend Studio

[resolved] tfileinputexcel number format exception on excel2007

Anonymous — Sat, 16 Nov 2024 12:26:39 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Wed, 18 Jan 2012 03:22:11 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Wed, 18 Jan 2012 09:26:31 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Wed, 18 Jan 2012 10:02:29 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Swell — Fri, 20 Jan 2012 16:59:16 GMT

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.

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Sat, 21 Jan 2012 17:09:44 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Swell — Thu, 02 Feb 2012 22:21:06 GMT

Any updates to this?

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Fri, 03 Feb 2012 00:18:02 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Fri, 03 Feb 2012 06:37:46 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Fri, 03 Feb 2012 23:21:57 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Fri, 10 Feb 2012 13:57:33 GMT

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

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Mon, 13 Feb 2012 01:34:03 GMT

Hi Fabio
Thank you for your feedback.
Regards,
Pedro

Re: [resolved] tfileinputexcel number format exception on excel2007

Anonymous — Sat, 28 Mar 2015 13:11:35 GMT

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.