Date parsing not working correctly

One Star

Date parsing not working correctly

When using the tFileInputDelimited component to parse large text files, I encounter parse exceptions on some lines but not all.
For example, this date cannot be parsed: "2013-10-01 00:00:57.8501" using pattern "yyyy-MM-dd HH:mm:ss.SSSS". Which is strange as the pattern matches.
However, when I parse the date using custom Java code in the tMap component, as shown below, it works perfectly:
new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSSS").parse(row1.timestamp);
This is the error thrown:
Exception in component tFileOutputDelimited_1
java.text.ParseException: Unparseable date: "2013-10-01 00:00:57.8501"
at java.text.DateFormat.parse(DateFormat.java:357)
at vex_poc.loadvps_0_1.LoadVPS.tMSSqlInput_1Process(LoadVPS.java:1657)
at vex_poc.loadvps_0_1.LoadVPS.runJobInTOS(LoadVPS.java:2795)
at vex_poc.loadvps_0_1.LoadVPS.main(LoadVPS.java:2660)

On two million rows of data the following date parsing errors were shown:

Unparseable date: "2013-10-01 00:00:44.7136"
Unparseable date: "2013-10-01 00:01:35.1515"
Unparseable date: "2013-10-01 00:01:37.1904"
Unparseable date: "2013-10-01 00:01:44.4690"
Unparseable date: "2013-10-01 00:00:40.5869"
Unparseable date: "2013-10-01 00:01:09.4939"
Unparseable date: "2013-10-01 00:00:50.2467"
Unparseable date: "2013-10-01 00:01:44.8292"
Unparseable date: "2013-10-01 00:00:06.7424"
Unparseable date: "2013-10-01 00:00:57.8501"
Unparseable date: "2013-10-02 00:01:52.1378"
Unparseable date: "2013-10-02 00:00:10.6584"
Unparseable date: "2013-10-02 00:00:56.9370"
Unparseable date: "2013-10-02 00:01:24.1466"
Unparseable date: "2013-10-02 00:00:44.6291"
Unparseable date: "2013-10-02 00:01:40.5416"
Unparseable date: "2013-10-02 00:00:14.2098"
Unparseable date: "2013-10-02 00:01:19.0343"
Unparseable date: "2013-10-02 00:01:07.2618"
Unparseable date: "2013-10-02 00:00:02.1862"
Unparseable date: "2013-10-02 00:00:33.9170"
Unparseable date: "2013-10-03 00:00:04.4873"
Unparseable date: "2013-10-03 00:00:44.0131"
Unparseable date: "2013-10-03 00:00:35.4066"
Unparseable date: "2013-10-03 00:01:59.1118"
Unparseable date: "2013-10-03 00:01:50.8237"
Unparseable date: "2013-10-03 00:00:19.9430"
Unparseable date: "2013-10-03 00:00:27.6579"
Unparseable date: "2013-10-03 00:00:23.7156"
Unparseable date: "2013-10-04 00:00:35.8906"
Unparseable date: "2013-10-04 00:01:07.6214"
Unparseable date: "2013-10-04 00:01:37.4683"
Unparseable date: "2013-10-04 00:01:22.7267"
Unparseable date: "2013-10-04 00:00:20.1533"
Unparseable date: "2013-10-04 00:00:07.9380"
Unparseable date: "2013-10-04 00:00:51.2984"
Unparseable date: "2013-10-04 00:01:03.6638"
Unparseable date: "2013-10-05 00:01:29.5364"
Unparseable date: "2013-10-05 00:01:20.7690"
Unparseable date: "2013-10-05 00:00:29.6125"
Unparseable date: "2013-10-05 00:01:05.9774"
Unparseable date: "2013-10-05 00:01:48.0046"
Unparseable date: "2013-10-05 00:00:39.0355"
Unparseable date: "2013-10-05 00:00:22.7387"
Unparseable date: "2013-10-05 00:01:20.0623"
Unparseable date: "2013-10-06 00:01:08.3299"
Unparseable date: "2013-10-06 00:01:36.5042"
Unparseable date: "2013-10-06 00:00:53.0247"
Unparseable date: "2013-10-06 00:01:01.8703"
Unparseable date: "2013-10-06 00:00:27.1125"
Unparseable date: "2013-10-06 00:00:03.9768"
Unparseable date: "2013-10-06 00:00:31.8917"
Unparseable date: "2013-10-06 00:00:58.2272"
Unparseable date: "2013-10-07 00:00:30.3225"
Unparseable date: "2013-10-07 00:00:26.8298"
Unparseable date: "2013-10-07 00:00:06.1332"
Unparseable date: "2013-10-07 00:00:26.5540"
Unparseable date: "2013-10-07 00:02:01.9031"
Unparseable date: "2013-10-07 00:01:02.4971"
Unparseable date: "2013-10-07 00:01:18.0759"
Unparseable date: "2013-10-07 00:02:12.7640"
Unparseable date: "2013-10-08 00:01:48.6258"
Unparseable date: "2013-10-08 00:00:42.5078"
Unparseable date: "2013-10-08 00:01:57.3518"
Unparseable date: "2013-10-08 00:00:21.1289"
Unparseable date: "2013-10-08 00:00:53.8282"
Unparseable date: "2013-10-08 00:00:09.6477"
Unparseable date: "2013-10-08 00:00:00.4702"
Unparseable date: "2013-10-08 00:01:29.7475"
Unparseable date: "2013-10-01 08:31:51.1603"
Unparseable date: "2013-10-01 00:45:16.7969"
Unparseable date: "2013-10-01 00:46:13.9862"
Unparseable date: "2013-10-01 07:15:08.4224"
Unparseable date: "2013-10-01 07:10:23.2221"
Unparseable date: "2013-10-01 00:44:58.3764"
Unparseable date: "2013-10-01 07:38:02.0846"
Unparseable date: "2013-10-01 00:45:52.1720"
Unparseable date: "2013-10-01 07:39:18.0950"
Unparseable date: "2013-10-01 07:15:49.1590"
Unparseable date: "2013-10-01 07:33:26.2823"
Unparseable date: "2013-10-02 00:59:12.5594"
Unparseable date: "2013-10-02 06:22:16.8337"
Unparseable date: "2013-10-02 06:23:33.3612"
Unparseable date: "2013-10-02 06:23:50.5728"
Unparseable date: "2013-10-02 06:23:24.4567"
Unparseable date: "2013-10-02 06:22:35.3111"
Unparseable date: "2013-10-02 06:21:52.6602"
Unparseable date: "2013-10-02 06:23:08.6709"
Unparseable date: "2013-10-02 06:10:16.4948"
Unparseable date: "2013-10-02 06:23:57.7057"
Unparseable date: "2013-10-02 06:23:19.0003"
Unparseable date: "2013-10-03 06:11:52.5383"
Unparseable date: "2013-10-03 00:10:13.5562"
Unparseable date: "2013-10-03 00:11:43.7269"
Unparseable date: "2013-10-03 06:04:14.0712"
Unparseable date: "2013-10-03 06:04:45.5816"
Unparseable date: "2013-10-03 06:06:31.6253"
Unparseable date: "2013-10-03 06:07:41.5555"
Unparseable date: "2013-10-03 06:09:28.2038"
Unparseable date: "2013-10-04 00:03:35.2952"
Unparseable date: "2013-10-04 00:01:10.0863"
Unparseable date: "2013-10-04 00:06:48.6132"
Unparseable date: "2013-10-04 00:06:11.8361"
Unparseable date: "2013-10-04 00:02:07.5166"
Unparseable date: "2013-10-04 00:01:27.0331"
Unparseable date: "2013-10-04 00:00:04.7771"
Unparseable date: "2013-10-04 00:03:20.4186"
Unparseable date: "2013-10-05 00:01:00.2701"
Unparseable date: "2013-10-05 00:01:11.6116"
Unparseable date: "2013-10-05 00:01:16.3666"
Unparseable date: "2013-10-05 00:01:13.3251"
Unparseable date: "2013-10-05 00:01:32.9035"
Unparseable date: "2013-10-05 00:01:01.4999"
Unparseable date: "2013-10-05 00:02:16.6372"
Unparseable date: "2013-10-05 00:02:01.2309"
Unparseable date: "2013-10-06 00:10:00.5059"
Unparseable date: "2013-10-06 00:00:10.1404"
Unparseable date: "2013-10-06 00:01:44.0231"
Unparseable date: "2013-10-06 00:00:39.9982"
Unparseable date: "2013-10-06 00:01:29.6085"
Unparseable date: "2013-10-06 00:02:12.7442"
Unparseable date: "2013-10-06 00:01:56.7907"
Unparseable date: "2013-10-06 00:05:29.9706"
Unparseable date: "2013-10-07 00:37:06.2371"
Unparseable date: "2013-10-07 00:23:23.5935"
Unparseable date: "2013-10-07 00:11:32.9989"
Unparseable date: "2013-10-07 00:04:08.3591"
Unparseable date: "2013-10-07 00:11:09.9572"
Unparseable date: "2013-10-07 00:07:56.0767"
Unparseable date: "2013-10-07 00:04:06.3676"
Unparseable date: "2013-10-07 00:16:40.0237"
Unparseable date: "2013-10-08 00:01:58.6417"
Unparseable date: "2013-10-08 00:03:27.2543"
Unparseable date: "2013-10-08 00:01:14.4822"
Unparseable date: "2013-10-08 00:04:20.0631"
Unparseable date: "2013-10-08 00:02:30.4096"
Unparseable date: "2013-10-08 00:04:37.6765"
Unparseable date: "2013-10-08 00:00:46.9281"
Unparseable date: "2013-10-08 00:01:11.7449"
Unparseable date: "2013-10-09 00:00:31.7545"
Unparseable date: "2013-10-09 00:00:05.0934"
Unparseable date: "2013-10-09 00:00:16.7251"
Unparseable date: "2013-10-09 00:00:04.2281"
Unparseable date: "2013-10-09 00:00:02.5598"
Unparseable date: "2013-10-09 00:00:01.2796"
Unparseable date: "2013-10-09 00:00:43.5848"
Unparseable date: "2013-10-09 00:00:03.1136"
Unparseable date: "2013-10-10 00:00:10.7399"
Unparseable date: "2013-10-10 00:00:11.7387"
Unparseable date: "2013-10-10 00:00:09.9275"
Unparseable date: "2013-10-10 00:00:00.4898"
Unparseable date: "2013-10-10 00:00:12.2962"
Unparseable date: "2013-10-10 00:00:03.8540"
Unparseable date: "2013-10-10 00:00:13.5002"
Unparseable date: "2013-10-10 00:00:09.2189"
Unparseable date: "2013-10-11 00:00:06.4925"
Unparseable date: "2013-10-11 00:00:21.9676"
Unparseable date: "2013-10-11 00:00:02.2022"
Unparseable date: "2013-10-11 00:00:11.2966"
Unparseable date: "2013-10-11 00:00:07.8026"
Unparseable date: "2013-10-11 00:00:03.1848"
Unparseable date: "2013-10-11 00:00:10.8030"
Unparseable date: "2013-10-11 00:00:01.4940"
Unparseable date: "2013-10-12 00:00:06.0656"
Unparseable date: "2013-10-12 00:00:00.7374"
Unparseable date: "2013-10-12 00:00:00.6524"
Unparseable date: "2013-10-12 00:00:09.9331"
Unparseable date: "2013-10-12 00:00:00.1383"
Unparseable date: "2013-10-12 00:00:03.7568"
Unparseable date: "2013-10-12 00:00:04.8284"
Unparseable date: "2013-10-12 00:00:00.9484"
Unparseable date: "2013-10-13 00:00:04.7033"
Unparseable date: "2013-10-13 00:00:02.0740"
Unparseable date: "2013-10-13 00:00:05.4356"
Unparseable date: "2013-10-13 00:00:03.0842"
Unparseable date: "2013-10-13 00:00:05.8875"
Unparseable date: "2013-10-13 00:00:24.5297"
Moderator

Re: Date parsing not working correctly

Hi,
Is it working when you check out option "Trim all columns" in Advanced Setting of tFileInputDelimited component?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: Date parsing not working correctly

Hello,
Thanks for your answer. The trimming option doesn't help.
Below is what my input looks like. It's tab delimited. For some reason the date on the 8257th line is not correctly parsed.
2013-10-01 23:58:44.8850 248723 VPS 2726 372542
2013-10-01 23:59:22.2324 3049544 VPS 2714 372612
2013-10-01 23:59:45.7266 1468083 VPS 2676 372823
2013-10-01 00:00:44.7136 346492 VPS 2700 372740
2013-10-01 00:02:33.9008 1039553 VPS 2652 372337
2013-10-01 00:02:37.7853 2067373 VPS 2726 372550
2013-10-01 00:02:56.6462 310077 VPS 2726 372581
2013-10-01 00:03:15.0547 317185 VPS 2694 371915
2013-10-01 00:03:30.2963 310077 VPS 2726 372577
This is (part of) the error output:
Starting job LoadVPS at 09:49 22/11/2013.

connecting to socket on port 3823
connected
Unparseable date: "2013-10-01 00:00:44.7136"
Unparseable date: "2013-10-01 00:01:35.1515"
Unparseable date: "2013-10-01 00:01:37.1904"
Unparseable date: "2013-10-01 00:01:44.4690"
Unparseable date: "2013-10-01 00:00:40.5869"
...
Community Manager

Re: Date parsing not working correctly

Hi
Exception in component tFileingOutputDelimited_1
java.text.ParseException: Unparseable date: "2013-10-01 00:00:57.8501"

Are you reading the data from a text file or trying to write the data into a file? Why the exception is occurring on tFileOutputDelimited? Can you upload a screenshot of the job? It will be helpful for us to find out the problem.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Date parsing not working correctly

Hi again,
Thanks for your help!
I found the problem in the end. The data contained several non-printable characters at the start of some lines in the dataset. I only found these characters on Linux using 'cat --show-nonprinting'. During my first work on Mac OS I couldn't see them for some unknown reason.
Glad it's solved now, my bad. Doesn't the trimming function in Talend remove these?
52737 2013-10-01 23:59:21.9805 608197 VPS 2701 372474^M
52738 M-oM-;M-?2013-10-01 00:01:09.4939 175144 VPS 2700 372654^M
52739 2013-10-01 00:02:06.0605 195582 VPS 2726 372558^M
52740 2013-10-01 00:02:06.8094 310077 VPS 2726 372549^M
Cheers,