In a previous article, SQL and Excel: Why you need both, we showed that for many businesses the use of both SQL Server and Excel is an optimum approach to data management. For organizations that use SQL Server and Excel extensively, there is often a need to export data from SQL Server to Excel. The most common reasons to get the data into Excel are to:
- produce data visualizations such as charts and graphs for use in reports
- aggregate or analyze data in pivot charts
- collate data from multiple sources for further transformation
- make the data available to non-database users to view or process
In this article, we’re going to look at how to export data from SQL Server to Excel. The first method uses the SQL Server Import and Export Wizard, which can be used to export data to a number of destination types, not just Excel. The second method uses native functionality in Excel. The third method uses the SQL Spreads Add-In for Excel, which provides functionality beyond the export process. There are pros and cons of each method, which we’ll summarise at the end of the article.
Method (1): Exporting using SQL Server Import and Export Wizard
The SQL Server Import and Export Wizard can be launched from within SQL Server Management Studio or as a stand-alone application.
From within SQL Server Management Studio, click on the database from which you want to export data. We’re using the demo database in our example, so click on that and then right-click and select Tasks > Export Data.
The Welcome page is displayed, and you can go ahead and click ‘Next’.
On the next screen, select the data source. To export from SQL Server, select ‘SQL Server Native Client’.
Once you’ve selected ‘SQL Server Native Client’, the details about the data source will be populated by the wizard.
Click ‘Next’ to go to the next screen, where you going to choose the destination for the export, which for us is Excel.
When you select Excel as the choice of destination, you then need to provide the location of the Excel file. When you then click ‘Next’, you may see the following message:
The operation could not be completed.
ADDITIONAL INFORMATION:
The ‘Microsoft.ACE.OLEDB.16.0’ provider is not registered on the local machine. (System.Data)
The reason this message appears is that when you launch the SQL Server Import and Export Wizard from SSMS, a 32-bit version of the wizard is launched (because SSMS is 32-bit) and your operating system is 64-bit. There is more information on this here.
To get around this, you need to close SSMS and go to the Windows Start menu and search for the 64-bit version of the SQL Server Import and Export Wizard from the Start menu. Note that this version will only be installed on your computer if you have installed SQL Server.
Once the stand-alone version of the Wizard has started, you can go through the steps as discussed so far, which was to specify the source and destination of the data copy. We now need to define what we want to copy, and (optionally) configure the copy operation.
After clicking ‘Next’ on the ‘Choose a Destination’ screen, you specify whether you want to export one or more complete tables or views, or a subset of data via a SQL query. In our case, we are exporting from a single table, so we can leave the default option selected.
Next, we’re going to edit the column mappings by clicking the ‘Edit Mappings…’ button. The Column Mappings screen allows you to make changes to the name and data type of the columns that will be created in Excel by the copy operation. In our example, we’re going to change the data type for the ‘order_date’ column from VarChar to DateTime. We’ll leave the others as the default set by the wizard. Click ‘Next’. The next screen in the wizard summarizes that the source is a SQL table called sales and the destination will be to a sheet called ‘sales’ in the Excel workbook. You can change the name of the sheet as needed.
Once done, click ‘Ok’ to close the dialog and then ‘Next’.
If any of the mappings that we specified may not succeed in the export process the SQL Server Import and Export Wizard shows the Review Data Type Mapping page. This page will highlight the conversions that the wizard needs to perform to handle the mappings that you have specified. It also indicates what the wizard will do if a there is an error during the conversion. It can either ignore the error or cause the export to fail. In our example, several columns have been flagged: the ones where there is a conversion from nvarchar(max) to its Excel equivalent, Long Text, and also the conversion from VarChar to DateTime for the ‘order_date’ column. The default setting is for the wizard to fail if there is an error, so we’ll leave that option in place.
Note: This SQL Import and Export Wizard uses SSIS under the hood; in other words, each time you run the wizard, you are creating an SSIS package. This is why you are given the option of saving it for future use.Click ‘Next’. The wizard now offers you the chance to save the Wizard configuration as an SSIS package in addition to the default option of running the copy immediately.
We are just going to leave the default option ticked and then click the ‘Next’ button.
The wizard now displays a summary of the steps that we have configured.
Click ‘Finish’, and the wizard will now show the final screen, which is the result of the execution. In our case, the execution was successful, and we can see that 5484 records were copied to the ‘Query’ sheet in our Excel workbook.
We can check that the data has been exported from SQL Server to Excel by opening up the file that we specified and checking the ‘sales’ sheet.
Now that the data is in Excel, we can create some visualizations or aggregations, or whatever other processing we want to perform. For example, we could create a pivot chart to display the Total per item per region.
Method (2): Exporting using native functionality in Excel
Although the method itself is much the same, Microsoft has made improvements in the last few releases to the way you get data from other sources into Excel. The main change was the addition of the Power Query data import wizards and tools as a native component in Excel 2016. These are accessed from the Power Query Editor, although it’s not displayed as such on the Excel Ribbon – instead, it is branded as Get & Transform Data group on the Data tab of the Excel ribbon.
The steps described below are valid for Excel 2016 and beyond.
To get started with the export from SQL Server to Excel, click on Data > Get Data > From Database > From SQL Server Database.
You are next prompted to enter the database server to connect to and, optionally, specify a database. If you don’t specify a database, you can select from the available ones on the next screen.
Click ‘Ok’. You’ll then be prompted to provide authentication details. The default is Windows, so you can go ahead and click ‘Connect’.
The next screen allows you to navigate through the databases and tables on the database server that you specified. Click on the ‘demo’ database to expand it and then click on the ‘sales’ table. The data for the selected table is displayed in the right-hand window. We could now click the ‘Load’ button to get the data into Excel immediately. Alternatively, we can click on ‘Transform Data’ to perform transformations on the data (eg change data types, clean up some data, perform computations). For this example, we’re just going to load the data as it is, so go ahead and click ‘Load’.
The data is now exported from the table in SQL Server and loaded into a new sheet in Excel.
That is all we need to do to perform a simple export of a full table from SQL Server to Excel. Aside from the simple procedure we’ve described here, the Power Query data import wizards and tools provide ways to ‘shape’ the data from external sources. For example, you can remove a column, change a data type, merge tables to meet your specific needs.
Method (3): Exporting using SQL Spreads Add-In for Excel
If you don’t already have the SQL Spreads Add-In for Excel installed, you can download a copy here.
Once SQL Spreads is installed, you’ll see that it has been added as a new ribbon tab; go here and click the Design Mode button.
In the SQL Spreads Designer panel on the right side, click the Edit button to open the SQL Server connection dialog.
Enter the name of your SQL Server into the SQL Server name field. You also need to choose an authentication method: Windows-login (Windows Authentication) or SQL Server Authentication (using a name and password set up in SQL Server). Windows authentication is the more secure of the two options.
Click OK. SQL Spreads will try to connect to the database. If the connection is successful, your databases will show up in the SQL Spreads Designer panel.
We can now expand the demo database and select the sales table.
When you click on the table, the data is immediately exported from SQL Server and copied to a sheet called ‘sales’ in Excel.
That’s it! We’ve managed to export data from SQL Server to Excel with just a couple of clicks.
There are several more options available to us in the SQL Spreads Designer. For example, we can:
- filter the data that is exported from SQL Server
- set whether certain columns are displayed and/or read-only
- use a look-up value to display text instead of an id for a column
- sort data
- make updates in Excel and save changes to SQL Server
There is more information on these features in the SQL Spreads knowledgebase here.
Summary
Being able to export data from SQL Server to Excel is a common requirement in most businesses. We’ve looked at three methods for performing this day-to-day task. The first uses the SQL Server Import and Export Wizard, which is native functionality in SQL Server. The second method uses the native ‘Get & Transform’ tools in Excel. The third uses the SQL Spreads Add-In for Excel.
So which method should you use?
If you normally work with databases, and SSMS is your second home, you’ll probably feel that the SQL Server Import and Export Wizard is the best option for you. If you are an Excel user, then it makes sense to work from within Excel itself to export data from SQL Server. Both of the two Excel options that we’ve looked at are robust and easy to use, but SQL Spreads has a distinct advantage because it allows users to make updates to data directly in Excel and then save the data back to SQL Server. This is a huge advantage for those organizations that like the best of both worlds: the ease of use and familiarity of Excel, with the power and data integrity associated with SQL Server.
Try SQL Spreads to see how quick and easy it is to export data from SQL Server to Excel; download a free trial version today.
*This article was originally published on August 26, 2021 and was updated on July 4, 2022 to include some new screenshots.
Leave a Reply
No comments yet. Be the first!