Import Excel into SQL Server: OPENROWSET Query and C# Code

Last updated: October 29, 2026 · Tested with SQL Server 2025 LocalDB 17.0.4025.3, Microsoft Access Database Engine (ACE OLE DB 12.0 and 16.0), .NET 10.0.12, ClosedXML 0.105.1, ExcelDataReader 3.9.0, Microsoft.Data.SqlClient 7.1.1, Windows 11

Short answer: to import an Excel sheet into a SQL Server table with a query, enable Ad Hoc Distributed Queries and use INSERT INTO ... SELECT ... FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;HDR=YES;Database=C:\path\file.xlsx', 'SELECT * FROM [Sheet1$]'). It works, but it needs the Access Database Engine on the server, it reads every number as float, and it can silently turn text in a numeric column into NULL. For imports you run from code, read the file in C# (ClosedXML or ExcelDataReader) and write it with SqlBulkCopy. I ran both on a real workbook; the outputs below are from those runs, including the errors.

You see this	Cause	Fix
Msg 15281: SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’	Ad Hoc Distributed Queries is off (the default)	sp_configure, section 1
NULL where the sheet has text	ACE guessed the column type from the first 8 rows	Section 3
1.0 instead of 1, money as float	ACE reads every Excel number as float	Cast in the SELECT, section 2
Msg 7357: Cannot process the object “SELECT * FROM [Employees$]”	Wrong sheet name	Use the tab name plus $
No data is available for encoding 1252	ExcelDataReader on .NET without the code-pages provider	Section 5
The given ColumnName ‘Employee Id’ does not match up with any column in data source	SqlBulkCopy mapping to a column the table doesn’t have	Section 6

The Test Workbook

Employees.xlsx has a Sheet1 with six columns (EmployeeId, Name, Department, ZipCode, HireDate, Salary) and five rows. ZipCode is formatted as text so leading zeros like 02134 survive. A second sheet, Mixed, has a Sku column with ten numbers and then one text value, A-17, which is the kind of data that breaks imports. The target table:

CREATE TABLE Employees (EmployeeId int NOT NULL, Name nvarchar(100) NOT NULL, Department nvarchar(50) NULL, ZipCode varchar(10) NULL, HireDate date NULL, Salary decimal(10,2) NULL);

I ran the SQL on a separate LocalDB instance created only for this test, because the first step changes a server-wide setting. In the error messages below, the server name is replaced with <server>.

1. Import Excel With a SQL Query (OPENROWSET)

The first attempt, on a default SQL Server:

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

Msg 15281, Level 16, State 1, Server <server>, Line 1
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

Ad hoc queries are off by default because they let any user with the right permissions read files and remote data sources through the server. Turning them on needs a sysadmin:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;

Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

The same SELECT now returns the sheet:

EmployeeId Name Department ZipCode HireDate Salary
---------- ---- ---------- ------- -------- ------
1.0 Asha Patel Sales 02134 2021-03-15 00:00:00.000 52000.0
2.0 Ben Ortiz IT 10001 2019-07-01 00:00:00.000 71000.5
3.0 Chen Li IT 00501 2023-01-09 00:00:00.000 64000.0
4.0 Dana Ross HR 94105 2020-11-30 00:00:00.000 48000.0
5.0 Eli Novak Sales 60601 2022-06-06 00:00:00.000 55500.0

To load the table, name the columns on both sides instead of INSERT INTO Employees EXEC(...) or SELECT *, so a reordered sheet can’t shift data into the wrong columns:

INSERT INTO Employees (EmployeeId, Name, Department, ZipCode, HireDate, Salary)
SELECT EmployeeId, Name, Department, ZipCode, HireDate, Salary
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;Database=C:\Imports\Employees.xlsx',
    'SELECT * FROM [Sheet1$]');

SELECT * FROM Employees;

EmployeeId Name Department ZipCode HireDate Salary
---------- ---- ---------- ------- -------- ------
1 Asha Patel Sales 02134 2021-03-15 52000.00
2 Ben Ortiz IT 10001 2019-07-01 71000.50
3 Chen Li IT 00501 2023-01-09 64000.00
4 Dana Ross HR 94105 2020-11-30 48000.00
5 Eli Novak Sales 60601 2022-06-06 55500.00

What this needs, and where it usually fails:

• The Access Database Engine (ACE) on the SQL Server machine, in the same bitness as SQL Server (64-bit for any current SQL Server). On my machine both Microsoft.ACE.OLEDB.12.0 and 16.0 were installed and both returned the same rows; the provider name in the query must match one that’s installed on the server, not on your PC.
• A path the SQL Server process can read. The file is opened by the server, not by SSMS. LocalDB runs as your own Windows user, so a path on your disk works. A SQL Server Windows service runs as its service account, and a file in your Downloads folder or on a mapped drive is usually not visible to it. I didn’t reproduce that error here; copy the file to a folder the service account can read.
• The right sheet name, the tab name followed by $:

Msg 7357, Level 16, State 2, Server <server>, Line 1
Cannot process the object "SELECT * FROM [Employees$]". The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

The message talks about permissions, but the cause here was only that the tab is called Sheet1, not Employees. When you’re done, set Ad Hoc Distributed Queries back to 0 unless this is a regular job.

2. The Column Types ACE Gives You

I ran SELECT * INTO into a new table to see the types ACE reports:

COLUMN_NAME DATA_TYPE
----------- ---------
EmployeeId float
Name nvarchar
Department nvarchar
ZipCode nvarchar
HireDate datetime
Salary float

Every number is a float, including the salary. Inserting into a decimal(10,2) column converted cleanly for these values, but float can’t store most decimal fractions exactly, so for money columns cast explicitly in the SELECT (CAST(Salary AS decimal(10,2))) and check the totals after the import. ZipCode kept its leading zeros only because the column is formatted as text in Excel; as a number, 02134 is 2134 before SQL Server ever sees it.

3. Text in a Numeric Column Becomes NULL

The Mixed sheet has ten SKUs that are numbers and one, A-17, that is text (rows 1004 to 1010 left out below):

Sku Qty
--- ---
1002.0 2.0
1003.0 3.0
1011.0 11.0
NULL 3.0

No error, no warning: A-17 is simply NULL. ACE looks at the first 8 rows, decides the column is numeric, and drops anything that doesn’t fit. The usual advice is to add IMEX=1 to the connection string. I tried it on the same sheet and got exactly the same NULL, because IMEX=1 only switches a column to text when the first 8 rows already contain mixed types. Here they didn’t.

With A-17 moved into the first 8 rows (sheet MixedEarly), the default still returns NULL, and IMEX=1 fixes it:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=YES;IMEX=1;Database=C:\Imports\Employees.xlsx',
    'SELECT * FROM [MixedEarly$]');

Sku
---
1002
1003
1004
A-17
1006

So IMEX=1 is not a guarantee. The reliable fix is in the workbook: format code-like columns (SKUs, ZIP codes, account numbers) as text before the import, or import from C#, where you read each cell as the type you choose. Always compare the row count and the count of NULLs with the sheet after an OPENROWSET import.

4. Import Excel From C#: ClosedXML + SqlBulkCopy

Reading the file in your application avoids the server-side setup entirely: no ad hoc queries, no ACE on the server, no file path the server has to reach. ClosedXML reads the sheet into a DataTable, and SqlBulkCopy writes it in one round trip:

using var workbook = new XLWorkbook(file);
var sheet = workbook.Worksheet(1);
var dataTable = new DataTable();
var header = sheet.FirstRowUsed()!;
foreach (var cell in header.CellsUsed())
    dataTable.Columns.Add(cell.GetString());

foreach (var row in sheet.RowsUsed().Skip(1))
{
    var values = new object[dataTable.Columns.Count];
    for (int i = 0; i < values.Length; i++)
    {
        var cell = row.Cell(i + 1);
        values[i] = cell.IsEmpty() ? DBNull.Value : cell.Value.ToString();
    }
    dataTable.Rows.Add(values);
}

using var connection = new SqlConnection(Cs);
connection.Open();
using var bulk = new SqlBulkCopy(connection) { DestinationTableName = table };
foreach (DataColumn column in dataTable.Columns)
    bulk.ColumnMappings.Add(column.ColumnName, column.ColumnName);
bulk.WriteToServer(dataTable);

ClosedXML: 5 rows into Employees (read 1176 ms, total 1719 ms, managed memory 16 MB)

The rows in the table matched the OPENROWSET import exactly, including 02134 and 71000.50. Mapping by header name means the column order in the sheet doesn’t matter.

5. ExcelDataReader: Streaming Rows Into SqlBulkCopy

ExcelDataReader’s reader is an IDataReader, so it can go straight into SqlBulkCopy without building a DataTable. The first run on .NET 10 failed:

System.NotSupportedException: No data is available for encoding 1252. For information on defining a custom encoding, see the documentation for the Encoding.RegisterProvider method.

.NET (unlike .NET Framework) doesn’t include the Windows code pages unless you register them, and ExcelDataReader needs code page 1252. One line at startup fixes it:

System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);

using var stream = File.OpenRead(file);
using var reader = ExcelReaderFactory.CreateReader(stream);
reader.Read(); // skip the header row

using var connection = new SqlConnection(Cs);
connection.Open();
using var bulk = new SqlBulkCopy(connection) { DestinationTableName = table, BatchSize = 5000 };
bulk.WriteToServer(reader);

ExcelDataReader: 5 rows into Employees (total 428 ms, managed memory 0 MB)

Without column mappings, SqlBulkCopy matches columns by position, so this version assumes the sheet’s column order matches the table. That’s fine for a fixed export format and risky for files people edit by hand.

50,000 rows: ClosedXML vs ExcelDataReader

ClosedXML: 50000 rows into Big (read 3653 ms, total 4307 ms, managed memory 56 MB)
ExcelDataReader: 50000 rows into Big (total 1660 ms, managed memory 9 MB)

One run each on the same 1 MB file with three columns, so treat the numbers as an order of magnitude, not a benchmark. ClosedXML loads the whole workbook into memory; ExcelDataReader streams it. For files with tens of thousands of rows, streaming is the safer choice; ClosedXML is easier when you need to read cells by name, check formats or write Excel files too.

6. SqlBulkCopy Errors

System.InvalidOperationException: The given ColumnName 'Employee Id' does not match up with any column in data source.
System.InvalidOperationException: The given value 'seven' of type String from the data source cannot be converted to type int for Column 0 [EmployeeId] Row 1.

The first one is misleading: Employee Id (with a space) did exist in my DataTable; it’s the SQL table that has EmployeeId. Check both sides, and if you build the mappings from the sheet’s headers, normalise the header text (trim, remove spaces) before mapping. The second error names the column and the row, which makes it easy to report back to whoever filled in the sheet.

Which Method to Use

• One-off import by a DBA: OPENROWSET, with explicit columns, casts for money, and a row/NULL count check afterwards. Switch ad hoc queries off again.
• Upload feature in an app: C# with ExcelDataReader (streaming) or ClosedXML, validated per row, written with SqlBulkCopy. Nothing to configure on the server.
• Code-like columns (SKU, ZIP, IDs): text format in Excel, or read them as strings in C#; never trust type guessing.

Reference: OPENROWSET (Transact-SQL) on Microsoft Learn.

Related: SQL injection prevention in C# · SQL Server ‘certificate chain not trusted’ error · C# collections performance · all C# tutorials