Overview

One of the easiest ways to import excel table data to SQL Server with SSMS, is to upload as CSV. This is a non-programmatic approach to transfer table data from excel to SQL Server. The Excel file is first exported as a comma delimited text file also know as CSV. CSV is one of the most common open source formats along with JSON, Excel, and plain text.

Excel Export to CSV

Open Excel File and Click Save As

Open the Excel File and click on Save As

Save as CSV

After browsing to the location where the file will be saved, choose CSV (Comma delimited)

You should see a new file with the type Microsoft Excel Comma Separated Values File

Import CSV to SQL Server Database

Import a Flat File with SSMS

Right Click on the Database you want perform the import on and select Tasks -> Import Flat File

Click Next

Click on Browse and select the CSV file you want to import. You can also change the table name from here otherwise it will take the name of the file by default.,

After Clicking Browse – Choose the file to upload

Once the file has been selected and table has a valid name, click Next

Preview the columns of the table.

Note: SSMS will only look at a hundred or so row to guess the size and type of each column

Finally, click on finish.

Insert Data Error

First click on the error link and read the message. The message won’t reveal which column is causing the error, but it will display the type of error.

Below is a list of common errors:

• There is a null value but the column is set to Not Null
  • Solution: uncheck the Not Null checkbox
• The estimated column size is too small.
  • Solution: Increase each column by 50 chars.
    • You can always go back in design and reduce the size.
• The estimated column type is incorrect.
  • Solution: Change smallint to int or to varchar(50) if unsure.