"Unparseable date" exception, date string seems correct

[akatao]

Hi,


I'm having trouble to load a CSV Input File into a Postgres 8.4 DB, and it seems that the lines with date strings I'm trying to insert don't have any problems (like invisible characters, as I looked with a hex-editor, or CR/LF problem). Though the error occurs with some aleatory lines:


2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Because of an error, this step can't continue:
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : org.pentaho.di.core.exception.KettleValueException:
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : DAY_ZERO_HOUR String : couldn't convert string [2038/10/17] to a date using format [yyyy/MM/dd]
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Unparseable date: "2038/10/17"
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) :
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertStringToDate(ValueMeta.java:609)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.getDate(ValueMeta.java:1660)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertData(ValueMeta.java:3061)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertBinaryStringToNativeType(ValueMeta.java:1124)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.getString(ValueMeta.java:1279)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.RowMeta.getString(RowMeta.java:184)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.RowMeta.getString(RowMeta.java:605)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.writeToTable(TableOutput.java:401)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.steps.tableoutput.TableOutput.processRow(TableOutput.java:117)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.trans.step.RunThread.run(RunThread.java:40)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.lang.Thread.run(Thread.java:619)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : Caused by: java.text.ParseException: Unparseable date: "2038/10/17"
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at java.text.DateFormat.parse(DateFormat.java:337)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : at org.pentaho.di.core.row.ValueMeta.convertStringToDate(ValueMeta.java:604)
2012/07/30 15:21:54 - Table output.0 - ERROR (version 4.2.0-stable, build 15748 from 2011-09-08 13.11.42 by buildguy) : ... 10 more


Below the csv file:
bugged_line.jpg

Any assistance on the correct approach will be greatly appreciated.

Thanks and Regards.
Anderson Katao
Jr Developer

[marabu]

Hm, something reminds me of the dreaded Y2038 problem...

[TomS]

Year 2038 Problem?
Never heard of it... let´s google...
http://en.wikipedia.org/wiki/Year_2038_problem

Woooooha Haha, nice one! Thanks for making my day marabu
Let´s got for 64bit!

[MattCasters]

So this is using lazy conversion: the "CSV Input" step doesn't do any data conversion (since you the lazy conversion option is enabled) and will defer the conversion to the "Table Output" step.
I would suggest to disable the feature for data loading to a database, perhaps the "CSV Input" step will give more details.
Also check that you specified the correct UTF-8 encoding.

[akatao]

I've tried disabling lazy conversion, setting UTF-8, but nothing changed. When disabling the hop, the input file was well processed, and this is what I got when trying to substitute the CSV File Input for a Data Grid:
data_grid_1.jpg
data_grid_1_preview.jpg
data_grid_2.jpg
data_grid_2_preview.jpg

Any help on how to solve this problem would be welcome.

Thanks and Regards.
Anderson Katao
Jr Developer