One Star

Unparseable number error for tFileInputExcel component

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
5 REPLIES
Moderator

Re: Unparseable number error for tFileInputExcel component

Hi,
How to set your schema? Is it a String type? Screenshot of schema and work flow will be appreciated.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Unparseable number error for tFileInputExcel component

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

Re: Unparseable number error for tFileInputExcel component

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

Re: Unparseable number error for tFileInputExcel component

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

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