Date parsing not working correctly

Anonymous — Sat, 16 Nov 2024 11:49:45 GMT

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"

Re: Date parsing not working correctly

Anonymous — Fri, 22 Nov 2013 02:14:45 GMT

Hi,
Is it working when you check out option "Trim all columns" in Advanced Setting of tFileInputDelimited component?
Best regards
Sabrina

Re: Date parsing not working correctly

Anonymous — Fri, 22 Nov 2013 09:05:00 GMT

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

Re: Date parsing not working correctly

Anonymous — Fri, 22 Nov 2013 09:35:05 GMT

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

Re: Date parsing not working correctly

Anonymous — Fri, 22 Nov 2013 14:45:51 GMT

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,

topic Re: Date parsing not working correctly in Talend Studio

Date parsing not working correctly

Re: Date parsing not working correctly

Re: Date parsing not working correctly

Re: Date parsing not working correctly

Re: Date parsing not working correctly