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
Thanks and a good suggestion. I have tried float and int types in desperation. All of these come up with errors. I don't need this column and ideally I would not import this troublesome column (and there may be a way to do this but i don't know how). Thanks for the idea and i will try any ideas. David
Wow, I am overwhelmed by everyone's enthusiasm to help a friend in need! It is an .xls version Excel sheet and I am using Talend 5.1.1 Below are screenshots of the tFIleInputExcel component. I can send someone a sample spreadsheet, but i don't know how to send via the forum. Thanks again.
Hi david I can reproduce the problem if I read that column as type float: ||For input string: "$0" - Line: 0 column: test (No. 61) ||For input string: "$0" - Line: 1 column: test (No. 61) ||For input string: "$0" - Line: 2 column: test (No. 61) make sure this column is set as String in the metadata Regards, Brandon
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
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.