Issue with converting String as datetime in excel to datetime in Talend

[sunbaj]

Hello !!!! I have spent enough days trying to convert a datetime as string from an excel and convert it into a datetime in talend and then write it into a notepad.

This is how my data in the source which is a CSV file is 

08-01-2018  11:44

I read the data as string and then use TalendDate.parseDate("dd-MM-yyyy HH:mm",Var.var1)  function to convert it into a datetime variable. 

However when I write the output in a notepad this is what I get

01-08-2018 11:01

Not sure why this is happening ?

Any help please !!!

[sunbaj]

Any help would be much appreciated !!!!

[Anonymous]

Hi,

     I hope you are reading the files using the MS Excel and it will auto format the dates according to the settings. 

     Most probably your input file will be in US settings where the date format will be MM-dd-yyyy. I would always recommend to see the file in a normal text editor like Notepad++ to understand the data format of the date columns.

     If you are still stuck, could you also share the sample input files and job flow + component details screen shots so that we can do detailed analysis?

     If the answer has helped to identify and resolve the problem, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi 

[sunbaj]

Hi Thanks for the reply. I tried to validate all that you have said and as per me I am not seeing any issues. I checked it in Notepad++ and there also the  format is as I mentioned above. I am attaching screen print of the excel and also the same when viewed in Notepad++.

Also I ran the code in Debug mode and you can see that row2 shows IO_Time as Augst 1 11:44

[Anonymous]

Hi,

     Could you please export your current job as a zip file along with your sample input file so that I can examine it from my end?

Warm Regards,

Nikhil Thampi

[sunbaj]

Attached as asked for.

Thanks

Data_Load.zip

[sunbaj]

The Source

Anuj Kumar.csv

[Anonymous · Accepted solution]

Hi,

      I ran your job with the data file and I got the data in sync at both input and output. The only minor issue I noticed was that the minute part was wrong in the output schema of tmap. I have modified it from MM to mm to pick minute instead of earlier value of Month (as shown below).

Below are the screenshots of Input and Output and they are in sync.

Input file

Console Output

Output Console

    So could you please make the above change mentioned in your tmap (allow the change to propagate to other components), then, things should work fine.

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

Warm Regards,

Nikhil Thampi