Help with cleaning up misquoted values in a tab-delimited file
I'm hoping someone can help me out here because I've almost lost the will to live. I'm not sure if this is the right place but I figure that as you guys are dealing with data then you may well have come up against something similar.
I'm trying to prepare a large amount of products (about 5000) to insert into a shopping cart system. I have a text file with all the products in with the data delimited by tabs. Amendments need to be made to the data in Excel before its inserted using the carts admin system. This is all fine until I export the data from Excel.
Aside from Excel replacing random characters with other characters (which has taken me a stupid amount of time to sort out), it surrounds any text field that contains a comma with double quotes (eg "This is a product, description"). The problem is that it only does this to certain fields ie the ones with commas and there doesn't seem to any way to turn it off.
The system I'm inserting the data into has an option to strip off double quotes but because the data also contains inch marks and valid uses of double quotes the system chokes and can't distinguish between the valid uses and the ones I want it to strip.
Is there a way to get Excel to either put double quotes around all fields (I could then remove them with a search and replace in BBEdit) OR force it to not use double quotes at all?
I've looked in the help files and they're next to useless - All they say is "Excel places quotation marks around text with commas" as if its a handy feature.
If someone could help me out here I would really really really appreciate it.
Please help me before its too late.