SQL Server Bulk Insert Using a Format File to Read Data that also Includes a Comma in the String

There are a lot of resources out there on the net about using SQL Server Bulk Insert, and there are a lot of technical discussions about the inner details of Format Files. The problem is that there aren't many concrete examples of a very common need: importing a CSV (comma-separated) file where a quoted string also includes a comma. Here's an example of what some data might look like:

ID,Name,Phone 
100983,"Jones, Frank",555-1212
118928,"Smith, Joe",555-1313
115454,"Franklin, Alibaster",555-1414

Using a bulk insert statment without a format file would give us data that would look like this:

ID	Name	Phone
100983	"Jones	Frank"	555-1212
118928	"Smith	Joe"	555-1313
115454	"Franklin	Alibaster"	555-1414

When in reality, we want the data to look like this:

100983	Jones, Frank	555-1212
118928	Smith, Joe	555-1313
115454	Franklin, Alibaster	555-1414

The format file is your friend! Basically, you'll create a format file that looks like this:

8.0
3
1 SQLCHAR 0 6 ",\"" 1 empid SQL_Latin1_General_Cp437_BIN 2 SQLCHAR 0 50 "\","
2 name SQL_Latin1_General_Cp437_BIN 3 SQLCHAR 0 10 "\n"
3 phone SQL_Latin1_General_Cp437_BIN

Here's how the above breaks down: 8.0 is the version. That's constant. For SQL Server 2005, anyway. :) 3 tells the Bulk Insert statement how many fields there are in each row. Then we have the three fields listed: 1, 2, and 3 are the field numbers. 6, 50, and 10 are the field sizes. You'll need these to match the data types that are defined in your table. Now we get to our field delimiters. In a format file, delimiters are specified in double quotes. If a double quote is a part of a delimiter, then it needs to be escaped with a backslash:

• ",\"" (note the double double quotes) means, then, that there will be a comma followed by a double quote at the end of the first field. This is nice in that it does two things for us: first, it delimits the field, but it also will strip the first quote from the data as it's inserted.
• "\"," means that there will be a quote followed by a comma at the end of the second field. In this way, we've skipped the comma that is in the data, and we've stripped the second quote from the data.
• "\n" means that the last field will be delimited by an end-of-line character.

SQL_Latin1_General_Cp437_BIN has to do with the database collating setting, and many people simply leave that out of their format file without apparent ill effect. Now you can use a bulk insert statment like this:

bulk INSERT temptable FROM 'csvfilename' WITH (FIRSTROW = 2, FORMATFILE='formatfilename')

This will insert the CSV file into table TEMPTABLE, starting at the second row (we don't want to import the headers), using your new format file.