Problem with reading Excel file having cell value as more than 15 digits number

Four Stars

Problem with reading Excel file having cell value as more than 15 digits number

I'm using Talend studio for big data 7.1 (open source) version. Facing problem related to read excel file which is having cell value as more than 15 digits. Actually, it is well known issue of excel, if cell format is Number and the value exceeds more than 15 digits, it converts the extra digits to 'Zero'.

 

In the image, excel file value is 17 digits. A, B and C columns are in cell format Text, General and Number respectively.

17digits.png

We are processing Number format value through Talend Studio.

In Talend, using Excel File Connector, it is showing the preview of the above image as:

talend_preview.png

It is showing the data discrepancy in Number Formatted column of excel file and Talend preview. Both values i.e Number format in excel and Number format in preview are different.

Though the actual value changes in excel itself, but why Talend studio does not process the same value as excel file?

 

Please someone help me with this.

Thanks. Smiley Happy

 

 

Employee

Re: Problem with reading Excel file having cell value as more than 15 digits number

Hi,

 

     Excel will format the numbers during display but if you open the same file with a simple text editor like notepad++, it will show the actual values present in the file.

 

     Talend always read the files like a normal text editor (means always read the actual values stored in file) instead of data shown in Excel in auto-formatted manner. So I would say Talend is working as expected.Please also make sure that you are allocating right data types in Talend which will hold the values of the size mentioned in picture.

 

    If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome :-)

 

Warm Regards,

 

Nikhil Thampi

 


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Four Stars

Re: Problem with reading Excel file having cell value as more than 15 digits number

Thanks for the reply @nikhilthampi

 

In excel file, Number value up to 15 digits is fine. But value more than 15 digits showing mismatch value in excel itself and in Talend also.

As i mentioned in image, 16th and 17th digits are converted to zero and while processing through Talend, Talend assuming some random value for converted zeros i.e 16th and 17th digit, which is exactly different from the actual value's 16th and 17th digits.

 

Please refer the both images and you can verify with counting the digits. It is where the actual problem i'm facing.

Employee

Re: Problem with reading Excel file having cell value as more than 15 digits number

Hi,

 

    Here are my quick queries?

 

a) What is the data type you are using to read the data using Talend?

b) Could you please paste a screenshot of the same data (which you have shown earlier in Excel and Talend) using Notepad++?

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Four Stars

Re: Problem with reading Excel file having cell value as more than 15 digits number

Hello,

 

My answers are as below,

a) For column Actual value, General value and Number value datatypes are String, Long, Long respectively.

 

b) 1.png

 

Screenshot for Talend preview for same values.

3.png

 

Thanks.

Employee

Re: Problem with reading Excel file having cell value as more than 15 digits number

Hi,

 

    Could you please read them as String itself from source and do a conversion use tConverttype later in your job flow and let us know the results?

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Four Stars

Re: Problem with reading Excel file having cell value as more than 15 digits number

Hi @nikhilthampi,

 

I've tried the solution as you said, but it didn't work. It is still showing different values.

 

If you are also using Talend big data studio, could you please try this type of problem by your side? It will not take too much time.

Steps:

  1. Create new excel sheet. In first column type 5 numeric value with 18 digits.
  2. in Talend job, create new excel connector to fetch same file and see the preview. 

If it is resolved by your side, it'll definitely very helpful for all.

 

Thanks and regards,

Amol Pawar

What’s New for Talend Spring ’19

Join us live for a sneak peek!

Sign up now

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download