Hi,

I am getting a text file from an old system that cannot be changed. It gives me data like this:

9,914376,"99863650891","LASERSHIP","LH12281461",,"DRP","POS",,"ZPX",,1,1.67,"P","APX",1,,"PET SUPPLIES",,,"P","B","ST",,15,"3597","43-2067",,"AMAZON FULFILLMENT SERVICES","172 TRADE STREET",,"LEXINGTON","KY","40511","US",,"RICHARD GONSALVES"
9,923519,"99888441052","DRJAYS.COM","9261293150429024370180",,"USP","POS",,"ZPX",,1,0.59,"P","APX",1,,"CLOTHES",,,"P","P","ST",,15,"3597","43-6548",,"DRJAYS.COM","17 S, MIDDLESEX DRIVE",,"MONROE TOWNSHIP","NJ","08831","US",,"MIGUEL BLACKFORD"

This is proving very difficult to get into SQL Server due to the quotation marks not being there if there is no value and there can be commas within an address (or any other quotation delimited column).

I am thinking that the best way to handle this is to pre-parse the text file and change the comma separator to a pipe character. Can anyone suggest a suitable solution to do this? I guess some kind of RegEx will be needed but I need a big jump start to get me going.