How to avoid backslash before comma in CSVFormat

Question

I am creating a CSV file using CSVFormat in java, the problem i am facing in both header and values is whenever the string is long and there is a comma the api is inserting a \ before the comma always. As a result the header is not forming correctly and the values in the csv file is taking next cell for the . I am posting the code what i have done

   try (CSVPrinter csvPrinter = new CSVPrinter(out,
            CSVFormat.DEFAULT.withHeader("\""+SampleEnum.MY_NAME.getHeader()+"\"", "\""+SampleEnum.MY_TITLE.getHeader()+"\"",
                    "\""+SampleEnum.MY_ID.getHeader()+"\"", "\""+SampleEnum.MY_NUMBER.getHeader()+"\"", "\""+SampleEnum.MY_EXTERNAL_KEY.getHeader()+"\"",
                    "\""+SampleEnum.DATE.getHeader()+"\"","\""+SampleEnum.MY_ACTION.getHeader()+"\"",
                    "\"\"\""+SampleEnum.MY__DEFI.getHeader()+"\"\"\"", SampleEnum.MY_ACTION.getHeader(),
                    SampleEnum.CCHK.getHeader(), SampleEnum.DISTANCE_FROM_LOCATION.getHeader(),
                    SampleEnum.TCOE.getHeader(), SampleEnum.HGTR.getHeader(),SampleEnum._BLANK.getHeader(),
                    SampleEnum.LOCATION_MAP.getHeader(), SampleEnum.SUBMISSION_ID.getHeader())                      
                    .withDelimiter(',').withEscape('\\').withQuote('"').withTrim().withQuoteMode(QuoteMode.MINIMAL)
    )) {
        sampleModel.forEach(sf -> {
            try {
                csvPrinter.printRecord(sf.getMyName(),
                        sf.getMyTitle(),
                        sf.getMyID(),
                        sf.getMyNo(),

So now the problem is i am getting output like this

"\"Name:\"","\"Title\"","\"ID #:\"","\"Store #:\"","\"Store #: External Key\"","\"Date:\"","\"\"\"It's performance  issue in detail to include dates,times, circumstances, etc.\"\"\""

I am getting \ before each commas , and when this will come in the value the next portion of the text will shift to the next cell .

my Required output is

"Name:","Title:","Employee ID #:","Store #:","Store #: CurrierKey","Date:","Stage of Disciplinary Action:","""Describe your view about the company, times, circumstances, etc.""",

I am trying https://commons.apache.org/proper/commons-csv/jacoco/org.apache.commons.csv/CSVFormat.java.html this link, but i am unable to understand the fix. Please help .

@talex i used it to avoid extra double quotes in my output with Quotemode NONE — Mandrek, Jul 05 '21 at 14:56
@Mandrek I suspect it is also responsible for escaping you want to avoid. — talex, Jul 05 '21 at 15:06

Matt · Accepted Answer · 2021-07-06T16:22:31.287

This happens because you are using QuoteMode.NONE which has the following Javadoc:

Never quotes fields. When the delimiter occurs in data, the printer prefixes it with the escape character. If the escape character is not set, format validation throws an exception.

You can use QuoteMode.MINIMAL to only quotes fields which contain special characters (e.g. the field delimiter, quote character or a character of the line separator string).

I suggest that you use CSVFormat.DEFAULT and then configure everything yourself if you cannot use one of the other formats. Check if the backslash (\) is really the right escape character for your use case. Normally it would be a double quote ("). Also, you probably want to remove all the double quotes from your header definition as they get added automatically (if necessary) based on your configuration.

StringBuilder out = new StringBuilder();
try (CSVPrinter csvPrinter = new CSVPrinter(out,
        CSVFormat.DEFAULT
                .withHeader("AAAA", "BB\"BB", "CC,CC", "DD'DD")
                .withDelimiter(',')
                .withEscape('\\') // <- maybe you want '"' instead
                .withQuote('"').withRecordSeparator('\n').withTrim()
                .withQuoteMode(QuoteMode.MINIMAL)
)) {
    csvPrinter.printRecord("WWWW", "XX\"XX", "YY,YY", "ZZ'ZZ");
}
System.out.println(out);

AAAA,"BB\"BB","CC,CC",DD'DD
WWWW,"XX\"XX","YY,YY",ZZ'ZZ

After your edit, it seems like you want all fields to be quoted with a double quote as escape character. Therefore, you can use QuoteMode.ALL and .withEscape('"') like this:

StringBuilder out = new StringBuilder();
try (CSVPrinter csvPrinter = new CSVPrinter(out,
        CSVFormat.DEFAULT
                .withHeader("AAAA", "BB\"BB", "CC,CC", "\"DD\"", "1")
                .withDelimiter(',')
                .withEscape('"')
                .withQuote('"').withRecordSeparator('\n').withTrim()
                .withQuoteMode(QuoteMode.ALL)
)) {
    csvPrinter.printRecord("WWWW", "XX\"XX", "YY,YY", "\"DD\"", "2");
}
System.out.println(out);

"AAAA","BB""BB","CC,CC","""DD""","1"
"WWWW","XX""XX","YY,YY","""DD""","2"

In your comment, you state that you only want double quotes when required and triple quotes for one field only. Then, you can use QuoteMode.MINIMAL and .withEscape('"') as suggested in the first example. The triple quotes get generated when you surround your input of that field with double quotes (once because there is a special character and the field needs to be quoted, the second one because you added your explicit " and the third one is there to escape your explicit quote).

StringBuilder out = new StringBuilder();
try (CSVPrinter csvPrinter = new CSVPrinter(out,
        CSVFormat.DEFAULT
                .withHeader("AAAA", "BB\"BB", "CC,CC", "\"DD\"", "1")
                .withDelimiter(',')
                .withEscape('"')
                .withQuote('"').withRecordSeparator('\n').withTrim()
                .withQuoteMode(QuoteMode.MINIMAL)
)) {
    csvPrinter.printRecord("WWWW", "XX\"XX", "YY,YY", "\"DD\"", "2");
}
System.out.println(out);

AAAA,"BB""BB","CC,CC","""DD""",1
WWWW,"XX""XX","YY,YY","""DD""",2

As per the chat you want total control when the header has quotes and when not. There is no combination of QuoteMode and escape character that will give the desired result. Consequently, I suggest that you manually construct the header:

StringBuilder out = new StringBuilder();
try (CSVPrinter csvPrinter = new CSVPrinter(out,
        CSVFormat.DEFAULT
                .withDelimiter(',').withEscape('"')
                .withQuote('"').withRecordSeparator('\n').withTrim()
                .withQuoteMode(QuoteMode.MINIMAL))
) {
    out.append(String.join(",", "\"AAAA\"", "\"BBBB\"", "\"CC,CC\"", "\"\"\"DD\"\"\"", "1"));
    out.append("\n");
    csvPrinter.printRecord("WWWW", "XX\"XX", "YY,YY", "\"DD\"", "2");
}
System.out.println(out);

"AAAA","BBBB","CC,CC","""DD""",1
WWWW,"XX""XX","YY,YY","""DD""",2

i have updated my post , applied your changes but , getting an output with backslash appended in each value — Mandrek, Jul 05 '21 at 15:20
@Mandrek I have added an example and extended the explanation. — Matt, Jul 05 '21 at 15:30
I'm not quite sure what you mean by *"is it possible to edit my post"*. My answer addresses your questions and my example can be used to apply the same principles in your own code. — Matt, Jul 05 '21 at 17:30
my output requires multiple combination of double and tripple quotes to be added in the output , for example i have updated my sample output , please check the updtaed post — Mandrek, Jul 05 '21 at 17:35
@Mandrek I extended my answer again to address your latest edit and how the triple quotes are generated. — Matt, Jul 05 '21 at 17:45
QuoteMode ALL will append the quote with all the headers and values , but that is not the requirement . I want tripple quote in 1 header and whenever i require i will have double quotes in my header and value.And if i don’t need double quotes i will not use — Mandrek, Jul 05 '21 at 17:51
@Mandrek I extended my answer again based on your comment. It seems after all that you want `QuoteMode.MINIMAL` and `.withEscape('"')`. For the field with triple quotes, you can surround the variable with quotes to have triple quotes in the output. — Matt, Jul 05 '21 at 18:09
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/234543/discussion-between-mandrek-and-matt). — Mandrek, Jul 06 '21 at 04:19

How to avoid backslash before comma in CSVFormat

1 Answers1