Importing into SQL Server from Excel

  • Greetings,

    I have imported excel spreadsheets into a SQL Server database table many times, but I'm running into something I haven't seen before.

    I receive data in Excel format from several sources.  I then combine them into one excel spreadsheet (actually, one worksheet in one spreadsheet.)

    The first column is Social Security number. The problem is that when I import the spreadsheet, many of the SSNs are converted to <NULL>. I've tried reformatting the column in the spreadsheet to where they are all formatted the same (at least the best I can tell they are the same) but that doesn't help any.

    Any suggestions would be greatly appreciated!

    Bob

  • Long shot but does this help?

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

  • Have you been able to work out a pattern - if you look back at the source data, do the fields that come through as nulls have anything in common?

    When you talk about formatting, I presume that you mean more than just Format/Cells?  This command changes only the way in which Excel displays data, not the data itself.

    Regards, Phil


  • Try the code below to use Excel as linked server or import the data to a temp table before moving it to the destination table.   One of the above should solve the problem.  Hope this helps.

     

     

     

     

     

    /* Excel as a linked server */

    /* Assuming we have an Excel file 'D:\testi\Myexcel.xls'

       with following data in the first sheet:

     id name

      1 a

      2 b

      3 c

    */

     

    EXEC sp_addlinkedserver 'ExcelSource',

       'Jet 4.0',

       'Microsoft.Jet.OLEDB.4.0',

       'D:\testi\Myexcel.xls',

       NULL,

       'Excel 5.0'

     

    EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false'

     

    EXEC sp_tables_ex ExcelSource

    EXEC sp_columns_ex ExcelSource

     

    SELECT *

      FROM ExcelSource...Sheet1$

     

    CREATE TABLE test_excel

     (id int,

      name varchar(255))

    GO

     

    INSERT INTO test_excel

    SELECT *

      FROM ExcelSource...Sheet1$

     

    SELECT *

      FROM test_excel

     

    /* Now define two ranges in Excel on the 2nd sheet as tables */

    /* Select the range, Insert->Name->Define */

    /* Note: sp_tables_ex does not recognize the defined tables */

    /* We can still refer to the tables explicitly */

     

    EXEC sp_tables_ex ExcelSource

    EXEC sp_columns_ex ExcelSource

     

    SELECT *

      FROM ExcelSource...Table1

     

    SELECT *

      FROM ExcelSource...Table2

     

     

    Kind regards,
    Gift Peddie

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

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