如何同时为CSV文件转义逗号和双引号? [英] How to escape comma and double quote at same time for CSV file?

问题描述

我正在编写一个 Java 应用程序来将数据从 Oracle 导出到 csv 文件

I am writing a Java app to export data from Oracle to csv file

不幸的是，数据的内容可能相当棘手.逗号仍然是分隔符，但一行中的一些数据可能是这样的:

Unfortunately the content of data may quite tricky. Still comma is the deliminator, but some data on a row could be like this:

| ID    |   FN    |   LN   |  AGE   |  COMMENT                   |
|----------------------------------------------------------------|
| 123   |  John   |  Smith |   39   | I said "Hey, I am 5'10"."  |
|----------------------------------------------------------------|

所以这是 comment 列中的字符串之一:

so this is one of the string on the comment column:

我说嘿，我 5'10".

I said "Hey, I am 5'10"."

不开玩笑，我需要在 excel 或 open office 中从 Java 生成的 CSV 文件中不妥协地显示以上评论，当然不能弄乱其他常规转义情况(即常规双引号和元组中的常规逗号).我知道正则表达式很强大，但在如此复杂的情况下我们如何实现目标?

No kidding, I need to show above comment without compromise in excel or open office from a CSV file generated by Java, and of course cannot mess up other regular escaping situation(i.e. regular double quotes, and regular comma within a tuple). I know regular expression is powerful but how can we achieve the goal with such complicated situation?

推荐答案

有几个库.这里有两个例子:

There are several libraries. Here are two examples:

Apache Commons Lang 包括一个特殊的类来转义或取消转义字符串(CSV、EcmaScript、HTML、Java、Json、XML):org.apache.commons.lang3.StringEscapeUtils.

Apache Commons Lang includes a special class to escape or unescape strings (CSV, EcmaScript, HTML, Java, Json, XML): org.apache.commons.lang3.StringEscapeUtils.

• 转义到 CSV

String escaped = StringEscapeUtils
    .escapeCsv("I said "Hey, I am 5'10".""); // I said "Hey, I am 5'10"."

System.out.println(escaped); // "I said ""Hey, I am 5'10""."""

从 CSV 中取消转义

String unescaped = StringEscapeUtils
    .unescapeCsv(""I said ""Hey, I am 5'10"".""""); // "I said ""Hey, I am 5'10""."""

System.out.println(unescaped); // I said "Hey, I am 5'10"."

_{* 你可以从下载这里.}

如果您使用 OpenCSV，您将无需担心转义或unescape，仅用于写入或读取内容.

If you use OpenCSV, you will not need to worry about escape or unescape, only for write or read the content.

• 写入文件:

• Writing file:

FileOutputStream fos = new FileOutputStream("awesomefile.csv"); 
OutputStreamWriter osw = new OutputStreamWriter(fos, "UTF-8");
CSVWriter writer = new CSVWriter(osw);
...
String[] row = {
    "123", 
    "John", 
    "Smith", 
    "39", 
    "I said "Hey, I am 5'10".""
};
writer.writeNext(row);
...
writer.close();
osw.close();
os.close();

读取文件:

Reading file:

FileInputStream fis = new FileInputStream("awesomefile.csv"); 
InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
CSVReader reader = new CSVReader(isr);

for (String[] row; (row = reader.readNext()) != null;) {
    System.out.println(Arrays.toString(row));
}

reader.close();
isr.close();
fis.close();

_{* 你可以从下载它这里.}

这篇关于如何同时为CSV文件转义逗号和双引号?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！