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

[Anonymous]

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?

[manodwhb]

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

[Anonymous]

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

It looks good..

tLogRow.PNG

[manodwhb]

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

[Anonymous]

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

[manodwhb]

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

[Anonymous]

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 

[manodwhb]

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