Hi,
this problem has frustrated me for a few days so I am asking for your help.
I have a Excel spreadsheet that I am importing using a tFileInputExcel. I am importing the cells of the spreadsheet as string and floats. The columns import fine until column 61, which is imported as a string (I am actually not interested in this column - I ignore the contents). On the reject output of the tFileInputExcel component I get the error:
For input string: ""$"0" - Line: 1 column: BBTotalCredit_ (No. 61)
I get this error for all subsequent rows for column 61.
I should note that I get this spreadsheet each month and every one has the same problem and I don't want to ask them to change the format.
Below is a screenshot of the troublesome Excel column and the Excel metadata for column 61. Any ideas? Is there a way I could ignore this column?
Thanks,
David
Brandon,
thanks for looking into this. I changed the column 61 type from sting to float then back to string. I was expecting to get a different error but it was the same.
As a float I got the error:
For input string: ""$"0" - Line: 1 column: BBTotalCredit_ (No. 61)
As a string I got the error:
For input string: ""$"0" - Line: 1 column: BBTotalCredit_ (No. 61)
Maybe something is corrupted. Maybe I should delete the tFileInputExcel component and replace. I will try that tomorrow.
It is time for me to log-off tonight (10:30 PM in Australia now).
cheers,
David
Maybe there's a column that has been swapped somewhere, it can get pretty confusing with so many. Try with a simplified version of this file with only one column ?
David, this error is almost certainly caused by trying to read a money-formatted column as a numeric Talend data type (int, float, BigDecimal). Talend cannot handle the dollar-sign in converting the String returned from Excel to a numeric.
The thing to remember is that the column names in Talend have no automatic relationship to the column names in the source data. Talend simply assigns the first column read from the source to the first column in the Talend schema etc.. Thus it's possible for a mistake to creep in - a mismatch between the columns in the source and the columns in Talend - meaning that Talend is trying to treat data from column D in the source as belonging to column C in the schema where the data might actually be incompatible with the type of column C.
In your reject flow from tFileInputExcel, you should be able to see that only columns up to the problem one are populated. This will allow you to compare back to the source and ensure that the correct data is being placed in each column of the Talend schema.
