Handling Dates while copying from Excel to SQL server database table

[_AnonymousUser]

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?

[Anonymous]

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

[_AnonymousUser]

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.

[Anonymous]

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

[_AnonymousUser]

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,

[_AnonymousUser]

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

[_AnonymousUser]

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" ?

[Anonymous]

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

[_AnonymousUser]

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.

[Anonymous]

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

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