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.
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)
Configure the following SQL settings
-- allows you to change advanced options
-- enables ad hoc distributed queries
-- OLEDB AllowInProcess
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.
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.
DROP TABLE IF EXISTS #Sheet1
Define the path and import the excel data into our temp table.
DECLARE @excelPath NVARCHAR(100) = N'\\C:\Users\carl.paton\Downloads\foo.xlsx'
This example builds on the select above, the data put into
#Sheet1 (temp table) is read and inserted into
Sheet2$ (excel workbook tab)
SELECT id_a, id_b FROM [Sheet2$] is defining the target for the insert. While
SELECT * FROM #Sheet1 is the source data.
DECLARE @writeToExcel NVARCHAR(500) = N'
The result of the insert into
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 62 [Batch Start Line 0]
- This means you have not set
show advanced optionsto 1
Msg 15281, Level 16, State 1, Line 1
- The means you have no set
Ad Hoc Distributed Queriesto 1
Msg 7303, Level 16, State 1, Line 1
- Run SSMS as Administrator
- Check what Microsoft ACE OLEDB providers are installed and their settings
DynamicParameters is set to 1 for your OLEDB version.
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
Msg 7357, Level 16, State 2, Line 10
Sheet2and or the columns
id1, id2dont exist