Six Stars

Converting String format in excel to date format in db

Hi,

 

I have an excel in which one column contains dates, however these dates are in the string format. I'd like to convert this column to a date format in db.

 

Could anyone please help me with this.

 

regards,

hs.

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Four Stars

Re: Converting String format in excel to date format in db

Hi,

tFileExcelInput -> tmap -> tOracleOutput (example)
In the tmap component you can convert string to date in the expression editor of the particular object by using the below function.

TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",your_Date_Column_in_String_Format)

This will convert your string to date

Thanks
Raghu
Six Stars

Re: Converting String format in excel to date format in db

thank you sir...

 

my input string is in 1/15/17 00:00 ..

so I have used this function in tMap :

 TalendDate.parseDate("MM/dd/yy h:mm",your_Date_Column_in_String_Format)

and the string datatype is changed to date datatype in output.

 

I have one doubt , if I have a file in csv format,will it work?

 

6 REPLIES
Four Stars

Re: Converting String format in excel to date format in db

Hi

You can use a tmap in between the excel input and the db output components. Where you can use Talend Date functions to convert it from String to Date.
Five Stars

Re: Converting String format in excel to date format in db

Can't you just change the schema in your tFileInputExcel (presume that's what you're using) so that the column has a correctly defined Date format?
Six Stars

Re: Converting String format in excel to date format in db

sir could you please help me with example.

 

for example: I have a column name modified on in excel and in that I have records in string format but data is filled with 8/3/12 12:00....so on

 

now I want the string data type to date data type while I am loading the excel to db.

 

regards,

sushmaaa

Four Stars

Re: Converting String format in excel to date format in db

Hi,

tFileExcelInput -> tmap -> tOracleOutput (example)
In the tmap component you can convert string to date in the expression editor of the particular object by using the below function.

TalendDate.parseDate("yyyy-MM-dd HH:mm:ss",your_Date_Column_in_String_Format)

This will convert your string to date

Thanks
Raghu
Six Stars

Re: Converting String format in excel to date format in db

thank you sir...

 

my input string is in 1/15/17 00:00 ..

so I have used this function in tMap :

 TalendDate.parseDate("MM/dd/yy h:mm",your_Date_Column_in_String_Format)

and the string datatype is changed to date datatype in output.

 

I have one doubt , if I have a file in csv format,will it work?

 

Six Stars

Re: Converting String format in excel to date format in db

thank you..
its working