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?
updateWould 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?