04 October 2008 16:44 tonyrogerson

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: ,


# Interesting Finds: October 5, 2008

05 October 2008 16:43 by Jason Haley

# http://check.no/info.php?a%5B%5D=%3Ca+href%3Dhttp%3A%2F%2Fwww.accesssolicitor.com%2Fmilner-elledge-family-solicitors%2Fdagenham%2F5114%3Edivorce+lawyer+romford%3C%2Fa%3E

BCP / BULK INSERT format file - escape a quote, more to the point - loading quoted CSV - Tony Rogerson's ramblings on SQL Server