Search code examples
sqlsql-serverexcelt-sqlopenrowset

SQL import from openrowset mixed type [keep leading 0, long ints and TEXT] correctly

I have a column in Excel I'm trying to import, and it includes codes like the following:

  • 01166
  • 1166
  • 2354654765432
  • xx132

I use IMEX=1 and everything imports as TEXT but when I assign the select to temporary table with nvarchar type, the codes with long numbers become incorrect:

1001051 becomes 1.00105e+006

I tried to cast to bigint, but this makes the code lose the leading 0 in 01166

this is my current query:

INSERT INTO #XTEMP
SELECT DISTINCT 
    (CASE ISNUMERIC([item_code]) WHEN 1 THEN CAST(CAST([item_code] AS BIGINT) AS nvarchar) ELSE CAST([item_code] AS nvarchar) END)
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=C:\path\file.xls',
    'SELECT * FROM [sheet$]')
Solution

  • IMEX=1 allows reading mixed columns; it does not force data values to be text. Looking at this SO page, you can see a possible workaround if you set HDR=NO, but that has its own complications.

    If you can, the best thing to do is set the format of the column in the spreadsheet to Text.