Friday, August 29, 2008

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:
IDNamePhone
100983"JonesFrank"555-1212
118928"SmithJoe"555-1313
115454"FranklinAlibaster"555-1414
When in reality, we want the data to look like this:
100983Jones, Frank555-1212
118928Smith, Joe555-1313
115454Franklin, Alibaster555-1414