topic Re: Date loose its format after load table into SQL in Talend Studio

Date loose its format after load table into SQL

valkmeister — Thu, 25 Jun 2020 14:41:10 GMT

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!

Re: Date loose its format after load table into SQL

JohnRMK — Thu, 25 Jun 2020 17:55:56 GMT

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

Re: Date loose its format after load table into SQL

valkmeister — Thu, 25 Jun 2020 18:45:49 GMT

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!

Re: Date loose its format after load table into SQL

JohnRMK — Fri, 26 Jun 2020 10:02:45 GMT

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

Re: Date loose its format after load table into SQL

valkmeister — Wed, 05 Aug 2020 16:26:17 GMT

Thank you! It worked!