I am receiving a text file with comma separated data with quotation marks around certain fields that I need to bulk insert into SQL Server 2008 R2. An example of the data is:

"BUNGALOW 101,RIDGEWAY DR","POINT FORTIN",,,"TT","868-389-9769",,"ZPXB303191",1

As you can see there is a comma in the first field.

Using this Bulk Insert command causes that first field to be split into two columns. I know BOL says this is what is supposed to happen and to choose the separators carefully, but I have no control over the text file which is created by a 20 year old system which cannot be changed.

Any ideas how I can import this?

update

Would an alternative be to change the separator character (comma) to another character which I am sure won't be in the text, like a "|"?

I guess there must be some regex expression which will help me do this, can anyone suggest one?