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

[Anonymous]

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]

[vboppudi]

Are you looking for the following output?

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

Regards,

[Anonymous]

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.

[vboppudi]

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,

[Anonymous]

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

[vboppudi]

Hi,

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

Regards, 

[Anonymous]

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?

[vboppudi · Accepted solution]

Hi,

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

Regards,

[Anonymous]

Thank you very much for you assistance. It worked.

God bless.