04 October 2008 16:44
BCP / BULK INSERT format file - escape a quote, more to the point - loading quoted CSV
I'm forever forgetting how to do this and am currently on a project where I get a ton of CSV files daily and need to load them. The trick with getting rid of the " is to know how to escape the " in a format file.
The data looks like this...
"M1","Title for M1"
"M2","Title for M2"
"M3","Title for M3"
"M4","Title for M4"
How can we use BULK INSERT to load this data without the quotes? There probably is a better way to do this without a) using SSIS and b) relying on openrowset to mangle what the type of data is.
create table csv_matrix (
f1 char(1) not null,
menu_item_code varchar(5) not null primary key clustered,
menu_description varchar(250) not null unique
And the format file is:
1 SQLCHAR 0 1 "" 1 f1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 5 "\",\"" 2 menu_item_code SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 250 "\"\r\n" 3 menu_item_description SQL_Latin1_General_CP1_CI_AS
And the BULK INSERT statement is:bulk insert csv_matrix from 'e:\dataimport\matrix.csv'
with ( formatfile = 'e:\dataimport\matrix.fmt'
The key here is the use of the back slash (\) which escapes the character. I cannot rid myself of the first quote though without a filler column to hold and discard it.
Filed under: SQL Server, SQL Development