Convert String to ISO date format in Talend

Question

I have Excel data and trying to insert the data into MongoDB using Talend Big Data for Open Studio. This is my job,

tFileInputExcel --> tMap --> tMongoDBOutput

In excel sheet, i have a date value column in this format 7/13/2017(MM/dd/yyyy) as string type and I am trying to insert this column value as ISO format ISODate("2017-07-13T00:00:00.000Z") in MongoDB.

This is my Job:
tFileInputExcel:

tMap:

tMongoDBOutput:

When execute this job, I'm getting the below error. Error:

When i change the parse format like this TalendDate.parseDate("MM/dd/yyyy",row1.ClosingDate) , I'm getting SimpleDateFormat error. Simple Date Format Error How to resolve this issue?

Théo Capdet · Answer 1 · 2017-12-29T15:06:55.833

0

you can do simply if you mongodb column schema is date:

TalendDate.parseDate("MM/dd/yyyy",row3.newColumn)

That will automatically convert the date in the date model that your mongoDB column have.

You can change in your schema in Talend the date Model like "yyyy-MM-dd'T'HH:mm:ss.SSS'Z'".

edited Dec 29 '17 at 15:06

answered Dec 29 '17 at 10:59

Théo Capdet

1,042
3
18
34

should be MM/dd/yyyy you missed an y – Balazs Gunics Dec 29 '17 at 15:00
I'm getting error and added my job info, error snapshot above. – user3114967 Jan 02 '18 at 14:37
try to remove the remove the quote from "row1.ClosingDate" – Théo Capdet Jan 02 '18 at 14:47
Yes, I made those changes alread, but it is showing SimpleFormat error. – user3114967 Jan 02 '18 at 17:04

score 0 · Answer 2 · answered Jan 03 '18 at 11:20

This is a very common mistake doing in reading data without understanding the underlying data types.

I have blogged about this especially for Talend: https://www.tobiasmaasland.de/2017/07/20/using-date-in-talend-etl-jobs/

But let me explain a bit.

Sometimes Excel tries to convert data in the cell even if one might think the cell type is set to String. Insted, it is set to Date. As such, no conversion is needed and the type needs to be Date in the input component.

If it is a String and an error occurs, the the structure of the String is either not the same everywhere or some cells are empty (null). So you might be lucky with

TalendDate.parseDate("MM/dd/yyyy", (row1.ClosingDate == null), "01/01/1970", row1.ClosingDate)

I just assumed you might want to use a placeholder date insted of having null.

This heavily depends on the actual data type in the cells, if every cell has the same data type and if all the data is formatted correctly.

To sum up one of the facts in my blog post: Don't use String for dates. Use Date for dates in Excel. It makes everything easier.

Convert String to ISO date format in Talend

2 Answers2