topic Re: Handling Dates while copying from Excel to SQL server database table in Talend Studio

Handling Dates while copying from Excel to SQL server database table

_AnonymousUser — Sat, 16 Nov 2024 11:37:38 GMT

Hello,
I am copying data from an Excel workbook to the SQL server database table.
One of the column in my Excel sheet has Dates, which I am generating using Rand function.
If I keep data types of my columns as string in both my Excel input and SQL output.
I get an error which says
"Conversion failed when converting character string to smalldatetime data type."
If I keep my datatypes as Date it gives as error which says
"The cell format is not Date in ....(cell address)"

In my table's definition in SQL server. It has data type as 'smalldatetime'.
So which data types I should assign to my input as well as output to transfer them from Excel to SQL?

Re: Handling Dates while copying from Excel to SQL server database table

Anonymous — Wed, 28 May 2014 16:13:16 GMT

Hi
Can you please upload a screenshot of the source data? It will be helpful for us to understand your problem well.
Shong

Re: Handling Dates while copying from Excel to SQL server database table

_AnonymousUser — Wed, 28 May 2014 16:19:12 GMT

The data is simple dates which I have generated using RANDBETWEEN(...) function.
It displays as (e.g.1) January 31, 1965 (e.g.2) April 7, 1970... so on.

Re: Handling Dates while copying from Excel to SQL server database table

Anonymous — Wed, 28 May 2014 16:47:23 GMT

Hi
If you read the data as string, you need to convert the string to Date in the job to map the target data type (smalldatetime). Have a try to add a tLogRow after tFileInputExcel to print the data on the console and see what's data you get from the excel file.
Shong

Re: Handling Dates while copying from Excel to SQL server database table

_AnonymousUser — Wed, 28 May 2014 18:12:41 GMT

When I remove the mapping between date's columns I am being able to copy the rest of the data.
As you have said I need to take Excel input as string, convert it into date format and copy it to the SQL table.
Now I wanted to know how and when do I do this?
Right now my job has Excel Input (created by metadata -> create file excel) -> tMap -> tMSSqlOutput (created by retrieving the schema from the connection to the database).
Where and what changes do I need to make in this job?
Thanks,

Re: Handling Dates while copying from Excel to SQL server database table

_AnonymousUser — Wed, 28 May 2014 18:22:07 GMT

Will my new job flow be like:
Excel Input -> tConvertType -> tMap -> tMSSqlOutput ?

Re: Handling Dates while copying from Excel to SQL server database table

_AnonymousUser — Wed, 28 May 2014 18:52:45 GMT

I have tried it but it gives me an error which says
"java.text.ParseException: Unparseable date: "Fri Nov 30 00:00:00 EST 2001" ?

Re: Handling Dates while copying from Excel to SQL server database table

Anonymous — Thu, 29 May 2014 02:58:48 GMT

Hi
You don't set the proper date pattern for parsing the data "Fri Nov 30 00:00:00 EST 2001" read by tFileInputExcel from the excel file. You can use the built-in function TalendDate.parseDateLocale() to convert the string data to a Date on tMap.
tFileInputExcel--main-->tMap-->tMSSQLOutput
See my screenshot.
Shong

Re: Handling Dates while copying from Excel to SQL server database table

_AnonymousUser — Thu, 29 May 2014 14:18:01 GMT

I have tried this function but I am still getting errors such as..
Exception in component tMap_1
java.lang.NullPointerException
at java.text.SimpleDateFormat.parse(Unknown Source)
at java.text.DateFormat.parse(Unknown Source)
at routines.TalendDate.parseDateLocale(TalendDate.java:854)
at datejob2.job1_0_1.Job1.tFileInputExcel_1Process(Job1.java:2177)
at datejob2.job1_0_1.Job1.runJobInTOS(Job1.java:2804)
at datejob2.job1_0_1.Job1.main(Job1.java:2670)

I wanted to share a screenshot but didn't find the option to do so.

Re: Handling Dates while copying from Excel to SQL server database table

Anonymous — Thu, 29 May 2014 14:37:22 GMT

before parsing you have to check whether your input column is null or not null. use below statement.

youcolumn!=null && !"".equalsIgnoreCase(youcolumn)? parsetodate : null

Re: Handling Dates while copying from Excel to SQL server database table

_AnonymousUser — Thu, 29 May 2014 15:04:27 GMT

Thanks a lot, Umesh!