Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2007
    Posts
    3

    Question Unanswered: SSIS Import of Excel data like "9760020" imports "9.76002e+006"

    Hello,



    I have a problem with the Import of an Excel file with SSIS and hope one of you can help me out.



    There is a column with mixed data (format is TEXT) in an excel file and I want to import it as Text (DT_WSTR (255)).

    So far everything works fine but some fields like "9760020" imports "9.76002e+006".



    My settings so far are:



    Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<FileName>;Extended Properties="EXCEL 8.0;HDR=NO;IMEX=1"



    In addition I altered the registry entry from Microsoft.Jet.engine.excel



    TypeGuessRows to 0 (ImportMixedType = Text)





    Has someone got a solution?



    Thankx

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    9760020 = 9.76002e+006
    So SSIS is not doing anything wrong.

    This is clearly a number and should be treated as one. Numbers <> text.
    George
    Home | Blog

  3. #3
    Join Date
    Sep 2007
    Posts
    3

    no its not a number....

    my settings from the first post (IMEX=1) say treat everything as text....

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yes I realise that - but that is where the issue is stemming from!
    The number is being converted into text!
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Quote Originally Posted by georgev
    Yes I realise that - but that is where the issue is stemming from!
    The number is being converted into text!
    Yeah indeed, change the format of the column to number = problem fixed

  6. #6
    Join Date
    Sep 2007
    Posts
    3
    no I have mixed data like

    1234
    asdf
    1a1s2c

  7. #7
    Join Date
    Mar 2003
    Posts
    223
    I have the same problem before. SQL Server cannot do anything. Before loading, convert data type from General to Nemuric in Excel.

    ZYT

  8. #8
    Join Date
    Nov 2003
    Location
    London
    Posts
    169
    Quote Originally Posted by -JWS-
    no I have mixed data like

    1234
    asdf
    1a1s2c
    It doesn't matter, just change it to number column in excel as I and others have already said and it will fix your problem....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •