Search notes:

SQL Server: bulk insert

Simple example

The following simple example creates a table and inserts a small CSV file into the table: 
drop table if exists dbo.csv_simple;

create table dbo.csv_simple (
  id     integer,
  val_1  varchar(10),
  val_2  varchar(10),
);


bulk insert dbo.csv_simple
from 'C:\Users\Rene\github\about\MSSQL\t-sql\statements\bulk-insert\01-basic\data.csv'
with (format = 'CSV');
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/01-basic/bulk-insert.sql
This is the imported CSV file. The format='CSV' specification of the bulk insert statement mandates that the file is RFC 4180 compliant. 
1,one,foo
2,two,bar
3,three,baz
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/01-basic/data.csv

Loading CSV files with unix line endings

A CSV file with Unix line endings (hexadecimal 0x0a) can only be loaded if the row terminator is explicitly stated. Otherwise, the following error is shown Bul load failed due to invalid column value in CSV data file …. 
drop table if exists dbo.csv_unix;

create table dbo.csv_unix (
  id     integer,
  val_1  varchar(10),
  val_2  varchar(10),
);


bulk insert dbo.csv_unix
from 'C:\Users\Rene\github\about\MSSQL\t-sql\statements\bulk-insert\unix-line-endings\data-unix.csv'
with (
   format          = 'CSV' ,
   rowTerminator   = '0x0a'
);
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/unix-line-endings/bulk-insert.sql
The same data (but with Unix line endings), which of course is not visible on a web page: 
1,one,foo
2,two,bar
3,three,baz
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/unix-line-endings/data-unix.csv

Data with header

Often, CSV data comes with a header. In this case, the firstRow specifier is needed. 
drop table if exists dbo.data_with_header;

create table dbo.data_with_header (
  id     integer,
  val_1  varchar(10),
  val_2  varchar(10),
);


bulk insert dbo.data_with_header
from 'C:\Users\Rene\github\about\MSSQL\t-sql\statements\bulk-insert\header\data-with-header.csv'
with (
  format    = 'CSV',
  firstRow  =     2
);
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/header/bulk-insert.sql
id,val_1,val_2
1,one,foo
2,two,bar
3,three,baz
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/header/data-with-header.csv

Semicolon (or generally non-comma) separated values

Often, the CSV file to be imported comes with data whose values are semicolon separated. In this case, the separator needs to be specified with fieldTerminator=: 
drop table if exists dbo.semicolon_separated_data;
go

create table dbo.semicolon_separated_data (
  id     integer,
  val_1  varchar(10),
  val_2  varchar(10),
);
go

bulk insert dbo.semicolon_separated_data
from '$(curdir)\header-and-semicolons.csv'
with (
  format          = 'CSV',
  firstRow        =  2   ,  -- start with second row because first row is the header
  fieldTerminator = ';'     -- data is separated with semicolons
);
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/semicolons/bulk-insert.sql
The sample data that is imported: 
id;val_1;val_2
1;one;foo
2;two;bar
3;three;baz
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/semicolons/header-and-semicolons.csv
With sqlcmd, it is possible to specify the directory where the CSV file resides with a variable whose value can be passed when sqlcmd is invoked with -v. 
sqlcmd -S . -d tq84_db -v curdir="%CD%" -i bulk-insert.sql
Github repository about-MSSQL, path: /t-sql/statements/bulk-insert/semicolons/load-data.bat

See also

T-SQL statements

Index