Issue using the Microsoft.ACE.OLEDB.12.0 provider to read excel content using T-SQL

Copper Contributor

Hi experts,

I'm trying to read excel content from T-SQL using the ACE provider and OPENROWET.

Using the following syntax:

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; HDR=NO; IMEX=1; Database=E:\ExcelFile1.xlsm','SELECT * FROM [CONTROLLING$A11:G120]');

 

SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0; HDR=NO; IMEX=1; Database=E:\ExcelFile2.xlsm','SELECT * FROM [CONTROLLING$A11:G120]');

 

I'll have 2 different results.

File 1 will skip the first column (A is an empty column) > returns 6 columns

File 2 will return NULL in first column (A is the same empty column) > returns 7 columns

Both files have Column A empty, Column A is having the same data type in both files.

 

Can someone help trying to figure out what happened?

 

Oli

3 Replies

 


Column A is having the same data type in both files.

@OlivierSanzot , Excel don't know data types, it knows a value and a display format.

Without having the Excel files I can't say why.

@olafhelper, IMEX=1 means I'm considering everything as string. TYPE(cellreference) returns a data type. In my case it is 1 for every cell. The entire A column is empty in both files. Unfortunately, I can't share a customer file :(
For Excel a cell is only then "empty" when it was never edited. If you enter a value and remove it again, then the cell isn't "empty" = NULL in database terms, it's a white space.