6

Been investigating for a while now and keep hitting a brick wall. I am importing from xls files into temp tables via the OpenRowset command. Now I have a problem where I’m trying to import a certain column has a range values but the most common are the following. Columns structured as long numbers i.e. 15598 and the some columns as strings i.e. 15598-E.

Now the openrowset is reading the string version no problem but is reporting the number version as a NULL. I read (http://www.sqldts.com/254.aspx ) that openrowset has that issue and the author speaks of implementing “HDR=YES;IMEX=1” into the query string but that’s not working for me at all.

Have any of you guys every encountered this?

Just some more info as well. I may not do this with the JET engine (Microsoft.Jet.OLEDB.4.0) so this is what my query looks like:

SELECT *
FROM 
    OPENROWSET('MSDASQL'
                , 'Driver=Microsoft Excel Driver (*.xls);HDR=YES;IMEX=1;DBQ=C:\ImportFile.xls;'
            , 'SELECT * FROM [Sheet1$]')
StevenMcD
  • 17,262
  • 11
  • 42
  • 54

5 Answers5

6

I notice you are using the Excel ODBC driver. Have you tried the JET OLEDB Provider with the equivalent connection string?

select * from openrowset(
    'Microsoft.Jet.OLEDB.4.0',
    'Data Source=C:\ImportFile.xls;Extended Properties="Excel 8.0;HDR=Yes;IMEX=1"',
    'SELECT * FROM [Sheet1$]')

EDIT: Sorry, just noticed your last paragraph. Surely the Excel ODBC driver still goes via the JET engine, so what difference would it make?

EDIT: I have looked at the KB194124 link, and the registry values it recommends are the default values on my machine, which I have never changed. I have used the above method several times myself without problems. Maybe it's an environmental issue?

Christian Hayter
  • 30,581
  • 6
  • 72
  • 99
  • no problem, check out http://stackoverflow.com/questions/1178243/what-is-the-difference-between-odbc-and-oledb for more info – StevenMcD Aug 26 '09 at 12:43
  • +1. A year after this was posted and it solved the same issue for me. – 8kb Oct 22 '10 at 20:24
3

If you don't mind opening the file in Excel, take the columns that have the problem, select the column, and do

Data -> Text to Columns -> Next -> Next -> Text

Save the spreadsheet and they should all come in as Text in OPENROWSET

I've found using .CSV files instead of Excel, opened by setting up a Linked Server, and setting up the format of the files in schema.ini a more practical approach for handling imports like this, with that method you can explicitly choose each column's format.

SqlACID
  • 4,024
  • 20
  • 28
1

We've come across the same issue. Unfortunately we've not found a solution either. There's more information here which indicates that there might be a registry fix.

nickd
  • 3,951
  • 2
  • 20
  • 24
0

I had the same problem. I fixed it cuting and pasting a row that contains a column with the string/numeric value (for example 123ABC) in the first row position of the sheet. For some reason T-SQL reads the first row and assumes that all the values are numeric.

0

Response by SqlACID in this link worked great [https://wikigurus.com/Article/Show/185717/OpenRowSet-command-in-TSQL-is-returning-NULLS] :-

If you don't mind opening the file in Excel, take the columns that have the problem, select the column, and do

Data -> Text to Columns -> Next -> Next -> Text

Save the spreadsheet and they should all come in as Text in OPENROWSET

I've found using .CSV files instead of Excel, opened by setting up a Linked Server, and setting up the format of the files in schema.ini a more practical approach for handling imports like this, with that method you can explicitly choose each column's format.

Anurag Dwivedi
  • 1