Bulk Insert Partially Quoted CSV File in SQL Server

I'm trying to import a correctly quoted CSV file, meaning data is only quoted if it contains a comma, e.g.:

41, Terminator, Black
42, "Monsters, Inc.", Blue

I observe that the first row imports correctly, but the second row errors in a manner that suggests the quoted comma was treated as a field separator.

I have seen suggestions such as this one

SQL Bulk import from CSV

to change the field terminator

FIELDTERMINATOR='","'

However, my CSV file only quotes fields that need it, so I do not believe that suggestion would work.

Can SQL Server's BULK IMPORT statement import a correctly quoted CSV file? How?

Unfortunately SQL Server interprets the quoted comma as a delimiter. This applies to both BCP and bulk insert .

From http://msdn.microsoft.com/en-us/library/ms191485%28v=sql.100%29.aspx

If a terminator character occurs within the data, it is interpreted as a terminator, not as data, and the data after that character is interpreted as belonging to the next field or record. Therefore, choose your terminators carefully to make sure that they never appear in your data.

I know this is an old topic but this feature has now been implemented since SQL Server 2017. The parameter you're looking for is FIELDQUOTE= which defaults to '"'. See more on https://docs.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-2017

There is another solution for this.

Consider the quotes as part of the fields delimiter, by editing the fmt file.

You can check this out for more information:

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/02/04/how-to-remove-unwanted-quotation-marks-while-importing-a-data-file.aspx

An extract of the link above:

The only way to remove the quotation marks would be to modify the column delimiters specified during the import operation. The only drawback here is that if you inspect the data to be inserted, you will very quickly realize that the column delimiters are different for each column (Delimiters highlighted above).

So to specify different column delimiters for each column, you would need to use a format file if you plan to use Bulk Insert or BCP. If you generate a format file for the above table structure, it would be as follows:

9.0
3
1       SQLCHAR       0       5       "\t"     1     FName              SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       5       "\t"     2     LName              SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       50      "\r\n"   3     Company            SQL_Latin1_General_CP1_CI_AS

Modify the format file to represent the correct column delimiters for each column. The new format file to be used will look like this:

9.0
4
1       SQLCHAR       0       0     "\""      0     FIRST_QUOTE      SQL_Latin1_General_CP1_CI_AS
2       SQLCHAR       0       5     "\",\""   1     FNAME               SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR       0       5     "\",\""   2     LNAME            SQL_Latin1_General_CP1_CI_AS
4       SQLCHAR       0       50    "\"\r\n"  3     COMPANY          SQL_Latin1_General_CP1_CI_AS