topic Re: Excel date column format issue in Talend Studio

Excel date column format issue

gt0731 — Sat, 16 Nov 2024 07:22:56 GMT

. when i am trying to read THE DATE column it is giving me error
any help on it would be much appreciated

Attached is the screenshot of job

tarting job DebugTime at 14:46 30/10/2018.

[statistics] connecting to socket on port 3446
[statistics] connected
Exception in component tMap_1 (DebugTime)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "0030c00002XGcpHAAT"
	at routines.TalendDate.parseDate(TalendDate.java:898)
	at routines.TalendDate.parseDate(TalendDate.java:842)
	at frog_demo.debugtime_0_1.DebugTime.tFileInputExcel_1Process(DebugTime.java:978)
	at frog_demo.debugtime_0_1.DebugTime.runJobInTOS(DebugTime.java:1437)
	at frog_demo.debugtime_0_1.DebugTime.main(DebugTime.java:1286)
Caused by: java.text.ParseException: Unparseable date: "0030c00002XGcpHAAT"
	at java.text.DateFormat.parse(Unknown Source)
	at routines.TalendDate.parseDate(TalendDate.java:884)
	... 4 more
[statistics] disconnected

Job DebugTime ended at 14:46 30/10/2018. [exit code=1]

Also, attaching exact excel sheet with date column that i want to read here

Re: Excel date column format issue

Anonymous — Thu, 01 Nov 2018 08:25:01 GMT

Hello,

What is the source date format? Do youneed to convert date to string?

Best regards

Sabrina

Re: Excel date column format issue

sunbaj — Thu, 01 Nov 2018 08:33:43 GMT

I see that your source is string and the target is date. You need to use the following expression to convert string to date. Also make sure that you filter out any nulls or put in a check that if null then ignore else use the below expression

TalendDate.parseDate("yyyy-MM-dd",Var.var1)

Re: Excel date column format issue

gt0731 — Thu, 01 Nov 2018 10:05:11 GMT

I am reading source as string type and i want to convert it into date ?
I also attached excelsheet here. please dowanload at your end

Re: Excel date column format issue

gt0731 — Thu, 01 Nov 2018 10:06:55 GMT

When i tried the this expression it gives me error
Starting job DebugTime at 15:36 01/11/2018.

[statistics] connecting to socket on port 4057
[statistics] connected
java.lang.NumberFormatException: For input string: "003a"
at java.lang.NumberFormatException.forInputString(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at java.lang.Integer.parseInt(Unknown Source)
at routines.system.FastDateParser$DateParser.parse(FastDateParser.java:127)
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:884)
at routines.TalendDate.parseDate(TalendDate.java:842)
at frog_demo.debugtime_0_1.DebugTime.tFileInputExcel_1Process(DebugTime.java:979)
at frog_demo.debugtime_0_1.DebugTime.runJobInTOS(DebugTime.java:1438)
at frog_demo.debugtime_0_1.DebugTime.main(DebugTime.java:1287)
Exception in component tMap_1 (DebugTime)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "003a000001QLYuTAAX"
at routines.TalendDate.parseDate(TalendDate.java:898)
at routines.TalendDate.parseDate(TalendDate.java:842)
at frog_demo.debugtime_0_1.DebugTime.tFileInputExcel_1Process(DebugTime.java:979)
at frog_demo.debugtime_0_1.DebugTime.runJobInTOS(DebugTime.java:1438)
at frog_demo.debugtime_0_1.DebugTime.main(DebugTime.java:1287)
Caused by: java.text.ParseException: Unparseable date: "003a000001QLYuTAAX"
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDate(TalendDate.java:884)
... 4 more
[statistics] disconnected

Job DebugTime ended at 15:36 01/11/2018. [exit code=1]

Re: Excel date column format issue

gt0731 — Thu, 01 Nov 2018 10:09:35 GMT

when i am clicking on cell of excel sheet. it gives me diffrent date format for ex 07/22/2018 34:35:L36 AM

Re: Excel date column format issue

sunbaj — Thu, 01 Nov 2018 10:21:48 GMT

Your date format is MM/DD/YYYY HH:ss

Re: Excel date column format issue

sunbaj — Thu, 01 Nov 2018 10:22:58 GMT

It seems that there is some issue with the time format. Do check your source.

Re: Excel date column format issue

gt0731 — Thu, 01 Nov 2018 10:50:50 GMT

I attached my source file . please use the file

Re: Excel date column format issue

gt0731 — Thu, 01 Nov 2018 14:09:43 GMT

Starting job DebugTime at 19:39 01/11/2018.

[statistics] connecting to socket on port 3700
[statistics] connected
java.lang.NumberFormatException: For input string: "003a"
	at java.lang.NumberFormatException.forInputString(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at java.lang.Integer.parseInt(Unknown Source)
	at routines.system.FastDateParser$DateParser.parse(FastDateParser.java:127)
	at java.text.DateFormat.parse(Unknown Source)
	at routines.TalendDate.parseDate(TalendDate.java:884)
	at routines.TalendDate.parseDate(TalendDate.java:842)
	at frog_demo.debugtime_0_1.DebugTime.tFileInputExcel_1Process(DebugTime.java:977)
	at frog_demo.debugtime_0_1.DebugTime.runJobInTOS(DebugTime.java:1436)
	at frog_demo.debugtime_0_1.DebugTime.main(DebugTime.java:1285)
Exception in component tMap_1 (DebugTime)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "003a000001QLYuTAAX"
	at routines.TalendDate.parseDate(TalendDate.java:898)
	at routines.TalendDate.parseDate(TalendDate.java:842)
	at frog_demo.debugtime_0_1.DebugTime.tFileInputExcel_1Process(DebugTime.java:977)
	at frog_demo.debugtime_0_1.DebugTime.runJobInTOS(DebugTime.java:1436)
	at frog_demo.debugtime_0_1.DebugTime.main(DebugTime.java:1285)
Caused by: java.text.ParseException: Unparseable date: "003a000001QLYuTAAX"
	at java.text.DateFormat.parse(Unknown Source)
	at routines.TalendDate.parseDate(TalendDate.java:884)
	... 4 more
[statistics] disconnected

Job DebugTime ended at 19:39 01/11/2018. [exit code=1]

Re: Excel date column format issue

Anonymous — Fri, 02 Nov 2018 03:41:34 GMT

Hello,

From your source file, your string :2017-07-17'T'16:15:38'.000Z'.

What does your expected result look like?

Elaborating your case with an example with input and expected output values will be preferred.

Best regards

Sabrina

Re: Excel date column format issue

gt0731 — Fri, 02 Nov 2018 06:10:55 GMT

Here is information of Input/output

Input

From source file, string :2017-07-17'T'16:15:38'.000Z'. but when we click on excel cell it looks like 7/17/2017 4:15:38 PM.

Here is screenshot for input data

Output
Expected output should be in date type format should be in DATE: yyyy-MM-dd'T'HH:mm:ss'000Z'

Re: Excel date column format issue

gt0731 — Fri, 02 Nov 2018 15:15:48 GMT

I am getting exception it tells that internal excel date representation coming numeric
How to parse numeric into date type

Starting job DebugTime at 20:42 02/11/2018.

[statistics] connecting to socket on port 3418

[statistics] connected

Exception in component tMap_1 (DebugTime)

java.lang.RuntimeException: java.text.ParseException: Unparseable date: "42933.67752314815"

at routines.TalendDate.parseDate(TalendDate.java:898)

at routines.TalendDate.parseDate(TalendDate.java:842)

at frog_demo.debugtime_0_1.DebugTime.tFileInputExcel_1Process(DebugTime.java:987)

at frog_demo.debugtime_0_1.DebugTime.runJobInTOS(DebugTime.java:1444)

at frog_demo.debugtime_0_1.DebugTime.main(DebugTime.java:1293)

Caused by: java.text.ParseException: Unparseable date: "42933.67752314815"

at java.text.DateFormat.parse(Unknown Source)

at routines.TalendDate.parseDate(TalendDate.java:884)

... 4 more

[statistics] disconnected

Job DebugTime ended at 20:42 02/11/2018. [exit code=1]

Re: Excel date column format issue

gt0731 — Fri, 02 Nov 2018 15:36:00 GMT

I have date column in excel, but when I'm reading in my talend application I'm getting value as number

Example

Excel Date

1/1/2013 03:44:55 AM

Am getting as

41275.00

How to convert number to date in TALEND