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

问题描述

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

不幸的是，数据内容可能很棘手。虽然逗号是分隔符，但行上的某些数据可能如下：

ID FN LN AGE COMMENT

39，我说嘿，我是5'10。

所以这是 comment column：

我说嘿，我是5'10 p>

没有开玩笑，我需要从excel或open office显示以上评论，从Java生成的CSV文件，当然不能乱（即常规双引号和元组中的常规逗号）。我知道正则表达式是强大的，但是我们如何能够用这种复杂的情况实现目标？

解决方案

以下是两个示例：

---

&＃x2750; Apache Commons Lang

Apache Commons Lang 包含用于转义或取消转义字符串（CSV，EcmaScript，HTML，Java，Json，XML）的特殊类： org.apache.commons.lang3.StringEscapeUtils 。

• 转到 CSV

    String escaped = StringEscapeUtils 
   .escapeCsv（I said \嘿，我是5'10\.\）我说嘿，我是5'10。 
   
   System.out.println（转义）; //I said嘿，我是5'10。
    



• p>
  从CSV中取消转义
  
  

    String unescaped = StringEscapeUtils 
   .unescapeCsv 我说\\嘿，我是5'10 \\.\\\）; //I said嘿，我是5'10。
   
   System.out.println（unescaped）; //我说嘿，我是5'10。
    

b $ b

_{* 您可以从 此处。}

---

&＃x2750; OpenCSV

如果使用 OpenCSV ，您不需要担心转义或取消转义，只能用于写入或读取内容。 / p>

• 撰写档案：

    FileOutputStream fos = new FileOutputStream（awesomefile.csv）; 
   OutputStreamWriter osw = new OutputStreamWriter（fos，UTF-8）; 
   CSVWriter writer = new CSVWriter（osw）; 
   ... 
   String [] row = {
  123，
  John，
  Smith，
  39，
  我说：嘿，我是5'10 \。\
  }; 
   writer.writeNext（row）; 
   ... 
   writer.close（）; 
   osw.close（）; 
   os.close（）; 
    




• 读取档案：

    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（）; 
    

_{从 此处下载。}

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"."

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

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

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

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

• Escape to 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""."""
  

• Unescape from 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"."
  

_{* You can download it from here.}

---

❐ OpenCSV

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();
  

_{* You can download it from here.}

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