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?