Importing Excel Data Into SQL Server With OPENROWSET

OPENROWSET lets a T-SQL statement read an Excel file as if the sheet were a table. When it works it is the shortest route there is: one INSERT ... SELECT, no wizard, no external script, easy to put in a stored procedure. It comes with real requirements and real risks, and this post covers both. It belongs to the cluster on getting Excel data into a database; the point-and-click alternative is the Import Wizard.

What has to be in place

Three conditions must hold before the first query can succeed.

The provider is installed on the server. OPENROWSET runs inside the database engine, so the OLE DB provider must be on the machine running SQL Server, not on yours. For .xlsx and .xlsb files that provider is the Microsoft Access Database Engine (ACE), installed from the Microsoft redistributable. The SQL Server process is 64-bit, so install the 64-bit ACE. It is listed in SSMS under Server Objects, Linked Servers, Providers as Microsoft.ACE.OLEDB.12.0 (or .16.0 for newer builds). It is unavailable on Azure SQL Database, which cannot read local files at all.

Ad Hoc Distributed Queries is enabled. This server option is off by default:

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

Without it, the query fails with error 15281, saying that SQL Server blocked access to the OpenRowset/OpenDatasource component. Turning it on lets any user with the right permission build a query that connects to an arbitrary data source from the server, which is why security guidance recommends leaving it off. If you enable it for a load, turn it off afterward, or restrict who can run such queries.

The provider is allowed to run in process. Some installations also need provider options set, which you do in SSMS under the provider's Properties: "Allow inprocess" and "Dynamic parameter" ticked. The error "Cannot create an instance of OLE DB provider ... for linked server (null)" (error 7302) points here, and it can also point to a missing install or to the wrong bitness.

The query

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

The three arguments are the provider name, the connection string and the query. In the connection string, Excel 12.0 Xml selects the XLSX flavor, HDR=YES says the first row holds column names, IMEX=1 asks the provider to treat columns with mixed types as text, and Database= is the path to the file. The query reads the sheet named Orders, written [Orders$]: the dollar sign marks a sheet, and square brackets protect names with spaces. A named range or an Excel Table is read without the dollar sign, as [OrdersTable], and to read part of a sheet use a range: [Orders$A1:D200].

The result is a table with the columns named in the header row, typed by the provider. Run it once with SELECT * and look at the types before you build anything on top.

Where the file path points

The path in the connection string is resolved by the SQL Server service, on the server's file system, with the service account's permissions. A local path such as C:\data\orders.xlsx means the C drive of the server, not of the machine you typed it on. A mapped drive letter is not available to the service. Use a local folder on the server or a UNC share (\\fileserver\incoming\orders.xlsx), and grant the service account read access to it. If the file does not open, the message is a generic "The Microsoft Access Database Engine cannot open or write to the file" followed by a possible cause, and the usual causes are the path, the permissions, a file open in Excel and a temp folder the service account cannot write to. The ACE provider writes temporary files while reading, to the temp directory of the service account, so that account needs write access there.

Because the server reads files you name, a stored procedure that builds this connection string from user input is a path traversal and file-read risk. Never let a user supply the path. Take the path from a table of allowed files, or from a constant.

Load into a staging table

The load is an INSERT ... SELECT. Use a staging table with text columns and convert afterwards:

CREATE TABLE dbo.stage_orders (
    batch_id    int           NOT NULL,
    source_file nvarchar(260) NOT NULL,
    order_no    nvarchar(50)  NULL,
    customer    nvarchar(200) NULL,
    amount_eur  nvarchar(50)  NULL,
    ordered     nvarchar(50)  NULL
);

INSERT INTO dbo.stage_orders (batch_id, source_file, order_no, customer, amount_eur, ordered)
SELECT 42, N'orders.xlsx',
       CAST([Order No] AS nvarchar(50)),
       CAST([Customer] AS nvarchar(200)),
       CAST([Amount (EUR)] AS nvarchar(50)),
       CAST([Date] AS nvarchar(50))
FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0 Xml;HDR=YES;IMEX=1;Database=C:\data\orders.xlsx',
    'SELECT * FROM [Orders$]'
);

The CAST to nvarchar is where a quiet problem hides: if the provider decided that Order No is numeric, the value is already a float by the time you cast it, and the leading zeros are gone. The cast cannot bring them back. Check the first rows of the raw SELECT * before you trust a cast. When the column is mixed, IMEX=1 and the provider's registry setting ImportMixedTypes decide whether it arrives as text; the default is text, but confirm with a test sheet. A column that is all numeric stays numeric no matter what you cast it to afterwards, so numeric-looking identifiers are better protected by formatting them as text in the workbook or by reading the sheet with another tool.

The number of header columns is limited by the sheet's used range. Also, column names containing characters such as parentheses or periods may be altered by the provider: a dot becomes a hash sign in some versions. Selecting by position (F1, F2) is possible when HDR=NO, and then you assign the names yourself in the SELECT.

Check, then convert

As in every route, compare the staged count with the sheet's data rows (validating row counts after an Excel import), then convert:

INSERT INTO dbo.orders (order_no, customer, amount_eur, ordered_on)
SELECT order_no,
       customer,
       TRY_CONVERT(decimal(12,2), amount_eur),
       DATEADD(day, TRY_CONVERT(int, TRY_CONVERT(float, ordered)), '1899-12-30')
FROM dbo.stage_orders
WHERE batch_id = 42;

The double conversion handles a serial date that arrived as text like 45292 or as a float like 45292.0. TRY_CONVERT yields NULL for what it cannot convert, so follow the insert with a query for the rows where a source value was present and the converted one is NULL. Those are the rejects to review. If the provider returns dates as datetime values, which it does for date-formatted cells, then the ordered column holds text such as 2024-01-01 00:00:00 and you convert it with TRY_CONVERT(date, ordered) instead. See importing Excel dates correctly into a date column for telling the cases apart.

Doing it more safely

Because the feature is powerful, wrap it:

• Do the load from a controlled procedure that takes a file name from a table of approved files and builds the path from a fixed folder.

• Enable the option only for the load. Turn Ad Hoc Distributed Queries on, run the job, turn it off. It needs sysadmin to change, which makes it awkward for scheduled jobs, so consider a linked server instead.

• Use a linked server to the ACE provider, with a fixed data source. It avoids the ad hoc option, because a linked server is configured in advance by an administrator, and users query it by name. This is the cleaner design for a recurring job.

• Limit the service account. It should read the incoming folder and nothing more.

If these controls sound like a lot, they are a reason to choose another route for anything beyond a one-off.

Alternatives that avoid the provider

A frequent pattern in shops that dislike the ACE dependency is to convert the sheet to CSV outside the database and use BULK INSERT or bcp, which need no OLE DB provider and are fast:

BULK INSERT dbo.stage_orders_csv
FROM 'C:\data\orders.csv'
WITH (FORMAT = 'CSV', FIRSTROW = 2, CODEPAGE = '65001');

FORMAT = 'CSV' handles quoted fields in SQL Server 2017 and later, and CODEPAGE = '65001' reads UTF-8. Another option is a small Python or PowerShell job that reads the workbook and inserts through a normal connection, with parameters and no server-side file access (importing an Excel sheet into PostgreSQL with Python shows the same pattern for another database). These routes put the file handling outside the server, which is where untrusted files belong.

When OPENROWSET is the wrong way to read Excel

The server is managed or shared. Providers and ad hoc queries may be disallowed, and enabling them for one team affects everyone on the instance.

The file comes from outside. Parsing an untrusted workbook with a native provider inside the database process is a risk you can avoid by parsing elsewhere.

You need reliable types. The provider's guessing is the same as the wizard's. For control, read with a library.

The workbook is large or the load is frequent. CSV and BULK INSERT are faster and simpler to operate.

The syntax is documented in the OPENROWSET reference and the server option in the page on Ad Hoc Distributed Queries. To check a small extract as JSON, use the CSV to JSON converter.