I have a database exported in csv form. I need to paste this into excel so I can review the data. The problem is that some of the fields in the DB contain quotes and this causes problems... Example:
Open Excel and paste in the content of the csv file so it looks like:
"ID","FORMAT","NAME"
"001","T","onequote"twoquote"threequote"end"
"002","T","onequote"twoquote"end"
"003","T","onequote"end"
I then launch the text import wizard and select comma as the delimiter and hit finish. This splits the fields into cells and looks nice and tidy so is easy to review. However, if any of the fields in the “NAME” field contain a double quote, excel strips the first occurrence of the quote and it ends up at the end of the string, so it will end up looking like:
ID FORMAT STRING
1 T onequotetwoquote"threequote"end"
2 T onequotetwoquote"end"
3 T onequoteend"
Instead of:
ID FORMAT STRING
1 T onequote”twoquote"threequote"end
2 T onequote”twoquote"end
3 T onequote”end
Is there any way I can stop Excel doing this? It’s quite a big table so checking it manually is a bit of a pain. Unfortunately I can’t change the way the csv is provided. I could however edit the csv file once I have it if necessary.