Tony Rogerson's ramblings on SQL Server

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:

9.0
3
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:

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.