SQL Read, Write using Excel file

From SQL Server Management Studio (SSMS) we can read and write excel files using OLE DB (Object Linking and Embedding, Database). The examples below use OPENROWSET which may not be suitable for production environments.

Setup

Machine

This is for a windows machine and you will need to install

I used Google sheets to create the example sheets. These are then downloaded locally as foo.xlsx (File -> Download -> Microsoft Excel .xlsx)

data in sheets

SQL

Configure the following SQL settings

1
2
3
-- allows you to change advanced options
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE
1
2
3
-- enables ad hoc distributed queries
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE
1
2
-- OLEDB AllowInProcess
EXEC master.[sys].[sp_MSset_oledb_prop] N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1

Read from Excel

Reading the file to results, this will test that you can connect to the file and your OLEDB settings are all sweet. Here Sheet1 is the workbook tab with the data.

1
2
3
4
5
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.16.0',
'Excel 12.0 Xml;
Database=C:\Users\carl.paton\Downloads\foo.xlsx',
[Sheet1$]);

all the data from Sheet1$

Read the data from the excel file and insert it into a temp table, the temp table can then be used to perform business logic. Additionally I inserted this data into foo.MyData so it can be read by the write example.

1
2
3
4
5
DROP TABLE IF EXISTS #Sheet1
​
CREATE TABLE #Sheet1 
(IdA UNIQUEIDENTIFIER PRIMARY KEY,
IdB UNIQUEIDENTIFIER)

Define the path and import the excel data into our temp table.

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @excelPath NVARCHAR(100) = N'\\C:\Users\carl.paton\Downloads\foo.xlsx'

DECLARE @importFromExcel NVARCHAR(500) = N'
INSERT INTO #Sheet1 ([IdA], [IdB])
SELECT id_a, id_b
FROM OPENROWSET(''Microsoft.ACE.OLEDB.16.0'',
''Excel 12.0 Xml;Database='+@excelPath+''',
[Sheet1$])
WHERE id_a IS NOT NULL';

EXEC sp_executesql @readFromExcel

SELECT * FROM #Sheet1

result of the select from #Sheet1

Write to Excel

This example builds on the select above, the data put into #Sheet1 (temp table) is read and inserted into Sheet2$ (excel workbook tab)

Here SELECT id_a, id_b FROM [Sheet2$] is defining the target for the insert. While SELECT * FROM #Sheet1 is the source data.

1
2
3
4
5
6
DECLARE @writeToExcel NVARCHAR(500) = N'
INSERT INTO OPENROWSET(
''Microsoft.ACE.OLEDB.16.0'',
''Excel 12.0 Xml;Database='+@excelPath+';'',
''SELECT id_a, id_b FROM [Sheet2$]'')
SELECT * FROM #Sheet1'

The result of the insert into Sheet2$

result of insert into Sheet2$

Errors

Advanced options

1
2
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0]
The configuration option 'Ad Hoc Distributed Queries' does not exist, or it may be an advanced option.
  • This means you have not set show advanced options to 1

Ad Hoc Distributed Queries

1
2
Msg 15281, Level 16, State 1, 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.
  • The means you have no set Ad Hoc Distributed Queries to 1

Cannot initialize the data source

1
2
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".
  • Run SSMS as Administrator
  • Check what Microsoft ACE OLEDB providers are installed and their settings
1
EXEC master.dbo.sp_MSset_oledb_prop;

Ensure AllowInProcess and DynamicParameters is set to 1 for your OLEDB version.

1
2
3
4
5
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1 

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1

Object has no columns

1
2
Msg 7357, Level 16, State 2, Line 10
Cannot process the object "SELECT id1, id2 FROM [Sheet2$]". The OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
  • Sheet2 and or the columns id1, id2 dont exist

References