Problem importing Excel strings

One Star

Problem importing Excel strings

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

Re: Problem importing Excel strings

Should that column not be a float as well?
One Star

Re: Problem importing Excel strings

I cant' reproduce this. Can you post a screenshot of your tFileINputExcel component configuration + advanced options too + a sample of the Excel file ?
One Star

Re: Problem importing Excel strings

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

Re: Problem importing Excel strings

Hi David
What version of Talend are you using?
is this a ".xls" or ".xlsx" spreadsheet?
Have you changed any advanced settings in this tFileInputExcel component?
Regards,
Brandon
One Star

Re: Problem importing Excel strings

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.

Re: Problem importing Excel strings

Hi David
I can't seem to reproduce the error,
could you upload that file with fake data onto a file hosting site and give us the download url
Regards,
Brandon
One Star

Re: Problem importing Excel strings

According to your image column 61 is PBBCredit$. The error relates to BBTotalCredit_ .
can you show the data that is in BBTotalCredit_ and which column is it?
One Star

Re: Problem importing Excel strings

Hi Janhess,
Sorry I gave you a screenshot of column 50 of the sheet that I incorrectly labeled 61 (there are 121 columns in total).
Below is a screenshot of the correct (troublesome) column 61.

Re: Problem importing Excel strings

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

Re: Problem importing Excel strings

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

Re: Problem importing Excel strings

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 ?
Seven Stars

Re: Problem importing Excel strings

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.