Import CSV into MSSQL database

This is really common task in any database project. You always need to get initial data into database. Maybe most common file format is CSV.

We have CSV file in drive C: with name import.csv with following content. The location of the file is C:\import.csv.

id,login,name,surname
1,user1,James,White
2,user2,Johny,Walker
3,user3,Jim,Beam

We will create table with these four columns.

CREATE TABLE CSVImport (
  ID INT,
  Login VARCHAR(20),
  FirstName VARCHAR(40),
  LastName VARCHAR(40),
  BirthDate SMALLDATETIME
);

Now we have everything we need to import the file into database. We will use BULK INSERT to put the data into table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK INSERT CSVImport
FROM 'c:\import.csv'
WITH
(
  FIRSTROW = 2,
  FIELDTERMINATOR = ',',
  ROWTERMINATOR = '\n'
)
GO

Parameter FIRSTROW will tell BULK LOAD that we don’t want to import first line (= 1) because it contains headers.

If you will have table what have less columns than the csv file these columns will be added after text in the last column. If this column will reach maximum length, this line will not be inserted. With MAXERRORS you can specify the maximum number of error lines what are allowed before whole INSERT will be considered as failed.

Possible problems

Permissions

• User which runs bulk insert must have BULKINSERT permissions.
• SQL Sever must have access rights to the file.