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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch