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 .
Advertisement
Answer
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