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

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

sunbaj — Sat, 16 Nov 2024 07:22:13 GMT

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 !!!

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

sunbaj — Thu, 01 Nov 2018 15:35:36 GMT

Any help would be much appreciated !!!!

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

Anonymous — Thu, 01 Nov 2018 18:47:55 GMT

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

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

sunbaj — Fri, 02 Nov 2018 05:02:09 GMT

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

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

Anonymous — Fri, 02 Nov 2018 07:16:38 GMT

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

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

sunbaj — Fri, 02 Nov 2018 07:42:05 GMT

Attached as asked for.

Thanks

Data_Load.zip

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

sunbaj — Fri, 02 Nov 2018 07:42:32 GMT

The Source

Anuj Kumar.csv

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

Anonymous — Fri, 02 Nov 2018 09:14:40 GMT

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