Press "Enter" to skip to content

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.

8 Comments

  1. Klaus
    Klaus January 27, 2010

    if BULKINSERT permission is not granted ( because im using a service provider) is there another way?

  2. Martin M?lek
    Martin M?lek January 27, 2010

    @Klaus
    You can try this, but you will need to have the Ad Hoc Distributed Queries enabled.
    SELECT *
    FROM OPENROWSET(?MSDASQL?,’Driver={Microsoft Text Driver (*.txt; *.csv)}; DEFAULTDIR=C:\t.csv; Extensions=CSV; HDR=No;?,’SELECT * FROM t.csv?)

    In all cases you can write a script which will import the data manually. Parsing the csv file and running the insert is easy to write.

  3. Dr JB
    Dr JB November 23, 2011

    You forgot to add the birthdate in the import.csv
    After that it works fine on MS SQL

    cheers

  4. Nirbhav Gupta
    Nirbhav Gupta December 30, 2011

    If you want to add csv data in to data base than use it………

  5. vikas
    vikas July 30, 2012

    @Martin M?lek
    Hi Klaus: My requirement is:
    I have to insert csv data daily in to database by Using Mysql and scripting launuage.
    so please help me.
    Thanks in advance..

  6. Volker
    Volker February 28, 2013

    Many thanks for your help. :-)

  7. R3BL
    R3BL March 18, 2013

    Just wanted to thank you for your help. This was extremely helpful :)

Leave a Reply to Klaus Cancel reply

Your email address will not be published. Required fields are marked *