Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable
‎2013-11-21 06:22 AM

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"
Labels (3)
Labels
232 Views
0 Likes
4 Replies
Anonymous
Not applicable
‎2013-11-21 09:14 PM
Author

Hi,
Is it working when you check out option "Trim all columns" in Advanced Setting of tFileInputDelimited component?
Best regards
Sabrina
0683p000009MBpC.png
232 Views
0 Likes
Anonymous
Not applicable
‎2013-11-22 04:05 AM
Author

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"
...
232 Views
0 Likes
Anonymous
Not applicable
‎2013-11-22 04:35 AM
Author

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
232 Views
0 Likes
Anonymous
Not applicable
‎2013-11-22 09:45 AM
Author

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,
232 Views
0 Likes