There are several ways to Export/Import SQL Server data to an Excel file. In this article, a couple of ways to import and export SQL Server data to an Excel file will be explained:
- Export SQL Server data to an Excel file using
- the SQL Server Import and Export Wizard
- T-SQL code
- Export to Excel from ApexSQL Complete
- Import SQL Server data into Excel using the Data Connection Wizard
Export SQL Server data to an Excel file using the SQL Server Import and Export Wizard
In SQL Server Management Studio (SSMS), there is a feature that provides for exporting data from one data source to another data source; in this case, data from SQL Server will be copied to an Excel file trough Export Wizard.
To start to use this feature, go to Object Explorer, right click on any database (e.g. AdventureworksDW2016CTP3), under the Tasks, choose Export Data command:
This will open the SQL Server Import and Export Wizard window:
To proceed with exporting SQL Server data to an Excel file, click the Next button. This will open the Choose a Data Source window. On this window, a source from which you want to copy data can be chosen:
From the Data source drop down box, choose the SQL Server Native Client 11.0 item. Under the Server name drop down box, choose the SQL Server instance from which data need to be copied. In the Authentication section, choose the authentication mode for the data source connection and under the Database drop down box, choose the database from which the data will be copied. After all these options are set, click the Next button to proceed.
This will open the Choose a Destination window in which can be specified where to copy data from the data source:
In the Destination drop down box, choose the Microsoft Excel item. Under the Excel file path, choose the destination for copied data from SQL Server data source (SQL Data.xlsx). In the Excel version drop down box, choose the version of the Microsoft Excel worksheet. After these are all set, press the Next button to continue. But after pressing the Next button, the following warning message may appear:
The operation could not be completed.
ADDITIONAL INFORMATION:
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
This happens because SSMS is a 32-bit application and when you launch the Export Wizard via SSMS, it launches a 32-bit version of Export Wizard. On the other hand, the machine (Operating system) on which SSMS is installed is a 64-bit version and the installed SQL Server is a 64-bit version.
To solve this problem, close SSMS, go to the Start menu and find the 64-bit version of SQL Server Import and Export Data:
After launching this SQL Server Import and Export Wizard, it will be prompted with the same window that is used via SSMS. After setting all previous options, click the Next button. The Specify Table Copy or Query window will appear:
On this window, whether to copy data from one or more tables and views or to copy the results of a query can be specified.
Select the Copy data from one or more tables or views radio button and click the Next button. The Select Source Table and Views window allows choosing one or more tables and views from which you want to export SQL Server data to an Excel file by clicking the check box next to the name of the table/view:
As it may be noticed, as soon as the check box next to a table/view is checked, the name of the table/view will be copied under the Destination column. This name represents the name of the sheet where the data from the table will be put, this name can be changed to whatever you like, but for now, it will be left as it is:
To preview which data will be generated to an Excel file, click the Preview button:
After choosing which tables/views data will be copied to an Excel file, click the Next button:
On the Save as Run Package window, check the Run immediately check box and click the Next button:
The Complete the Wizard window shows all options that are chosen for exporting operations. To finalize the process of export SQL Server data to an Excel file, click the Finish button. The last SQL Server Import and Export Wizard window shows the state of the data have been successfully exported or some errors occurred during exporting data:
In our case, the data were successfully generated into the SQL Data.xlsx file in the DimScenario sheet:
Export SQL Server data to an Excel file using T-SQL code
The Transact-SQL OPENROWSET can be used to export SQL Server data to an Excel file via SSMS. In a query editor type and execute the following code:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM [Sheet1$]')
SELECT * FROM dbo.DimScenario
But, when executing the above code, the following error may occur:
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.
To resolve this error the Ad Hoc Distributed Queries option should be enabled This can be done by using the sp_configure procedure and executing the following SQL code in a query editor:
EXEC sp_configure 'show advanced options', 1 RECONFIGURE EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE
After executing the above code, the following message will appear that indicate that the show advanced options and Ad Hoc Distributed Queries options are enabled:
Configuration option ‘show advanced options’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Configuration option ‘Ad Hoc Distributed Queries’ changed from 0 to 1. Run the RECONFIGURE statement to install.
Now, when again the code is executed:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM [Sheet1$]')
SELECT * FROM dbo.DimScenario
The following error may appear:
OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)” returned message “The Microsoft Access database engine cannot open or write to the file ”. It is already opened exclusively by another user, or you need permission to view and write its data.”.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider “Microsoft.ACE.OLEDB.12.0” for linked server “(null)
This usually happens because of inadequate permissions.
More about how to resolving these issues can be found in the How to query Excel data using SQL Server linked servers page.
One way for resolving this issue is to open SSMS as an administrator and execute the code again. But this time, another error may appear:
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.
To resolved this, open excel file (e.g. SQL Data.xlsx) for which is planned to store the data from SQL Server table (e.g. dbo.DimScenario) and enter the column names which will represent the column names from the DimScenario table:
Close the SQL Data.xlsx file and once again execute the code:
Now, the following message will appear:
(3 rows affected)
Finally, the data from the SQL Server table are copied into the Excel file:
Import SQL Server data into Excel using the Data Connection Wizard dialog
Another way to copy data from SQL Server table to an Excel file is to use the Data Connection Wizard dialog from Excel.
In order to achieve that, open an Excel file (e.g. SQL Data.xlsx) in which you want to import data. In the Data tab, under the From Other Source sub-menu, choose the From SQL Server command:
This will open the Data Connection Wizard dialog. In the Server name, enter a name of the SQL Server instance from which data need to be copied. In the Log on credentials section, choose the authentication mode for the data source connection and click the Next button:
From the Select the database that contains the data you want drop down box, choose the database from which the data will be copied. In the grid, all available tables and views will be listed. Choose a desired table / view (e.g. DimScenario) and click the Next button to proceed.
On this window, leave everything as it is and press the Finish button:
In the Import Data dialog, choose the Table radio button, under the Where do you want to put the data? section, choose the Existing worksheet radio button and press the OK button:
After pressing the OK button, the data from the DimScenario table will be copied to the SQL Data.xslx file:
Export SQL Server data to an Excel file using Export to Excel from ApexSQL Complete
Using the Export to Excel feature from ApexSQL Complete, add-in for SSMS and Visual Studio, exporting data from the SQL Server table to an Excel file can be done by one click.
In a query editor, type and execute the following code:
SELECT * FROM dbo.DimScenario ds
From the Results grid, select data that you want to export to an Excel file, right click in the Result grid and, from the context menu, choose the Export to Excel option:
This will open a new Excel workbook and the selected data from the Results grid will be copied in the first worksheet:
More about exporting to Excel via ApexSQL Complete can be found on the Export to Excel page.
See also:
- Tutorial: Import Data into Excel, and Create a Data Model
- Get started with this simple example of the Import and Export Wizard
- How to query Excel data using SQL Server linked servers
- Export to Excel
September 11, 2017
