none
Problem with Excel data type conversion RRS feed

  • Question

  • I have a process where I'm pulling data from Excel. The data type in excel is date. The target column in SQL Server is VARCHAR(20). I do a data conversion transformation in my package where I cast the dates to string [DT_STR] with length 20.

    The original values are 2/19/2013

    They show up in the database as 41324.

    What is causing this and what is the solution?

    Tuesday, February 19, 2013 9:50 PM

Answers

  • Read all excel data as string by adding IMEX=1 in the connection string. Refer this link for more information. http://sqlserverrider.wordpress.com/2012/07/30/excel-auto-column-data-type-detector/

    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    • Marked as answer by falcon00 Wednesday, February 20, 2013 3:18 PM
    Wednesday, February 20, 2013 5:06 AM

All replies

  • I should also mention that there are 8 date columns in total and only 2 of them are exhibiting this behavior.
    Tuesday, February 19, 2013 9:50 PM
  • You need to convert the data from Excel to DT_DATE then to DT_STR

    Arthur My Blog

    Tuesday, February 19, 2013 10:04 PM
    Moderator
  • So you're saying you need two data conversion task? I tried that as you suggested and the first one blew up. [Data Conversion [305]] Error: Data conversion failed while converting column "Blah" (54) to column "Copy of Blah" (328).  The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
    Tuesday, February 19, 2013 10:11 PM
  • Inspect the value for the date, it is probably longer than you set to accommodate

    Arthur My Blog

    Tuesday, February 19, 2013 10:20 PM
    Moderator
  • It's blowing up before it hits any size contraints. I created two task. One where it converts from excel to DT_Date and then one where it takes that value and converts to DT_STR. It's the first task where it goes from Excel to DT_Date that it's blowing up at.
    Tuesday, February 19, 2013 10:29 PM
  • hi there,

    i can not see why you wouldn't able to do it. it is a straight forward simple process.

    follow my steps:

    1) open BIID and your project and package

    2) add excel source and configure. by default if your data is datetime it will use DT_DATE

    note: if you have column header then you may find issues while SSIS to choose the right data type. for the time being check the box if you have row header and read data only.

    3) optional: data conversion: add data conversion and connect. right click and open showadvanced. in hte input out put properties go to data conversion output and change the output data type to string[DT_STR]

    4) add oledb destination and map to your  table columns.

    here is the output: if you use dataconversion the out put will be like this:

    15/02/2011    6/04/2010
    5/06/2017    4/03/2012

    if you do not use data conversion the out put will be like this:

    2011-02-15 00:00:00    2010-04-06 00:00:00
    2017-06-05 00:00:00    2012-03-04 00:00:00

    good luck

    kumar

    Wednesday, February 20, 2013 12:03 AM
  • Read all excel data as string by adding IMEX=1 in the connection string. Refer this link for more information. http://sqlserverrider.wordpress.com/2012/07/30/excel-auto-column-data-type-detector/

    Thanks Ayyappan Thangaraj, http://SQLServerRider.wordpress.com

    • Marked as answer by falcon00 Wednesday, February 20, 2013 3:18 PM
    Wednesday, February 20, 2013 5:06 AM
  • Adding IMEX=1 does nothing. The key was to actually close the sheet while you have the designer open. Ridic.
    Wednesday, February 20, 2013 3:18 PM
  • "for the time being check the box if you have row header and read data only." This was checked already.
    Wednesday, February 20, 2013 3:20 PM