Skip to content

Import CSV Files into SQL Server

Did you ever had a CSV file and needed a simple way to import it into SQL Server? If so, try the BULK INSERT command. As long as you do not have any NULL values, you will not find a simpler way.

Your CSV file

Create your CSV file (a file with values separated by commas) without a header or an empty line at the end. It can contain umlaute, but not NULL values and should use the encoding UCS-2 BE BOM:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
1;Gilmoré;Luneä;1956.08.15
2;Lloyd;Evèlyn;1977.08.13
3;Faulkner;Eagan;1948.08.05
4;Fletcher;Chantale;1987.07.23
5;Webster;Yetta;1980.01.13
6;Griffin;Keelie;1939.05.14
7;Mcintosh;Bert;1953.04.07
8;Pope;Brielle;1980.10.13
9;Harmon;Zephania;1984.09.27
10;Elliott;Kirestin;1932.10.27

Create the table

The table must be an exact match for the values in your CSV file, including the order of the columns:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
CREATE TABLE dbo.Authors (
    Id [int] IDENTITY(1,1) NOT NULL,
    LastName [nvarchar](50) NOT NULL,
    FirstName [nvarchar](50) NOT NULL,
    BirthDay [date] NOT NULL,
CONSTRAINT [PK_dbo_Authors] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, 
    STATISTICS_NORECOMPUTE = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, 
    ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

The BULK INSERT command

This BULK INSERT command has various options: You can set the character that splits fields, define how lines are separated, select the encoding (via codepage parameter) and define where in the file the import should start:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
BULK
INSERT [dbo].Authors
FROM 'c:\A\authors.csv'
WITH
(
FIELDTERMINATOR = ';',
ROWTERMINATOR = '\n',
CODEPAGE = 'ACP', -- for the umlaute
FIRSTROW = 1, -- starts with 1 for the first row
--DATAFILETYPE = 'char',
KEEPIDENTITY
)
GO

The Result

When the command has finished you will find your data inside your table in SQL Server:

The data from the CSV file is now in a table

Alternatives when some values are NULL

In this case, the simple command for BULK INSERT is not going to work. A more complex yet more flexible approach is to use format files for the import or the bulk copy program bcp.

Conclusion

For simple files is the BULK INSERT a helpful command that can load your CSV file into SQL Server. Try it before you use a more elaborate approach like format files or write your own tool to load the data.