SQL SERVER – Export Data From SQL Server to Microsoft Excel Datasheet

Question: How to Export Data From SQL Server to Microsoft Excel Datasheet?

Answer: there are three good ways, and which one you want depends on whether this is a one off or something you will run again. I will start with the scripted method, then cover the two easier ones underneath.

Method 1: write straight into a spreadsheet from T-SQL

Enable Ad Hoc Distributed Queries. Run following code in SQL Server Management Studio, Query Editor.

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

Please read this before running it on a shared server. That setting is server wide, not per database, and it lets anybody with permission read files from the server’s own disk through OPENROWSET. On your own machine that is fine. On a production server it is a real change, so ask first, and switch it back off when you are done:

EXEC sp_configure 'Ad Hoc Distributed Queries', 0;
GO
RECONFIGURE;
GO

Create Excel Spreadsheet in root directory c:\contact.xls (Make sure you name it contact.xls). Open spreadsheet, on the first tab of Sheet1, create two columns with FirstName, LastName. Alternatively, you can download sample Spreadsheet from here.

Run following code in SQL Server Management Studio, Query Editor.

USE [AdventureWorks];
GO
INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\contact.xls;',
'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact
GO

Open contact.xls spreadsheet and you will see the first five records of Person.Contact inserted into the first two columns.

Make sure your spreadsheet is closed during this operation. If it is open, it may throw an error. You can change your spreadsheet name as well as the name of Sheet1 to your desired name.

If that gives you “provider is not registered”

This is the error nearly everybody hits, and it has nothing to do with your query being wrong.

Microsoft.Jet.OLEDB.4.0 only exists in 32 bit. SQL Server has been 64 bit for many years now, and a 64 bit process cannot load a 32 bit provider. So the exact code above, which worked perfectly when this post was written, fails on most servers today.

The replacement is ACE, which comes in a 64 bit version, and it handles the newer .xlsx format too.

INSERT INTO OPENROWSET
('Microsoft.ACE.OLEDB.12.0',
 'Excel 12.0 Xml;HDR=YES;Database=C:\contact.xlsx;',
 'SELECT * FROM [Sheet1$]')
SELECT TOP 5 FirstName, LastName
FROM Person.Contact;

Three things have changed and all three matter. The provider is ACE rather than Jet. Excel 12.0 Xml is for .xlsx, where Excel 8.0 was for the old .xls. And HDR=YES says the first row holds column names rather than data.

You need the Microsoft Access Database Engine redistributable installed on the machine running SQL Server, and it must be the 64 bit one to match. Installing the 32 bit version alongside 64 bit Office is its own small adventure, so match the bitness of SQL Server and not of your Office install.

Method 2: the Import and Export Wizard

For a one off export, this is genuinely the fastest route and there is no shame in it. Right click your database in Object Explorer, Tasks, Export Data. Pick your table or paste a query, choose Microsoft Excel as the destination, and it creates the file for you.

No server settings to change, no provider to install on the server, and it makes the columns itself. It also lets you save the whole thing as a package if it turns out you will run it every month.

Method 3: just save the results

Often the honest answer. Run your query in Management Studio, right click the results grid, Save Results As, and choose CSV. Excel opens that quite happily.

One setting is worth turning on first, because without it your column names are not saved: Tools, Options, Query Results, SQL Server, Results to Grid, and tick “Include column headers when copying or saving the results”.

Two things to watch with CSV. A value containing a comma needs quoting, and Excel will helpfully turn a long number like an account reference into scientific notation, or read 03/04 as a date. If either of those would ruin the file, use one of the methods above instead.

Which one should you pick

A one off for a colleague, save the results as CSV. A one off with awkward data, use the wizard. Something that has to run from a stored procedure or a scheduled job, use OPENROWSET with ACE, and switch Ad Hoc Distributed Queries back off afterwards if this is a shared server.

Let me know what you think of this blog post.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.