CODEWITHSUNDEEP
javaexcelcsvapache-poi
VICKY-TSC
VICKY-TSC

Reputation: 415

POI Java to convert xlsx to CSV comma and double quotes

Trying to convert xlsx to CSV, where one column has multiple quotes and comma, when converting to CSV, it is separated to different cells instead of one cell. Below is scenario,

Actual Data

Converted

The Title is moving to multiple, attaching the code

if(cell.getStringCellValue().contains(",")){
              if (cell.getStringCellValue().contains("\"")){
                                String t= "\""+cell.getStringCellValue()+"\"";
                                data.append(t+",");
                                }
                                else{
                                data.append("\""+cell.getStringCellValue()+"\""+",");
                                }
                            }

Upvotes: 1

Views: 1526

Answers (1)

Krzysztof Cichocki
Krzysztof Cichocki

Reputation: 6414

You need to escape these special signs:

eg. if you want to have values one and two,three as two columns in a CSV file enclose the second column in quotes:

one,"two,three"

To escape double quotes " in the column value encolse it with double quotes and use "" to escape the quotes inside the value. eg if you have a field with abc"def it should be escaped as:

"abc""def"

So for your case it could be one liner instead of the whole if statement:

data.append("\"").append(cell.getStringCellValue().replaceAll("\"", "\"\"")).append("\",");

Upvotes: 1

Related Questions