Four Stars

Excel Date Import

Hello,

 

I'm trying to process an Excel file (2007) in Talend. This file has a few dates, and I don't understand how to manage them correctly. I use a rFileInputExcel. The dates included range from 1930 to 2060 (so both 20th and 21st century).

For example, a date is "31/03/2017" in Excel. When checking the cell in Excel, it's a date.

If I import it as a String, and try to display it later in a tLogRow, it's displayed as "31/03/17". This cause an issue since the century information is missing.

If I import it as a Date, with the format "dd/MM/yyyy", the display later in tLogRow is 03/07/0019 which is definitely not my 31/03/2017.

How could solve this issue ?

  • Data Integration
5 REPLIES
Six Stars

Re: Excel Date Import

Hello

 

Rather than converting to date just pass it as is in string format. it should resolved your issue.

 

Thanks!

Four Stars

Re: Excel Date Import

Hello. Thanks for your answer. However, when I import as a String, the information about the century is lost, while I need it for further processing Smiley Sad

Six Stars

Re: Excel Date Import

Hello ,
Can you please share some examples with screenshot?
Thanks!
Four Stars

Re: Excel Date Import

Sure. In attachment:

- excelData: the kind of data I have in excel. You can note that there is 31/01/2050 in the second column, first line, and 31/01/1950 in the first column, third line.

- excelImport: from the tInputExcel

- job: the talendJob used for the test

- outputData: the output of the logRow. Both 31/01/2050 and 31/01/1950 are converted in 1/31/50, so the information of the century is lost.

Six Stars

Re: Excel Date Import

Hello

 

can you try to read the source data using Data viewer in talend? can you please share the screenshot for that too ?

 

Thanks!

Irshad