在将数据写入csv文件时如何转义(逗号)

Question

我正在从数据库中获取数据，并使用使用ê分隔符的csv Writer的write all方法将结果集写入csv文件。

某些列中的数据包含,(逗号)，因此数据被分成多个单元格。

有没有人能告诉如何在将数据写入csv文件时逃避,(逗号)?

问候,Rajasekhar

Answer 1

在我的情况下，我试图使用PostgreSQL COPY命令插入并遇到这个问题。我不得不把每一行都解析成一个新文件。我用NodeJS创建了一个小工具，也许它会帮助别人搜索同样的问题:

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();
    });
});