Import CSV Files to SQL Server Process and Common Error

Introduction

After deploying your applications, the user will give you his/her existing data to import you on your new systems. This article describes the way of importing csv/Excel file to SQL server on your existing table or in new table.

Background

I have chosen CSV due to accept file systems. To import Excel, you need to set up Microsoft Excel on your server first. So, I have used CSV to do it. Just save as CSV format and you can start work.

Using the Code

First save your existing Excel to CSV format from Excel save as options.

It's done! So our File is ready to import.

Now we will open your SQL Server Management Studio.

Right click on your database->Task->Click on Import Data

You will see an Import Wizard to import file.

Click on Next to choose Data Source. As we have planned import from CSV, so select Flat file source.

Browse file name and select your CSV file.

So you can take a look at your data.

Select Columns from Left menu. You will see columns and data.

1. Change your row delimiter and column delimiter
2. Click on advanced and you can
   1. Change your columns name
   2. DataType
   3. Columns Width change
   4. Delete unused columns from here

It's very important to import for existing table structure.

Then, you can preview your data by clicking from left preview menu.

Now click on next to forward to manage destinations.

Choose your destination-> Select Server Name-> Then choose Authentications.

If Use SQL Server Authentication, then fill your server Username and Password. If all are correct, it will load Databases automatically, then select a database.

If you want to import data into new database, select new.

Then click on next.

You will get options for writing Destination Table Name. If you want, you can change your table map.

Then, click Next to review your mapping.

Click Next, then it will be ready to run -> click next to run and click finished.

If all are OK, then it will run, otherwise fail and show you an error message.

Click on error messages, please read it carefully to know details about what the problem is like below:

If error is found, see message carefully and back and solve. After solving, go forward and run it again.

Here, my file was opened, just why does it show error.

All tasks will be green that are successfully imported.

Here are some common errors that occur frequently.

1. Files are open /by used another process
2. Data Columns width problem
3. Duplicate columns in CSV file

So before uploading, check it properly.

History

• 26^th August, 2016: Initial version