Post reply

Importing Excel into SQL - some columns import as NULL

  • June 9, 2005 at 6:52 am

    #89391

    I have an excel file that I want to import into a SQL table. The 3rd column is the one I'm having a problem with. This columns is formatted in excel as text, and most of the values are alphanumberic - except two rows ahve numeric-only values: 21021 and 21023. When I import the file into SQL, SQL replaces these columns with NULL. Why? How do I get it to import numeric-only values properly as the other alpanumeric values?

    05/01/200505/31/2005X1185
    05/01/200505/31/2005T9131
    05/01/200505/31/2005X1161
    05/01/200505/31/200521021
    05/01/200505/31/200521023
    05/01/200505/31/2005A004
    05/01/200505/31/2005E4004

    The SQL table is defined as:

    Period_Start_Date datetime 8 1

    Period_End_Date datetime 8 1

    Local_RM_Code varchar 20 1

  • June 9, 2005 at 7:38 am

    #564207

    This because the engine used to read the spreadsheet tries to decide whether each column is char or numeric based on it's data (IIRC the first 7 rows). If any data in that column does not match that type then null is substituted. The only way round this is to make sure all the data in your 3rd column is text by putting a single quote before the data in the spreadsheet.

    Far away is close at hand in the images of elsewhere.
    Anon.

  • June 9, 2005 at 7:50 am

    #564215

    There is a way around this using some package settings. See this article for a simple explanation of what to do. Works every time:

     

    http://www.sqldts.com/default.aspx?254

    ------------------------------
    The Users are always right - when I'm not wrong!

  • June 9, 2005 at 8:16 am

    #564228

    Really interesting problem.  I don't have it but I'm glad I know about this problem now because I have work coming up that could experience this issue. 

    Thanks so much for posting the solution!

    Paula

  • June 9, 2005 at 8:46 am

    #564242

    Thanks!  This worked perfectly:

    Right click on empty space in your DTS designer window

    Choose "Disconnected Edit..."

    Open up the connections

    Open up your Excel Connection

    Look in the OLE DB Properties of the connection

    The tenth item in the list is Extended Properties

    Now look across to the right hand pane and you will see a table with two columns. The one we want has value in the leftmost column and mine has "Excel 8.0;HDR=YES;"

    Double click on the value and change the string to read "Excel 8.0;HDR=YES;IMEX=1"

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply