Date loose its format after load table into SQL

[valkmeister]

Hi all!

I'm trying to load a csv table into mysql. The date data type on the CSV is DATE only, not DATETIME, and I just want to load it without as it is. However the date column end up as random numbers. I've already tried parseDate, but it keeps getting errors.

This is the csv schema:

This is the previews that Talend gives me during creating schema, that I want to keep.

I end up with this on mysql server.

Everytime I set a tMap to parse Date, I got errors. This is my first time with talend. Right now I'm just using a tdBconnection -> FileInputMetod -> tdbOutput

For anyone who can help me fixing this, thank you!

[JohnRMK]

Hello, 

You can notice that you have your date with yyyy-MM-dd pattern and in csv schema you have dd-MM-yyyy. So update the correct pattern in csv and mysql schema to have the same as the file

[valkmeister]

Hi JohnRMK, thank you for you help.

Still ouput this. I've tried Talend.parseDate("yyyy-MM-dd", row1.startDate), I've got error on tMap as well

at java.lang.Integer.parseInt(Integer.java:615)
at routines.system.FastDateParser$DateParser.parse(FastDateParser.java:127)
at routines.system.ParserUtils.parseTo_Date(ParserUtils.java:337)
at h2020.dim_projects_0_1.Dim_projects.tFileInputDelimited_1Process(Dim_projects.java:1142)
at h2020.dim_projects_0_1.Dim_projects.tDBConnection_1Process(Dim_projects.java:438)
at h2020.dim_projects_0_1.Dim_projects.runJobInTOS(Dim_projects.java:1722)
at h2020.dim_projects_0_1.Dim_projects.main(Dim_projects.java:1571)
Couldn't parse value for column 'startDate' in 'row1', value is 'adasdasd'. Details: java.lang.RuntimeException: Unparseable date: "asdasdasd"
java.lang.NumberFormatException: For input string: "ERC-"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Integer.parseInt(Integer.java:580)
at java.lang.Integer.parseInt(Integer.java:615)
at routines.system.FastDateParser$DateParser.parse(FastDateParser.java:127)
at routines.system.ParserUtils.parseTo_Date(ParserUtils.java:337)
at h2020.dim_projects_0_1.Dim_projects.tFileInputDelimited_1Process(Dim_projects.java:1111)
at h2020.dim_projects_0_1.Dim_projects.tDBConnection_1Process(Dim_projects.java:438)
at h2020.dim_projects_0_1.Dim_projects.runJobInTOS(Dim_projects.java:1722)
at h2020.dim_projects_0_1.Dim_projects.main(Dim_projects.java:1571)
java.lang.NumberFormatException: For input string: "H202"
at java.lang.NumberFormatException.forInputString(NumberFormatException.java:65)
at java.lang.Integer.parseInt(Integer.java:580)
at java.lang.Integer.parseInt(Integer.java:615)
at routines.system.FastDateParser$DateParser.parse(FastDateParser.java:127)
at routines.system.ParserUtils.parseTo_Date(ParserUtils.java:337)


Thank you!

[JohnRMK · Accepted solution]

Hello
The error seems obvious, you are trying to parse a date when it is a character chain.

Couldn't parse value for column 'startDate' in 'row1', value is 'adasdasd'. Details: java.lang.RuntimeException: Unparseable date: "asdasdasd"

This means that you have problems with the date columns or that you are using the wrong data schema.

Try to limit the reading of the file to a few rows (2 or 3) and normally you shouldn't have any errors.

To solve these errors, you can use ternary conditions in tMap with a function specific to talend TalendDate.isDate (...) which returns a boolean.

so if it's a date you return the rows or you put other date or you filter it , up to you on this

[valkmeister]

Thank you! It worked!