Question

In the Topic Materials, you learned about exporting SQL Server Data to Excel. Explain the two options for exporting data using the SQL Server Import and Export Wizard, and provide examples of when each option should be selected.

Answer 1

There are several ways to Export/Import SQL server data to an Excel file. In the article a couple of ways to import and export SQL Server data to an Excel file will be explained:

1. Export SQL Server data to an file using

the SQL Server import and export wizard

T-SQL code

Export to Excel from ApexSQL complete

2. 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:

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 Nextbutton:

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 code

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 SQL code in a query editor

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

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, a free 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: