csv文件 内容转义_使用Excel转义CSV文件中的引号和定界符

I try to import a CSV file in Excel, using ; as delimiters, but some columns contains

; and/or quotes.

My problem is : I can use double quotes to ignore the delimiters for a specific string, but if there is a double quote inside the string, it ignores delimiters until the first double quote, but not after.

I don't know if it's clear, it's not that easy to explain.

I will try to explain with a example :

Suppose I have this string this is a;test : I use double quotes around the string, to ignore the delimiter => It works.

Now if this string contains delimiters AND double quotes : my trick doesn't work anymore. For example if I have the string this; is" a;test : My added double quotes around the string ignore delimiters for the first part (the delimiter in the part this; is is correctly ignored, but since there is a double quote after, Excel doesn't ignore the next delimiter in the a;test part.

I tried my best to be as clear as possible, I hope you'll understand what is the problem.

解决方案

When reading in a quoted string in a csv file, Excel will interpret all pairs of double-quotes ("") with single double-quotes(").

so "this; is"" a;test" will be converted to one cell containing this; is" a;test

So replace all double-quotes in your strings with pairs of double quotes.

Excel will reverse this process when exporting as CSV.

Here is some CSV

a,b,c,d,e

"""test1""",""",te"st2,"test,3",test"4,test5

And this is how it looks after importing into Excel: