topic Re: Unparseable number error for tFileInputExcel component in Talend Studio

Unparseable number error for tFileInputExcel component

Anonymous — Thu, 16 May 2013 14:37:14 GMT

Hi all,
We have an excel spreadsheet which the tFileInputExcel component is reading from. There are cells in the spreadsheet that contain the following custom formatting:
#,##0.0,_);(#,##0.0,);"-"
The part to note in the above formatting string is the "-". When a value of 0 is detected a dash is displayed in the cell.
In our job when the tFileInputExcel component tries to read a cell with this formatting that contains the dash the following error occurs:
Exception in component tFileInputExcel_3
java.text.ParseException: Unparseable number: ""-""
at java.text.NumberFormat.parse(Unknown Source)
at emt.loadfinancereportspreadsheet_0_1.LoadFinanceReportSpreadsheet.tFileInputExcel_3Process(LoadFinanceReportSpreadsheet.java:9807)
at...
From viewing line 9807 I can see that the value of .getCellType() is CELL_TYPE_FORMULA (as I would have expected) and that the value of .getCachedFormulaResultType() is CELL_TYPE_NUMERIC (which I believe is incorrect due to the cell possibly containing a string).
Is this a bug?
This error was encountered in Talend Open Studio for Data Integration version 5.1.2.
Thanks,
Michael

Re: Unparseable number error for tFileInputExcel component

Anonymous — Fri, 17 May 2013 04:13:45 GMT

Hi,
How to set your schema? Is it a String type? Screenshot of schema and work flow will be appreciated.
Best regards
Sabrina

Re: Unparseable number error for tFileInputExcel component

Anonymous — Fri, 17 May 2013 09:29:06 GMT

Hi Sabrina,
Thanks for the reply.
Please find attached four images demonstrating my situation.
Image 1 shows my workflow and the error that is produced.
Image 2 shows my schema and that it is set to read it as a string.
Image 3 displays the generated code that fails. On line 1125 the cell is successfully identified as a formula. The code on lines 1126 and 1127 identify the result type as being numeric and then on line 1142 you assume you can convert the value to a number. This is the line that fails as it tries to convert "-" to a number.
Image 4 shows an example of the spreadsheet the job is trying to read. If you can reproduce this on your end you should be able to replicate the error. The formatting is only set on cell A1. Cell A1 has a value of =B1 and cell B1 has a value of 0.
Regards,
Michael

Re: Unparseable number error for tFileInputExcel component

Anonymous — Wed, 22 May 2013 15:06:20 GMT

Hi,
Is there any update on my issue? If my last post wasn't clear I can try to be clearer.
Thanks,
Michael

Re: Unparseable number error for tFileInputExcel component

Anonymous — Wed, 22 May 2013 19:48:51 GMT

Your picture shows the problem in the generated code. The format method is not needed here and this format method is what causes the problem.
As far as Talend has fixed this issue you can try the component tFileExcelSheetInput from talend exchange. This component does not have this bug. It would be great to know if it helps you to solve your problem.

Re: Unparseable number error for tFileInputExcel component

Anonymous — Fri, 24 May 2013 13:36:29 GMT

Hi
Alternatively you can upgrade your talend version, I can not reproduce the problem in 5.3.0
Regards,
Brandon