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

Six Stars

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

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]

 


Accepted Solutions
Nine Stars

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

Hi,

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

DateConve.PNGDateConve1.PNGDateConve2.PNG

 

Regards,

 

Veeru Boppudi

All Replies
Nine Stars

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

Are you looking for the following output?

dateConversion.PNG

 

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

Regards,

Veeru Boppudi
Six Stars

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

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.

Nine Stars

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

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,

Veeru Boppudi
Six Stars

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

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.

Nine Stars

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

Hi,

 

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

 

dateConversion1.PNG

Regards, 

Veeru Boppudi
Six Stars

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

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

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

 

Nine Stars

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

Hi,

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

DateConve.PNGDateConve1.PNGDateConve2.PNG

 

Regards,

 

Veeru Boppudi
Six Stars

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

Thank you very much for you assistance. It worked.

God bless.