Couldn't parse value for column in tFileInputDelimited

[Anonymous]

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

[manodwhb]

@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 )

[Anonymous]

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..?

[manodwhb]

@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.

[manodwhb · Accepted solution]

@wakkieeeeee ,check with below expression.

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