topic Re: Cleansing, parsing and inserting data into a Postgres table in Talend Studio

Cleansing, parsing and inserting data into a Postgres table

NAnto — Fri, 15 Nov 2024 23:28:52 GMT

Hello everyone!

I am stuck on inserting dates in a table. I think they are not recognized by Talend, even when the right format is specified in the mapping (I first tried with the output, they with the input, then both of them). I read the documentation, but I think I don't understand something in the procedure or in the heart of how the thing works.

First, I installed Talend carefully with the exact recommended version of Java (otherwise it wouldn't have worked). I created a job and imported a geoJSON file and the fields I want to map and insert into the table. I installed all the modules, did the preview, then I used tMap to change the fields' names. I converted the float types into string types but when it came to the dates, I got successive errors, each time different. The default "EN" date format was dd-MM-yyyy and I had in my dataset multiple formats: dd/MM/yy, dd/MM/yyyy, etc.

My goal is to have each correct date (but in wrong format) in the format dd/MM/yy

(I saw it was recognized by Talend in the documentation)

and make null the incomplete dates (for example: "0/0/2013"). Here is my error: unparseable date (see picture).

I tried with the functions formatDate, parseDate, etc. without success (I got two types of errors: "yy is not recognized as a variable" and "cannot convert from String to Date" nothing else).

Thank you in advance for your help, I hope you understood well my problem, don't hesitate to ask for more details if needed!

Regards,

Re: Cleansing, parsing and inserting data into a Postgres table

Anonymous — Wed, 01 Dec 2021 07:06:12 GMT

Should use corresponding date pattern to parse the different date format, eg:

row1.column.length=8?TalendDate.parseDate("dd/MM/yy", row1.columnName):TalendDate.parseDate("dd/MM/yyyy",row1.columnName)

For incomplete dates, you can use tSchemaCompilanceCheck to check and filter the rows before they are parsed to a Date, the rejected rows can be processed separately.

Regards

Shong

Re: Cleansing, parsing and inserting data into a Postgres table

NAnto — Fri, 10 Dec 2021 08:48:58 GMT

Hello Shong,

First thank you very much for your answer.

Here is the error I get. I think there is a problem in type declaration of that column, but I have no idea of how to change it or handle it. Thank you again.

Regards,

Re: Cleansing, parsing and inserting data into a Postgres table

Anonymous — Fri, 10 Dec 2021 11:22:44 GMT

It is a compilation error, the function TalendDate.parseDate(String, String) required two string parameters, so, you should read the source data as a string.

Regards

Shong

Re: Cleansing, parsing and inserting data into a Postgres table

NAnto — Fri, 10 Dec 2021 14:10:04 GMT

Hello,

Thank you for your answer, I tried to put "String" instead of "Date" so it can be read as a string into the tMap module but I have errors like "Type mismathc: cannot convert int to boolean", when it's not this one it is another, I always get an error on that job I don't understand.

Regards...

Re: Cleansing, parsing and inserting data into a Postgres table

Anonymous — Mon, 13 Dec 2021 08:04:28 GMT

It is another compilation error, the job will be translated to Java code, if you have a little Java knowledge, you will understand this kind of error more easy.

We usually open the Code tab to look into the job generated code, check which lines have the compilation error, return back to the job design and correct it.

Regards

Shong