topic Re: Help - Converting String format in excel to time format in db in Talend Studio

Help - Converting String format in excel to time format in db

Anonymous — Tue, 08 May 2018 18:12:34 GMT

Hi,

I have an excel file with a column that has data in this format: 09:00:00 (timestramp)

I need to convert from string (as talend is reading) to time (hh:mm) format in Database.

I tried doing this:

TalendDate.parseDate("HH:mm",row1.P_E)

Error I'm getting:

t 14:01 08/05/2018.

[statistics] connecting to socket on port 3865
[statistics] connected
java.lang.NumberFormatException: For input string: "Sun "
	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$DateTimeParser.parse(FastDateParser.java:206)
	at java.text.DateFormat.parse(Unknown Source)
	at routines.TalendDate.parseDate(TalendDate.java:884)
	at routines.TalendDate.parseDate(TalendDate.java:842)
	at mip_citelum.jb_08_mip_protocolos_0_1.jb_08_mip_protocolos.tFileInputExcel_1Process(jb_08_mip_protocolos.java:3716)
	at mip_citelum.jb_08_mip_protocolos_0_1.jb_08_mip_protocolos.runJobInTOS(jb_08_mip_protocolos.java:5353)
	at mip_citelum.jb_08_mip_protocolos_0_1.jb_08_mip_protocolos.main(jb_08_mip_protocolos.java:5202)
Exception in component tMap_1 (jb_08_mip_protocolos)
java.lang.RuntimeException: java.text.ParseException: Unparseable date: "Sun Dec 31 15:00:00 BRT 1899"
	at routines.TalendDate.parseDate(TalendDate.java:898)
	at routines.TalendDate.parseDate(TalendDate.java:842)
	at mip_citelum.jb_08_mip_protocolos_0_1.jb_08_mip_protocolos.tFileInputExcel_1Process(jb_08_mip_protocolos.java:3716)
	at mip_citelum.jb_08_mip_protocolos_0_1.jb_08_mip_protocolos.runJobInTOS(jb_08_mip_protocolos.java:5353)
	at mip_citelum.jb_08_mip_protocolos_0_1.jb_08_mip_protocolos.main(jb_08_mip_protocolos.java:5202)
Caused by: java.text.ParseException: Unparseable date: "Sun Dec 31 15:00:00 BRT 1899"
	at java.text.DateFormat.parse(Unknown Source)
	at routines.TalendDate.parseDate(TalendDate.java:884)
	... 4 more
[statistics] disconnected
Job jb_08_mip_protocolos ended at 14:01 08/05/2018. [exit code=1]

Re: Help - Converting String format in excel to time format in db

vboppudi — Tue, 08 May 2018 19:10:32 GMT

Are you looking for the following output?

Use :TalendDate.parseDate("HH:mm:ss",row2.DATE_HHMM)

Regards,

Re: Help - Converting String format in excel to time format in db

Anonymous — Tue, 08 May 2018 19:23:54 GMT

Yes, that's what i'm trying to do. But like I said before, I tried this :

TalendDate.parseDate("HH:mm",row1.P_E) already and it gives me the error that I posted.

Re: Help - Converting String format in excel to time format in db

vboppudi — Tue, 08 May 2018 19:26:40 GMT

Hi,

Please provide some sample source data. i think the error "

For input string: "Sun "

related to input data. What is the data type of output attribute?

Regards,

Re: Help - Converting String format in excel to time format in db

Anonymous — Tue, 08 May 2018 19:33:37 GMT

The input datatype is String;

The output datatype in talend is Date;

The output datatype in my DB is Time (hh:mm:ss)

In attach is the excel source.

Ocorrências.xlsx

Re: Help - Converting String format in excel to time format in db

vboppudi — Tue, 08 May 2018 19:47:38 GMT

Hi,

As per the data you provided P_E attribute data look different than you mentioned.

Regards,

Re: Help - Converting String format in excel to time format in db

Anonymous — Tue, 08 May 2018 19:56:38 GMT

Yes, because somehow talend is doing this, but if you open the excel, the column is HH:MMS.

In any case, do you how I can get the HH:MM of the P_E column?

Re: Help - Converting String format in excel to time format in db

vboppudi — Tue, 08 May 2018 20:16:15 GMT

Hi,

While reading data from excel you have to settings to read data from excel in required format.

Regards,

Re: Help - Converting String format in excel to time format in db

Anonymous — Tue, 08 May 2018 20:39:12 GMT

Thank you very much for you assistance. It worked.

God bless.