SQL SERVER – Import CSV File Into SQL Server Using Bulk Insert – Load Comma Delimited File Into SQL Server

This is a very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.

CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.

Create TestTable

USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO

Create a CSV file in drive C: named csvtest.txt with the following content. The location of the file is C:\csvtest.txt

1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202

Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.

BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO

“Cannot bulk load. Operating system error code 5”

This is the error people hit most often, and the reason catches everybody out once. BULK INSERT does not read the file as you. It reads the file as the SQL Server service account, on the machine where SQL Server is running. So the file has to sit where that account can reach it.

If SQL Server is on another machine, C:\csvtest.txt means that machine’s C drive, not yours. Put the file on the server, or use a share both can see. If it is on your own machine and still fails, give the SQL Server service account read permission on the folder. Not your login, the service account.

When the file has a header row

Most files you get from somebody else start with a line of column names. Load that and SQL Server tries to put the word FirstName into a date column and gives up. Tell it to start on line two.

BULK INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '0x0a'
);

Notice ROWTERMINATOR is 0x0a rather than ‘\n’. If your file came from Windows its line endings are carriage return and line feed, and ‘\n’ on its own can leave an invisible character stuck on the end of your last column. If your last column keeps coming out wrong for no reason, this is why.

When a value contains a comma

This is the one that breaks the script above. A name like “Smith, Robert” is wrapped in quotes in the file, and a plain comma split will tear it in half. From SQL Server 2017 onwards there is a proper answer built in.

BULK INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FORMAT = 'CSV',
FIRSTROW = 2,
FIELDQUOTE = '"'
);

FORMAT = ‘CSV’ teaches BULK INSERT the actual rules of a CSV file, quotes and all. If you are on an older version, you cannot fix this with terminators. Open the file in Excel and save it with a different separator, or load it with the Import Wizard instead.

When some rows are bad

Real files have bad rows in them. Rather than losing the whole load to one broken line, set the bad ones aside and carry on.

BULK INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FORMAT = 'CSV',
FIRSTROW = 2,
MAXERRORS = 100,
ERRORFILE = 'c:\csvtest_errors.txt'
);

MAXERRORS says how many bad rows you will tolerate before it gives up. ERRORFILE writes those rows to a file of their own so you can look at them afterwards. You get two files out of it, the rows themselves and a .Error.Txt explaining what was wrong with each. That second file is the one that saves your afternoon.

The other ways to do this

BULK INSERT is the fastest way to get a file into a table, and it is the right tool when the file is clean and the load is repeatable. It is not the only tool.

OPENROWSET with BULK lets you SELECT from the file first, so you can look at the data and transform it on the way in. Handy when you do not trust the file.

bcp is the command line version. Use it when the load has to run from a script or a scheduled job outside SQL Server.

The Import Flat File Wizard in SSMS is the friendly one. It guesses your column types and creates the table for you. For a one off file, it is genuinely the quickest way, and there is no shame in using it.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.