Converting String format in excel to date format in db

Highlighted
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.

 


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?

 


All 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

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads