Leading zeros not retained while reading ms excel with tfileinputexcel

One Star

Leading zeros not retained while reading ms excel with tfileinputexcel

Hi -
I have been facing the problem while reading the excel cell formatted leading zero values.
In Detail,
ColA having the formatted values like 003, 001, 002. While reading the out put is 3, 1, 2.
I can handle leading zeros with expressions by adding 0's in Tmap 
StringHandling.RIGHT(("000"+row1.column),3)
But the Problem is If I handle like this always leading zeros wil be appended to the column and In some case if the same column comes with out leading zeros means that 3, 1, 2 I have to read same as input 3, 1,2 only. I.e.,If leading zero's are there I need to show leading zero's other wise not required.
Please help this scenario how to read the excel cell with defined format in Talend.
Your kind help and ideas would be highly appreciated.
Thanks-
Raghav
Seventeen Stars

Re: Leading zeros not retained while reading ms excel with tfileinputexcel

Why do you not read the cell content as String? Leading zeros always get lost if you read numbers.
One Star

Re: Leading zeros not retained while reading ms excel with tfileinputexcel

Hi-
Thanks for your response.
I am reading the cell content as String only. But the problem is, If the input excel cell value is formatted value then leading zeros are missing. And If the input excel cell value is String then I can able to read the leading zeros.
Please see the below attachment first 3 loan number's zip column values formatted with leading zeros and which are missing the leading zeros while reading with tfileinputexcel as String. loan Number 104, 105, 106 zip values are Strings in Input file So I can read with leading zeros.  

TOS version: Talend 5.6.2
Note: I am facing this problem only with the .xlsx format files only. And able to read the leading zeros from the .xls files with data type String.

Kind Regards-
Raghav K
Five Stars

Re: Leading zeros not retained while reading ms excel with tfileinputexcel

I don't think Talend is seeing the cell formatting.
In an ideal world, you're spreadsheets would be consistent and a String is a String, not a formatted number.
Spreadsheets will always be an issue where there is user input, so regardless of how your read this file, you need to do your own validation and auto-correction where possible.
As suggested, I would read a String where I can.
Validate ZIP. If it needs zeros prepended, then do it.
One Star

Re: Leading zeros not retained while reading ms excel with tfileinputexcel

Thanks for your Response.
I can append the leading zeros by writing the expression in Tmap and can able to load in the target. As I need to show the error excel out put with the same  as input record, I want to read the input records as it is with String data type. I Can not restrict the client to provide the input in String format only. Some times we are getting the Data in formatted as discussed in previous conversation.
Why Talend able to read the formatted leading zeros for the .xls format and why not for .xlsx??
Kind Regards,
Raghav K
Five Stars

Re: Leading zeros not retained while reading ms excel with tfileinputexcel

If you're dealing with user generated Spreadsheets, I would recommend reading as String, performing your own validation and auto-correcting where you can.
If this is user generated data, there is no point in worry about why the formatting is not visible as there will be many reasons why the data is not as you might hope.