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


Before importing the Excel file, ensure every column in the table except for the ID column is configured to allow null.

Follow these steps to import an Excel file in SQL Server Management Studio:

  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 > Import Data

 

  1. Do the following in the Choose a Data Source dialog box:
    1. Change Data source to Microsoft Excel
    2. In the Excel file path field, either type the path or browse to the Excel file that contains the data you want to import (example: C:\\Users\\User1\\Downloads\\example.xlsx)
    3. Select Next

 

  1. Do the following in the Choose a Destination dialog box:
    1. Change Destination to SQL Server Native Client 11.0
    2. Select or type your Server name
    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 import the data into
    5. 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. In the Destination column, select the table you want to import the data into
    2. Select Edit Mappings.
      • Ensure Append rows to the destination table is selected
      • Ensure the Source and Destination mapping is correct
    3. Select Preview, and verify that the column headers have the expected data in the column
    4. Select Next

Note: Sometimes the Edit Mappings dialog box may have a different Type between the Source and the Destination. In this example, the Source type is LongText and the Destination type is varchar. If this causes the import to fail, there is not much that can be done, as the source type is predeteremined by Excel. In this scneario, you want to to look into some other type of import. This is one of the most annoying issues with import.

  1. In the Review Data Type Mapping dialog box, 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 aae9c3 in the box below so that we can be sure you are a human.