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.

Tagged on: , ,

10 thoughts on “Import CSV into MSSQL database

  1. Martin M?lek Post author

    @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.

  2. domino99

    See, without keeping realistic expectations from the way oonline casinos
    operate, you’ll be able too find yourself chasing some pretty crazy dreams thhat
    could land you inside the poorhouse within minutes. If you’ve a
    smnall stack now with the tournament, just wait for those good cards and goo
    all in. Different players generally have different Poker strategies; some favouring aggressive tactics,
    while othes play “tight games”, only playing if they
    are sure they will win.

  3. Agen Poker Terpercaya

    The poker industry presently estimated at some 300 million people is forecaszted to grow to around a bilion worldwide participants within the next ten years.
    Your image at thhe tabvle is definitely ann important section of how other
    players will answer your play and perceivbe you. However, iit ought
    too be noted that when a scandal is going to occur itt would probably be
    placed under wraps.

Leave a Reply

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