使用Excel转义CSV文件中的引号和定界符 [英] Escaping quotes and delimiters in CSV files with Excel

问题描述

我尝试使用;作为分隔符在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.

我将尝试举例说明:

假设我有这个字符串this is a;test:我在字符串周围使用双引号，以忽略定界符=>可以.

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

现在，如果此字符串包含定界符和双引号:我的把戏不再起作用.例如，如果我有字符串this; is" a;test:我在字符串周围添加的双引号会忽略第一部分的定界符(正确地忽略了部分this; is中的定界符，但是由于后面有双引号，因此Excel不会忽略a;test部分中的下一个定界符.

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.

推荐答案

当读取csv文件中带引号的字符串时，Excel将用单双引号()解释所有双双引号(").

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

因此"this; is"" a;test"将转换为一个包含this; is" a;test

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导出为CSV时将取消此过程.

Excel will reverse this process when exporting as CSV.

这是一些CSV

a,b,c,d,e
"""test1""",""",te"st2,"test,3",test"4,test5

这是导入Excel后的外观:

And this is how it looks after importing into Excel:

这篇关于使用Excel转义CSV文件中的引号和定界符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！