I have T-SQL code for SQL Server 2012 to read an Excel file into a temp table. I do this using OPENDATASOURCE:

INSERT INTO #MyTable (id, F1)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id, F1
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0','Data Source=FilePathMustBeHere;
                     Extended Properties="EXCEL 12.0;HDR=No;IMEX=1"')...['SheetNameMustBeHere$']

My question is: can I be 100%-sure that the #MyTable.id values will correspond to the physical order of the corresponding rows in the Excel file?

If needed, I could use OpenRowSet instead of the OpenDataSource. But the Excel file definitely has no column that may be used to sort the read rows in the order I need; I need to order the #MyTable.id values in the same way as the physical rows are ordered in the Excel file.

I have T-SQL code for SQL Server 2012 to read an Excel file into a temp table. I do this using OPENDATASOURCE:

INSERT INTO #MyTable (id, F1)
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS id, F1
FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.16.0','Data Source=FilePathMustBeHere;
                     Extended Properties="EXCEL 12.0;HDR=No;IMEX=1"')...['SheetNameMustBeHere$']

My question is: can I be 100%-sure that the #MyTable.id values will correspond to the physical order of the corresponding rows in the Excel file?

If needed, I could use OpenRowSet instead of the OpenDataSource. But the Excel file definitely has no column that may be used to sort the read rows in the order I need; I need to order the #MyTable.id values in the same way as the physical rows are ordered in the Excel file.

• sql
• sql-server
• excel
• t-sql
• openrowset

• 1 You can't even be sure that OpenDataSource will succeed in reading the file at all, even less that sorting will be correct. You should use proper libs to read files properly outside of sql server – siggemannen Commented Mar 12 at 9:48
• (1) It is better to use OPENROWSET(), (2) You can use ROW_NUMBER() OVER (ORDER BY @@SPID) clause. It will give you a natural order of data from an Excel file. – Yitzhak Khabinsky Commented Mar 12 at 12:35
• Database tables have no inherent order, so you are asking the wrong question. Instead add a new column in excel order the rows 1 through whatever. You can use =Row() to do this quickly. Then import. That new column is something you can use in your ORDER BY clause when you SELECT from the table. – JNevill Commented Mar 12 at 20:23

1 Answer 1

Short Answer: No.

The longer answer is that to ensure that the data is in the same order, you need something to order by that actually denotes the order of the data. (SELECT 1) returns a constant, so the ordering you get for ROW_NUMBER will be arbitrary in this case and so there is no guarantee that the ordering for your expression will be the same order of the data in your Spreadsheet. Define an order based on the columns. Don't fet, this could be a combination of columns. For example ORDER BY ClientID, ContactDate would be acceptable.

Therefore you need to ORDER BY a column (or columns) in your spreadsheet that denote(s) the correct order. If you don't have a column (or columns) that denote(s) that order this suggests one of two things:

1. The ordering isn't important, so an arbitrary order is fine (perhaps you should use an IDENTITY instead though?), or,
2. The Spreadsheet is flawed, and it should have something that denotes the order. Fix the spreadsheet first, and then you can ORDER BY the column(s) you need.