none
problems importing data from excel (numbers stored as text error) RRS feed

  • Question

  • i have an excel sheet

    in it there is a column that holds values of item numbers

    some of these values are preceeded with zeros  E.G "00123" (with out the ")

    when i view the data in excel i get this little green corner whice states (when pressed) that these values are numbers stoerd as text.

    no inside the excel sheet i have no problem with that

    but when i try to import the excel sheet into ssis using an excel source task

    all these values are imported as nulls!!!

     

    i am lost

    i tried converting the format of these cells to numbers but then i loose the leading zeros

    what i done temporarly to solve this problem is to accept excel's suggestion and turn these values into numbers

    i then import them and convert them to strings in ssis and then ad dthe zeros.

    now althouge this works, this isnt realy a solution.

    i canot manualy correct each excel file each  time i get a new copy

    and in the future i will not have the luxuery of having fixed lenght values (so i wont be able to know how many zeroe i"d need to add)

     

    there must be a better way

    please help

    thanks in advance

    Daniel

     

    Wednesday, May 21, 2008 8:56 PM

Answers

  • Have you tried adding IMEX=1 to the extended properties of the connection string? This is a common problem when the column contains mixed types, as the OLE DB Driver is trying to guess what the types are. If the type of an actual value doesn't match the guessed type for the column, the driver replaces it with a NULL.

     

    This issue is discussed in more depth here: http://technet.microsoft.com/en-us/library/ms141683.aspx

     

    Thursday, May 22, 2008 2:07 AM
    Moderator

All replies

  • Have you tried adding IMEX=1 to the extended properties of the connection string? This is a common problem when the column contains mixed types, as the OLE DB Driver is trying to guess what the types are. If the type of an actual value doesn't match the guessed type for the column, the driver replaces it with a NULL.

     

    This issue is discussed in more depth here: http://technet.microsoft.com/en-us/library/ms141683.aspx

     

    Thursday, May 22, 2008 2:07 AM
    Moderator
  • hi there

    thank you very much for the answer

    i was only now able to try it

    it did solve my problem but my only concern is  (even after reading the articale in the link) that when you use this IMEX=1 its not posible to determin how the driver will act on the diffrent data types in the column

    but that antoher story Smile

    thank you very much

    Daniel

    Saturday, May 24, 2008 8:36 PM