How to escape comma and double quote at same time for CSV file?

Question

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?

I'm pretty sure Oracle (and the rest of the major RDBMSs) has an 'export' function that performs this function. Barring that, there's probably an open-source version that would interface with pretty much anything through JDBC, given how common this type of functionality is needed. — Clockwork-Muse, May 04 '12 at 15:40
Thanks X-Zero but here the job actually relies on the controller side. But still very helpful idea :) — Dreamer, May 04 '12 at 18:44

Paul Vargas · Accepted Answer · 2016-12-13T15:37:07.050

123

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

edited Dec 13 '16 at 15:37

answered May 04 '12 at 16:13

Paul Vargas

41,222
15
102
148

Thank you for the advice Paul. It is an good idea and quite flexible with rgx but can we achieve that without additional libraries? – Dreamer May 07 '12 at 13:44
And once again Paul it is a really supportive idea and useful piece information if in the next project, I can have to privilege to add more dependencies in Maven. :D – Dreamer May 07 '12 at 13:46
1

commons lang sucks because of this issue: https://issues.apache.org/jira/browse/LANG-857; It even sucks more because people patched the issue but there is no release date – Gelin Luo May 14 '13 at 03:42
1

**Fixed** since Apache Commons Lang 3.2 – Paul Vargas Jul 16 '14 at 16:51
StringEscapeUtils has no escapeCsv method – Elia Weiss Dec 09 '14 at 10:41
@user1406269 Actually, yes. **☺** Are you the *downvoter*? — Are you using the «**correct**» library? – Paul Vargas Jan 08 '15 at 04:23
Paul's link at the start of his answer to Apache Commons Lang leads directly to the page with the maven coordinates for the library and a download link. Its version 3.3.2 (or even later depending on when this comment is read). – cfeduke Jan 29 '15 at 17:01
2

This is the correct answer. Programmers should not re-invent the wheel (unless there's a really good reason). – James Watkins Feb 06 '15 at 15:48
@green How can you say that the entire commons lang sucks because a utility method throws an exception when you give it random binary data with invalid UTF-8 code points? I would argue that it's a feature, to protect against injection. – James Watkins Feb 17 '15 at 14:19
@JamesWatkins interesting argument, if this is a feature why do they bother fixing it in 3.2 and mark the type as "bug" ? BTW when I say commons sucks, I mean it sucks in this context, i.e. "escape comma and double quote". – Gelin Luo Feb 17 '15 at 22:03
Sorry to ask a question. But I don't see any escape of the comma inside the string. I am having issues where Open Office and Excel thinks it is a separator even though it is in quotes. – bytor99999 Jul 27 '16 at 16:01
@user1567291 Though not explicitly mentioned in the answer, it works for comma escapes also. – Mooncrater Jun 01 '21 at 08:08

Tony Ennis · Answer 2 · 2014-12-09T18:41:29.410

35

Excel has to be able to handle the exact same situation.

Put those things into Excel, save them as CSV, and examine the file with a text editor. Then you'll know the rules Excel is applying to these situations.

Make Java produce the same output.

The formats used by Excel are published, by the way...

****Edit 1:**** Here's what Excel does
****Edit 2:**** Note that php's fputcsv does the same exact thing as excel if you use " as the enclosure.

rdeslonde@mydomain.com
Richard
"This is what I think"

gets transformed into this:

Email,Fname,Quoted  
rdeslonde@mydomain.com,Richard,"""This is what I think"""

edited Dec 09 '14 at 18:41

answered May 04 '12 at 15:34

Tony Ennis

12,000
7
52
73

Tony that's a good idea. I should think that way then it should be an easy job. – Dreamer May 07 '12 at 13:42
3

How to escape `""""` ? – Martijn Courteaux Dec 11 '13 at 18:23
7

@MartijnCourteaux by doing this """"""""" This is what I think, suckas! """"""""" :) – SimonGates Jan 06 '14 at 13:36
5

Bad answer. Apache commons does everything without MS Excel. – Viacheslav Dobromyslov May 07 '14 at 23:42

Dreamer · Answer 3 · 2019-01-26T15:33:46.963

Thanks to both Tony and Paul for the quick feedback, its very helpful. I actually figure out a solution through POJO. Here it is:

if (cell_value.indexOf("\"") != -1 || cell_value.indexOf(",") != -1) {
    cell_value = cell_value.replaceAll("\"", "\"\"");
    row.append("\"");
    row.append(cell_value);
    row.append("\"");
} else {
    row.append(cell_value);
}

in short if there is special character like comma or double quote within the string in side the cell, then first escape the double quote("\"") by adding additional double quote (like "\"\""), then put the whole thing into a double quote (like "\""+theWholeThing+"\"" )

score 4 · Answer 4 · answered May 07 '12 at 13:47

4

You could also look at how Python writes Excel-compatible csv files.

I believe the default for Excel is to double-up for literal quote characters - that is, literal quotes " are written as "".

answered May 07 '12 at 13:47

Li-aung Yip

12,320
5
34
49

Thank you Li-aung. I wasn't get your post before post my own answer. Python does get more library methods for CVS producing. – Dreamer May 08 '12 at 18:29
Original question doesn't mention Python. – James Watkins Feb 06 '15 at 15:46
1

@JamesWatkins: OP doesn't mention Python - nonetheless, there is useful information in the Python documentation. – Li-aung Yip Feb 07 '15 at 20:16

score 4 · Answer 5 · edited Sep 20 '19 at 16:43

4

If you're using CSVWriter. Check that you don't have the option

.withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)

When I removed it the comma was showing as expected and not treating it as new column

edited Sep 20 '19 at 16:43

slfan

8,950
115
65
78

answered Sep 20 '19 at 16:21

silver_fox

1,057
1
5
4

CSVWriter csvWriter = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END, CSVWriter.NO_QUOTE_CHARACTER); if you create the csvwrite like this, the comma will not be processed properly. – Kurt Shaw Sep 26 '19 at 23:03
@silver_fox Thanks a lot. It saved my time – Sunil Dec 09 '19 at 16:27

score 2 · Answer 6 · answered Jan 06 '16 at 11:24

"cell one","cell "" two","cell "" ,three"

Save this to csv file and see the results, so double quote is used to escape itself

Important Note

"cell one","cell "" two", "cell "" ,three"

will give you a different result because there is a space after the comma, and that will be treated as "

score 0 · Answer 7 · edited Aug 03 '17 at 06:48

0

String stringWithQuates = "\""+ "your,comma,separated,string" + "\"";

this will retain the comma in CSV file

edited Aug 03 '17 at 06:48

user1211

1,507
1
18
27

answered Aug 03 '17 at 05:41

Ashiq M

19
1

2

Note that the question has already been answered, and the answer has been accepted. Your proposes solution does not escace commas. – EFrank Aug 03 '17 at 06:01

score 0 · Answer 8 · answered Nov 30 '22 at 11:04

In openCSV, use below method to create csvWriter obj,

CSVWriter csvWriter = new CSVWriter(writer, CSVWriter.DEFAULT_SEPARATOR, CSVWriter.DEFAULT_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END, CSVWriter.DEFAULT_QUOTE_CHARACTER);

In this, DEFAULT_QUOTE_CHARACTER is very important. It will work perfectly, If you want to insert any ',' or '"' in csv file.

How to escape comma and double quote at same time for CSV file?

8 Answers8

❐ Apache Commons Lang

❐ OpenCSV

Linked

Related