CODEWITHSUNDEEP
javaregexcsvseparator
John
John

Reputation: 195

java csv semi colon issue

I have to modify a program. This program creates csv file with columns in database like this : 

    csvBuilder.initCsvFile();
    csvBuilder.createRow(headers.toArray(new String[headers.size()]));
while (resultSet.next()) {
        String[] row = new String[resultSetColumnCount];
        int columnIndex = 1;
        while (columnIndex <= resultSetColumnCount) {
            Object object = resultSet.getObject(columnIndex);
            if (object == null) {
                row[columnIndex - 1] = "";
            } else {
                row[columnIndex - 1] = object.toString();
            }
            columnIndex++;
        }
        csvBuilder.createRow(row);
    }

initCsvFile method:

public void initCsvFile() {
    try {
        writer = new CSVWriter(new FileWriter(filePath), ';', CSVWriter.NO_QUOTE_CHARACTER);
    } catch (IOException e) {
        logger.error("CsvBuilder : error when creating {}", filePath, e);
        throw new CsvException("Error when creating the file : " + filePath, e);
    }
}

createRow method: 

public void createRow(String[] row) {
    writer.writeNext(row);
}

The problem is that, i have some data that includes the separator ';' like shown below,

id col1     col2   
1   US     United;States

Unfortunately, the csv file separates United and states in two . How can I check the content of resultSet and escape the separator without modify all program to get United;states in one row?

Thanks for your help

Upvotes: 0

Views: 2143

Answers (3)

Nebular Noise
Nebular Noise

Reputation: 510

I was just confronted to a similar problem. Apparently, a string is supposed to be escaped with double quotes, to prevent such issues. So I created a escapeCSVDatum() function:

private static String escapeCSVDatum(String s){
    return "\""+s.replace("\"", "\"\"").replace("\n", " ")+"\"";
}

So in your case, it would apply here

} else {
    row[columnIndex - 1] = escapeCSVDatum(object.toString());
}

However, as stated by Haroldo_OK, your use of CSVWriter.NO_QUOTE_CHARACTER makes it impossible to treat properly such cases. It is impossible to have a semicolon in a datum with this attribute.

As such, you might want to modify your code to replace semicolons with spaces, like so

} else {
    row[columnIndex - 1] = object.toString().replace(";"," ");
}

Finally, if you want to mess with people, replace your semicolons with greek question marks.

Upvotes: 1

Haroldo_OK
Haroldo_OK

Reputation: 7230

In those cases, you will need some quote character, otherwise there will be ambiguity; in your case, you're instructing it to not use quotes, and as a result, it has no way of treating those cases.

Try explicitly defining a quote character:

public void initCsvFile() {
    try {
        writer = new CSVWriter(new FileWriter(filePath), ';', '"');
    } catch (IOException e) {
        logger.error("CsvBuilder : error when creating {}", filePath, e);
        throw new CsvException("Error when creating the file : " + filePath, e);
    }
}

Keep in mind, that in this case any data that could cause ambiguity will be enclosed between quotes:

1;US;"United;States"

That's valid CSV, and most CSV parsers will automatically strip the quotes when reading.

Upvotes: 3

felix
felix

Reputation: 9285

you could replace the ";" before creating csv rows like this 

while (resultSet.next()) {
        String[] row = new String[resultSetColumnCount];
        int columnIndex = 1;
        while (columnIndex <= resultSetColumnCount) {
            Object object = resultSet.getObject(columnIndex);
            if (object == null) {
                row[columnIndex - 1] = "";
            } else {
                // replace ";" by ""
                row[columnIndex - 1] = object.toString().replace(";", "");
            }
            columnIndex++;
        }
        csvBuilder.createRow(row);
    }

Upvotes: 1

Related Questions