Ordering Issue while importing excel file from excel to sql server table with Openrowset

Question

Issue Description

We are using OPENROWSET to load data from excel file to SQL Server2019 Table.

select *  into dbo.StagingTest

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\PDTS\Test\Test.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')

 

select * from dbo.StagingTest

 

--drop table dbo.StagingTest

Data got staged to dbo.StagingTest but we can see data does not retain its order as header row moved from first to nth row in staging table. The issue is intermittent and can’t be reproduced in lower environment.

Issue usually happen with large file. File having around 37000 rows that errored out.

Excel:

Staging Table:

 

Can you please help why this happen and is there any resolution?

Answer 1

but we can see data does not retain its order

Data don't have a "natural order", it always can be some random.

Answer 2

Hi @Ranjan Kalita,

You can try to modify your statement as follows:

SELECT * INTO dbo.StagingTest
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
	'Excel 12.0 Xml; HDR=YES;
	Database=D:\PDTS\Test\Test.xlsx', 
	'[Sheet1$]');

Explanation:

File Type (extension) Extended Properties

---

Excel 97-2003 Workbook (.xls) "Excel 8.0"

Excel Workbook (.xlsx) "Excel 12.0 Xml"

Excel Macro-enabled workbook (.xlsm) "Excel 12.0 Macro"

Excel Non-XML binary workbook (.xlsb) "Excel 12.0"

"HDR=Yes;" indicates that the first row contains column names, not data. "HDR=No;" indicates the opposite.

"HDR=Yes;" will omit (ignore) the first row while getting data from an Excel file.

For the reference: https://www.connectionstrings.com/ace-oledb-12-0/

Answer 3

There are not first or last rows in a table. Tables are by definition unordered sets. The only way to assign "order" to data in a table is by the data itself. If you want to look at the data with the header rows on top, you need to do:

SELECT *
FROM  tbl
ORDER BY CASE F1 = 'Personalnumber THEN 1 ELSE 2 END