topic Re: Couldn't parse value for column in tFileInputDelimited in Talend Studio

Couldn't parse value for column in tFileInputDelimited

Anonymous — Sat, 16 Nov 2024 06:41:18 GMT

Hi there,

I'm trying to get 2 TSV data from S3 and unite them into one file.

But, when reading the file with tFileInputDelimited, I've got the error below.

ジョブ Lyr1_Intg001_SJ_gaw_m_supplier を 13:14 04/02/2019 に開始しました。
[statistics] connecting to socket on port 3504
[statistics] connected
Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
[ERROR]: dwh_poc_project.lyr1_intg001_sj_gaw_m_supplier_0_1.Lyr1_Intg001_SJ_gaw_m_supplier - tFileInputDelimited_1 - Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
[ERROR]: dwh_poc_project.lyr1_intg001_sj_gaw_m_supplier_0_1.Lyr1_Intg001_SJ_gaw_m_supplier - tFileInputDelimited_1 - Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
[ERROR]: dwh_poc_project.lyr1_intg001_sj_gaw_m_supplier_0_1.Lyr1_Intg001_SJ_gaw_m_supplier - tFileInputDelimited_1 - Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
[ERROR]: dwh_poc_project.lyr1_intg001_sj_gaw_m_supplier_0_1.Lyr1_Intg001_SJ_gaw_m_supplier - tFileInputDelimited_1 - Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
[ERROR]: dwh_poc_project.lyr1_intg001_sj_gaw_m_supplier_0_1.Lyr1_Intg001_SJ_gaw_m_supplier - tFileInputDelimited_1 - Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
[ERROR]: dwh_poc_project.lyr1_intg001_sj_gaw_m_supplier_0_1.Lyr1_Intg001_SJ_gaw_m_supplier - tFileInputDelimited_1 - Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"
[ERROR]: dwh_poc_project.lyr1_intg001_sj_gaw_m_supplier_0_1.Lyr1_Intg001_SJ_gaw_m_supplier - tFileInputDelimited_1 - Couldn't parse value for column 'upd_bat_time' in 'row3', value is '\N'. Details: java.lang.RuntimeException: Unparseable date: "\N"

The data I'm using is the below. This is the record of the file. The 'upd_bat_time' is the red letters.

JKT 0SGP ENG \N MSGP IDRM SGD \N 0 \N 331 xxxxx xxxxxx ROAD #xx-xx xxxxx TOWERS xxxxxxxxxx xxxxxx \N \N \N \N \N \N \N \N \N \N \N xxxx SOUTH EAST ASIA xxx LTD \N \N \N \N \N \N \N \N \N +xx-x xxxx xxxx +xx-x xxx xxxx \N 0 \N 112 \N \N \N \N \N \N \N \N \N \N 188720 \N 02 \N FOB \N \N \N \N \N SBBBB SGP 0 0 \N dummy@mail.xxxxxx.co.jp SAAAA 0 0 IKOU 2016-01-10 17:09:33 2016-01-10 15:09:33 2016-01-10 17:09:33 PWC_MO IKOU 2016-01-10 17:09:33 2016-01-10 15:09:33 2016-01-10 17:09:33 PWC_MO 2016-01-11 07:31:32 \N \N

I defined the general schema and set the colume as DATE, and allowed NOT NULL.

What should I do this matter?

This is very stupid question.

But I'm struggling with this for over 1 hour.

Can anyone help me?

Can someone understand

Re: Couldn't parse value for column in tFileInputDelimited

manodwhb — Mon, 04 Feb 2019 05:09:18 GMT

@wakkieeeeee ,your data is coming other than the date format what you have specified in the Schme.

Since i will suggest you to write it to tLogrow by specifying the sting data type and see the data how are you getting.

Re: Couldn't parse value for column in tFileInputDelimited

Anonymous — Mon, 04 Feb 2019 05:29:53 GMT

Hi manodwhb

Thank you for your suggestion.

I looked into the data with tLogRow.

As I mentioned, I defined The "upd_bat_time" column as DATE, and the pattern is like

"yyyy-MM-dd' 'HH:mm:ss"

The actual data with tLogRow is below.

2018-11-14 17:53:24

I defined the DATE patten suited to the actual data.

Is that wrong?

Re: Couldn't parse value for column in tFileInputDelimited

manodwhb — Mon, 04 Feb 2019 05:33:41 GMT

@wakkieeeeee ,that date format is correct ,may be you need to verify for all the rows are you getting in same format or not.

Re: Couldn't parse value for column in tFileInputDelimited

Anonymous — Mon, 04 Feb 2019 05:34:21 GMT

The second columns from the end of rows is "upd_bat_time".

It looks good..

tLogRow.PNG

Re: Couldn't parse value for column in tFileInputDelimited

manodwhb — Mon, 04 Feb 2019 05:38:00 GMT

@wakkieeeeee ,after tlogrow ,use tmap to covert that column form sting to date ,then you will get to know to which row you may have issue.

Re: Couldn't parse value for column in tFileInputDelimited

Anonymous — Mon, 04 Feb 2019 05:42:44 GMT

Hi Manohar

Thank you for your help!

I looked into the actual data on Amazon S3, and I found that the some of the rows in the data includes null data like '\N'.

Is this the cause of problem??

Later, as you mentioned, I will try to tMap.

actual data.PNG

Re: Couldn't parse value for column in tFileInputDelimited

manodwhb — Mon, 04 Feb 2019 05:45:45 GMT

@wakkieeeeee ,yes for the null data if you have \N,which you trying to convert to date then you will get Unparseable date: "\N".

Re: Couldn't parse value for column in tFileInputDelimited

Anonymous — Mon, 04 Feb 2019 06:09:24 GMT

Hi Manohar

Thank you for your quick reply.

Thanks to you, I understand "\N" is the cause of this issue.

However, there are a lot of "\N" character as null in the original data.

How do I handle this character?

Should I define the date columns as "STRING" for the general schema, and then I have to convert then into "DATE" with tMap?

Sorry for

Re: Couldn't parse value for column in tFileInputDelimited

manodwhb — Mon, 04 Feb 2019 06:20:32 GMT

@wakkieeeeee ,first read that column as sting and in the tmap replace /N with null and then convert that field is not null then sting to date format conversion else null.this way you will not face issue.

Re: Couldn't parse value for column in tFileInputDelimited

manodwhb — Mon, 04 Feb 2019 06:40:28 GMT

@wakkieeeeee ,with the below expression you can handle i have tested. input data type is sting and the ouput is date with "yyyy-MM-dd HH:mm:ss" format.

row3.newColumn1.replace("/N","NULL").equals("NULL")?null:TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",row3.newColumn1 )

Re: Couldn't parse value for column in tFileInputDelimited

Anonymous — Mon, 04 Feb 2019 07:39:17 GMT

Hi Manohar,

Thank you for your sample.

I defined as below.

But, another error occurred..

ジョブ sample_01 を 16:34 04/02/2019 に開始しました。
[statistics] connecting to socket on port 3992
[statistics] connected
java.lang.StringIndexOutOfBoundsException: String index out of range: 4
at java.lang.String.substring(String.java:1963)
at routines.system.FastDateParser$DateTimeParser.parse(FastDateParser.java:206)
at java.text.DateFormat.parse(DateFormat.java:364)
at routines.TalendDate.parseDate(TalendDate.java:916)
at routines.TalendDate.parseDate(TalendDate.java:874)
at dwh_poc_project.sample_01_0_1.sample_01.tFileInputDelimited_1Process(sample_01.java:6612)
at dwh_poc_project.sample_01_0_1.sample_01.tS3Get_1Process(sample_01.java:745)
at dwh_poc_project.sample_01_0_1.sample_01.tS3Connection_1Process(sample_01.java:527)
at dwh_poc_project.sample_01_0_1.sample_01.runJobInTOS(sample_01.java:7619)
at dwh_poc_project.sample_01_0_1.sample_01.main(sample_01.java:7445)
Exception in component tMap_1 (sample_01)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "\N"
at routines.TalendDate.parseDate(TalendDate.java:930)
at routines.TalendDate.parseDate(TalendDate.java:874)
at dwh_poc_project.sample_01_0_1.sample_01.tFileInputDelimited_1Process(sample_01.java:6612)
at dwh_poc_project.sample_01_0_1.sample_01.tS3Get_1Process(sample_01.java:745)
at dwh_poc_project.sample_01_0_1.sample_01.tS3Connection_1Process(sample_01.java:527)
at dwh_poc_project.sample_01_0_1.sample_01.runJobInTOS(sample_01.java:7619)
at dwh_poc_project.sample_01_0_1.sample_01.main(sample_01.java:7445)
Caused by: java.text.ParseException: Unparseable date: "\N"
at java.text.DateFormat.parse(DateFormat.java:366)
at routines.TalendDate.parseDate(TalendDate.java:916)
... 6 more
[FATAL]: dwh_poc_project.sample_01_0_1.sample_01 - tMap_1 java.text.ParseException: Unparseable date: "\N"
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "\N"
at routines.TalendDate.parseDate(TalendDate.java:930)
at routines.TalendDate.parseDate(TalendDate.java:874)
at dwh_poc_project.sample_01_0_1.sample_01.tFileInputDelimited_1Process(sample_01.java:6612)
at dwh_poc_project.sample_01_0_1.sample_01.tS3Get_1Process(sample_01.java:745)
at dwh_poc_project.sample_01_0_1.sample_01.tS3Connection_1Process(sample_01.java:527)
at dwh_poc_project.sample_01_0_1.sample_01.runJobInTOS(sample_01.java:7619)
at dwh_poc_project.sample_01_0_1.sample_01.main(sample_01.java:7445)
Caused by: java.text.ParseException: Unparseable date: "\N"
at java.text.DateFormat.parse(DateFormat.java:366)
at routines.TalendDate.parseDate(TalendDate.java:916)
... 6 more
[statistics] disconnected

ジョブ sample_01 が 16:34 04/02/2019 に終了しました。 [終了コード=1]

Can you advise me to fix this..?

Re: Couldn't parse value for column in tFileInputDelimited

manodwhb — Mon, 04 Feb 2019 07:46:04 GMT

@wakkieeeeee ,you can ask the person who is placing the file in AmzonS3 bucket for the null values do not place \N ,so that when you were converting sting date you will not get issues.

are you sure you are getting /N only for the null values,based on the error you may getting more than /N.

Re: Couldn't parse value for column in tFileInputDelimited

manodwhb — Mon, 04 Feb 2019 07:55:07 GMT

@wakkieeeeee ,check with below expression.

row3.newColumn1.replaceAll("\\\\N","NULL").equals("NULL")?null:TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",row3.newColumn1 )