How to escape ,(comma) while writing data into a csv file

Question

I am fetching the data from the DB and writing the Result Set into the csv file using write all method of CSV Writer using ê delimiter.

The data in the some of the columns contains ,(comma) due to which the data is splitting into multiple cells.

could any one tell how to escape ,(comma) while writing data into a csv file?

Regards, Rajasekhar

You need to put the text that contains a comma inside quotes. OpenCSV will ignore commas in quoted elements. — LordWilmore, Jul 04 '16 at 09:49
So to be clear - you want opencsv to escape a character that is not the delimeter (since you are using ê as the separator)? If this is true then go to https://sourceforge.net/p/opencsv/feature-requests/ and create a feature request as openCSV only escapes out the separator, quote, and escape characters - since you changed the separator to something other than comma it will not escape the comma. I am not sure if we will be able to get it in the 3.9 release but we will try. — Scott Conway, Jul 05 '16 at 15:04
Thanks Scott Conway for the reply. is it possible to do it using any jar other than open csv. — Raj sekhar, Jul 15 '16 at 05:17
Not offhand. I have looked a little at the Apache Commons CSV but I cannot say for sure if they could or not either. — Scott Conway, Jul 16 '16 at 20:56
Sorry - ran out of editing time: Not offhand. I have looked a little at the Apache Commons CSV but I cannot say for sure if they could or not either. For what you are wanting to do try this: CSVWriter uses the write(String) method of the Writer object passed in to write the data to the file. Create your own Writer class that extends an existing writer class (say PrintWriter) that overrides the write method to call the string.replaceAll(",","\\,"); then call super.write(string). Then pass that Writer into the CSVWriter. — Scott Conway, Jul 16 '16 at 21:02

score 0 · Answer 1 · answered Oct 15 '20 at 20:08

In my case I was attempting insert using the PostgreSQL COPY command and ran into this issue. I had to parse every line into a new file. I used NodeJS to create a little utility for this and maybe it will help somebody searching the same question:

const fs = require('fs');
const path = require('path');
const readline = require('readline');

/**
 * @description formatLine should handle escaping commas w/in a representative cell.
 * @example 'row1, row2, "row3,with,embedded commas", row4
 * @param {string} str - Line to format and return. 
 * @returns {string} - 'row1, row2, "row3\,with\,embedded commas", row4
 */
const formatLine = (str) => {
    const regexp = /"(.*?)"/g;
    const array = [...str.matchAll(regexp)];
    // line growth is equal to the number of backslashes added.
    let additionsLength = 0;
    let formattedLine = '';
    if (array.length > 0) {
        array.forEach(item => {
            console.log('item: ', item);
            const index = item.index;
            const length = item[0].length;
            const pieces = item[0].split(/,/g);
            const insertMe = pieces.join('\\,');
            const beginning = (formattedLine) ? formattedLine.substring(0, index + additionsLength) : str.substring(0, index);
            const end = (formattedLine) ? formattedLine.substring(index + length + additionsLength) : str.substring(index + length);
            console.log(''.padEnd(50, '='));
            formattedLine = beginning + insertMe + end;
            additionsLength += pieces.length - 1;
        });
        return formattedLine;
    } else {
        return str;
    }
};

const writeStream = fs.createWriteStream(path.join('/postgres/', 'formatted.csv'));
const input = fs.createReadStream(path.join('/postgres/', 'input.csv'));
const rl = readline.createInterface({
    input,
    crlfDelay: Infinity
});
writeStream.on('open', () => {
    rl.on('line', (line) => {
        line = formatLine(line);
        writeStream.write(line + '\n');
    }).on('close', () => {
        writeStream.end();
    });
});

How to escape ,(comma) while writing data into a csv file

1 Answers1