Forum Discussion
Issue using the Microsoft.ACE.OLEDB.12.0 provider to read excel content using T-SQL
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.
