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

​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 columnsFile 2 will return NULL in first column (A is the same empty column) > returns 7 columnsBoth 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  Read More

​