Prevent opencsv from writing quotes to .csv file

Question

I'm populating a file from a resultSet like so :

      while(rs.next()){

          String[] entries = new String[3];
          entries[0] = rs.getString(1);
          entries[1] = ",";
          entries[2] = rs.getString(2);

          println("entries : "+entries);
          writer.writeNext(entries);

      }

When I open the excel file the values contain double quotes around them. So test1,test2,test3 when read from the database and written to a .csv file becomes "test1","test2","test3"

How can I write the text to file but not include the quotes ?

When I print the entries to the console the double quotes are not printed so I don't know where they are being added ?

score 26 · Answer 1 · answered Mar 23 '14 at 19:54

26

Just to extend on Matten's answer, use the built-in characters in the CSVWriter to specify the quotechar. So your writer would look like the following:

CSVWriter writer = new CSVWriter(new FileWriter(fileName), CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);

answered Mar 23 '14 at 19:54

obsessiveCookie

1,130
2
18
33

7

But if you use `NO_QUOTE_CHARACTER` does it still quote when needed? If I understand CSV correctly, the quotes are only needed when linebreaks, comma's etc are present in the string. However, OpenCSV always adds quotes, making it not compliant and unusable. Does this solve that? – Peterdk Apr 24 '14 at 17:49
This method should work if it is the end of the line: `public CSVWriter(Writer writer,char separator,char quotechar,String lineEnd)`. You can find more information (here)[http://opencsv.sourceforge.net/apidocs/au/com/bytecode/opencsv/CSVWriter.html#NO_QUOTE_CHARACTER/]. I was just highlighting that you can use the (constant field values)[http://opencsv.sourceforge.net/apidocs/constant-values.html#au.com.bytecode.opencsv.CSVWriter.NO_ESCAPE_CHARACTER] – obsessiveCookie Apr 24 '14 at 18:32
There is a newer solution without changing the quotechar. See my answer. – Sikor Oct 16 '19 at 11:05
How can I configure NO_QUOTE_CHARACTER for Writer or CSVMapper or CSVSchema? I am not using CSVWriter class – Hanh Nguyen Oct 29 '21 at 08:56

Sikor · Answer 2 · 2020-03-09T11:14:17.193

Since this question pops up at the first place in google results when someone looks for OpenCSV and quotes issue, I'm going to add the newer solution here.

I'm using version 4.6 and it is indeed possible to skip the quotes, while keeping quotes in entires that contain separator char.

Sample code:

List<SomeCsvEntryTO> csvEntries = new ArrayList<>();
csvEntries.add(new SomeCsvEntryTO("sdf1", "sdf2"));
csvEntries.add(new SomeCsvEntryTO("hgh1", "hgh2;hghgh2"));

Writer writer = new FileWriter(filePath);
StatefulBeanToCsv<SomeCsvEntryTO> csvWriter = new StatefulBeanToCsvBuilder<SomeCsvEntryTO>(writer)
        .withSeparator(';')
        .withApplyQuotesToAll(false)
        .build();
csvWriter.write(csvEntries);
writer.close();

SomeCsvEntryTO:

public class SomeCsvEntryTO{

   @CsvBindByName(column = "sample1colname")
   private String sample1;

   @CsvBindByName(column = "sample2colname")
   private String sample2;
}

As you can see above, I am using semicolon as a separator and I do not change the quotechar. Just change the withApplyQuotesToAll to false.

This produces the following result:

SAMPLE1COLNAME;SAMPLE2COLNAME
sdf1;sdf2
hgh1;"hgh2;hghgh2"

Creating a bean is overkill for what the question is asking. The problem at hand was solved in '14/'15 through the `applyQuotesToAll` parameter on `writeNext()` and `writeAll()`. Other answers predate this but an answer requiring version 4.6, can just use `false` for `applyQuotesToAll` which is a one line fix to the original question. — antak, Oct 27 '21 at 07:26
@antak Bean is being created in a sample code and sample code is just that, a sample. If you read the sentence before TO class and the result, it says "Just change withApplyQuotesToAll to false". — Sikor, Nov 03 '21 at 09:59

vegemite4me · Answer 3 · 2019-10-16T21:04:33.517

4

Update (16-Oct-2019): This is now possible with later versions of OpenCSV. Please see Sikor's answer.

TL;DR You cannot do it with OpenCSV v2.3. But you can with Commons CSV.

OpenCSV (v2.3) does not seem to provide a safe way of turning off quotes when they are not required. Quotes usually are not required for values that do no have the separator character or line end character in them.

Using NO_QUOTE_CHARACTER as some have suggested:

CSVWriter writer = new CSVWriter(new FileWriter("data.csv"), CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER);
String[] someData = new String[] {"Black and white", "Red, yellow and blue"};
writer.writeNext(someData);

produces the incorrect CSV representation as:

Black and white,Red, yellow and blue

A more valid representation of this data, which uses quotes only when required would be:

Black and white,"Red, yellow and blue"

I have not used Commons CSV, but it appears to provide this ability via QuoteMode.MINIMAL which does the following:

Quotes fields which contain special characters such as a delimiter, quote character or any of the characters in line separator.

edited Oct 16 '19 at 21:04

answered Feb 11 '15 at 10:20

vegemite4me

6,621
5
53
79

CommonsCSV has CSVPrinter, not WRiter. are you using a different library? – Stealth Rabbi Feb 10 '17 at 15:37
@StealthRabbi The code in my answer related to OpenCSV, to show how it does not provide the feature required. – vegemite4me Feb 13 '17 at 13:39
@vegemite4me This is now possible with OpenCSV. I don't know which version introduced it, but I am using version 4.6 and it works. See my answer. – Sikor Oct 16 '19 at 11:04
1

@Sikor Great to hear that. I have updated my answer with a link to your answer (and given it a +1). – vegemite4me Oct 16 '19 at 21:05

score 3 · Answer 4 · answered Jun 23 '16 at 21:22

Yes. There is a CSVWriter in the same package that follows the same semantics as the CSVReader. For example, to write a tab separated file:

CSVWriter writer = new CSVWriter(new FileWriter("yourfile.csv"), '\t');
// feed in your array (or convert your data to an array)
String[] entries = "first#second#third".split("#");
writer.writeNext(entries);
writer.close();

If you'd prefer to use your own quote characters, you may use the three arg version of the constructor, which takes a quote character (or feel free to pass in CSVWriter.NO_QUOTE_CHARACTER).

You can also customise the line terminators used in the generated file (which is handy when you're exporting from your Linux web application to Windows clients). There is a constructor argument for this purpose.

score 1 · Answer 5 · answered Mar 12 '13 at 16:39

The constructor code of the writer allows you to give an escape character (quotechar):

CSVWriter(Writer writer, char separator, char quotechar)

If this is the wrong one, there is another constructor with escape character :-)

CSVWriter(Writer writer, char separator, char quotechar, char escapechar)

score 1 · Answer 6 · edited May 23 '17 at 12:01

1

see this link on how to use NO_QUOTE_CHARACTER constructor and proper encoding when writing out to a file:

https://stackoverflow.com/a/29362439/1454926

edited May 23 '17 at 12:01

Community

1
1

answered Mar 31 '15 at 07:24

user1454926

181
8

score 0 · Answer 7 · answered Oct 06 '20 at 15:14

0

Here is what works for me since version 5.x

import static com.opencsv.ICSVParser.*;

...
new CSVWriter(writer, DEFAULT_SEPARATOR, NO_QUOTE_CHARACTER, DEFAULT_ESCAPE_CHARACTER, DEFAULT_LINE_END)

answered Oct 06 '20 at 15:14

夢のの夢

5,054
7
33
63

score 0 · Answer 8 · answered Dec 09 '20 at 20:17

Use this pattern to prevent from double quotes (checked on 5.3 version)

CSVWriter writer = new CSVWriter(new FileWriter(filename), CSVWriter.DEFAULT_SEPARATOR, CSVWriter.NO_QUOTE_CHARACTER, CSVWriter.NO_ESCAPE_CHARACTER, CSVWriter.DEFAULT_LINE_END);

score 0 · Answer 9 · answered Apr 07 '21 at 09:22

A long time and many working answers, but as of 2021 and v5 the cleanest solution in my eyes is using the builder with defined constants. This allows you more control on the CsvWriter than with a fixed constructor.

import static com.opencsv.ICSVWriter.*;

CSVWriterBuilder builder = new CSVWriterBuilder(new FileWriter("yourfile.csv"));
ICSVWriter csvWriter = builder
      .withQuoteChar(NO_QUOTE_CHARACTER)
      .build();
csvWriter.writeAll(rs, true);

score -1 · Accepted Answer · answered Mar 12 '13 at 16:44

You can use the Constructor as mentioned by @Matten and along with that, use the other writeAll() of CSVWriter.

OpenCSV also provides support to dump data from SQL table directly to CSV. For this we need ResultSet object. Following API can be used to write data to CSV from ResultSet.

java.sql.ResultSet myResultSet = getResultSetFromSomewhere();
writer.writeAll(myResultSet, includeHeaders);

The writeAll(ResultSet, boolean) method is utilized for this. The first argument is the ResultSet which you want to write to CSV file. And the second argument is boolean which represents whether you want to write header columns (table column names) to the file or not.

Prevent opencsv from writing quotes to .csv file

10 Answers10

Linked