Bootstrap FreeKB - SQL Server Management Studio - Export an Excel file
SQL Server Management Studio - Export an Excel file


Follow these steps to export data from a table in SQL Server Management Studio to an Excel file:

  1. In the left panel of SQL Server Management Studio, expand Databases

 

  1. Right-click the database that contains the table you want to import data into and select Tasks > Export Data

  1. Do the following in the Choose a Data Source dialog box:
    1. Change Data source to SQL Server Native Client 11.0
    2. Select or type your Server name (example: server1\database1)
    3. Select Use Windows Authentication or Use SQL Server Authentication. If selecting Use SQL Server Authentication, you will need to provide your SQL Server username and password
    4. Select the database that contains the table you want to export data from
    5. Select Next

 

  1. Do the following in the Choose a Destination dialog box:
    1. Change Data source to Microsoft Excel
    2. In the Excel file path field, either type the path or browse to the loction where the Excel file will reside (example: C:\\Users\\User1\\Downloads\\example.xlsx)
    3. Select Microsoft Excel 2007
    4. Select Next

 

  1. Select Copy date from one or more tables or views and select Next

  1. Do the following in the Select Source Tables and Views dialog box:
    1. Select the table to export data from
    2. Select Preview, and verify that the column headers have the expected data in the column
    3. Select Next

  1. The Review Data Type Mapping dialog box provides a summary of the mapping of the data from the SQL Server table to Excel. Select Next
Note: Yellow exclamation points imply a concern with the mapping of the data from SQL Server to Excel. Double-click the yellow exclamation point to view a report of the mapping. The mapping can be modified by selecting Back and then selecting Edit Mapping.

  1. In the Save and Run Package dialog box, select Run immediately or Save SSIS Package.
Note: If you are using the Express version of SQL Server, the only option displayed is Run immediately. If you are using a paid version of SQL Server, you can Run immediately or Save SSIS Package.
  1. If selecting Run immediately:
    1. Select Next
    2. In the Complete the Wizard dialog box, select Finish.
  2. If selecting Save SSIS Package:
    1. Select File system
    2. Select Do not save sensitive data
    3. Select Next
    4. Give the SSIS Package a name (example: ExcelExport1)
    5. Select the directory where you want the SSIS Package to be saved to (example: C:\\Users\\User1\\Downloads\\ExcelExport1.dtsx)
    6. Select Next
    7. In the Complete the Wizard dialog box, select Finish.

Warning! After selecting Finish to run the job, if Operating stopped is displayed and there is an Error, the export failed. In this scenario, select Messages to view detailed information on what caused the export to fail.

  1. Select Close to close the Export wizard.
  2. Navigate to the location where the Excel file was exported to (example: C:\Users\User1\Downloads\example.xlsx) and verify the Excel file exists.
  3. Open the Excel file, and verify the Excel file has the data from the SQL Server table.

If you created an SSIS Package, follow the instructions in the article on How to run a SQL Server SSIS DTSX package using Windows PowerShell.

 

 




Did you find this article helpful?

If so, consider buying me a coffee over at Buy Me A Coffee



Comments


Add a Comment


Please enter 4b5e93 in the box below so that we can be sure you are a human.