[SOLVED] Not all rows are imported from Excel file using OPENROWSET in SQL Server

Not all rows are imported from Excel file using OPENROWSET in SQL Server

I have a Excel table with 47 columns and 14K rows. I import this data to SQL Server with OPENROWSET:

INSERT INTO dbo.my_table
SELECT * FROM OPENROWSET
(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=Yes;Database=C:\ExcelFile.xlsx',
    'SELECT * FROM [Sheet1$]'
);

However, only 5138 rows were imported. After some time the number of imported rows decreased down to 5052, i.e. every time - different number of rows. However, when I use Tasks -> Import Data..., then all rows are successfully imported. What's the reason of such behavior?

I'm using SQL Server 2017 14.0.3356.2.

Solution

The syntax of the SQL Server code differs slightly from the example code in the Docs. To exactly conform to the syntax of the code in the Docs it should look something like this

INSERT INTO dbo.my_table
SELECT * FROM OPENROWSET
(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=Yes;Database=C:\ExcelFile.xlsx',
    [Sheet1$]
);

The name of the sheet [Sheet1$] is no longer inside quotes and the SQL-like code ("SELECT * FROM ") was removed.

The other possible issue could be with specifying HDR=Yes. Do each of the columns for which there is/are any rows have a header without spaces and/or unusual formatting? It's something to consider if needed.