Question 187020 in Mongodb Question posted in
The official documentation can be found here.

Mongodb – How I can remove extra commas from my CSV format file in Node.js?

I only want to remove the extra commas but don’t want to shift any data. Just remove the extra commas and left that cell to be empty.

a b c d e f
First row 1 0 , , ,
Second row 1 1 3 , ,
Third row 1 , 3 , ,

and I want =

a b c d e f
First row 1 0
Second row 1 1 3
Third row 1 3

I have written the following code, the extra commas get removed but the data get shifted to the previous column:

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

// Function to remove extra commas from a line
function removeExtraCommas(line) {
    let withinQuotes = false;
    let result = '';

    for (let i = 0; i < line.length; i++) {
        const char = line[i];

        if (char === '"') {
            withinQuotes = !withinQuotes;
            result += char;
        } else if (char === ',' && withinQuotes) {
            result += char;
        } else if (char === ',' && !withinQuotes) {
            const nextChar = line[i + 1];

            if (nextChar === ',') {
                result += '';
            } else {
                result += char;
            }
        } else {
            result += char;
        }
    }

    return result;
}

// Function to process the CSV file
function processCSVFile(filePath) {
    const outputFilePath = '../70_primary_codesData2CSV/70 Primary Codes Data/19120/19120(828+828_new)withoutExtraS.csv';
    const fileStream = fs.createReadStream(filePath);
    const rl = readline.createInterface({
        input: fileStream,
        crlfDelay: Infinity
    });

    const outputStream = fs.createWriteStream(outputFilePath);

    rl.on('line', (line) => {
        const processedLine = removeExtraCommas(line);
        outputStream.write(processedLine + 'n');
    });

    rl.on('close', () => {
        console.log('CSV file processed successfully.');
    });
}


// Example usage
const filePath = '../70_primary_codesData2CSV/70 Primary Codes Data/19120/19120(828+828_new).csv'; // Replace with your input file path
processCSVFile(filePath);
Tags:

2

Answers


  1. 0

    Try this:

    processedRow[key] = value ? value.replace(/,/g, ' ') : ' ';

    you’ll be replacing by a space.

    Login or Signup to reply.
  2. 0

    Issue with the removeExtraCommas function when it encounters an extra comma, is that it appends an empty string ” to the result, hence the data shift to the previous column.

    The requirement should be to append an empty value to the result to ensure columns remain aligned.

    The solution would be to introduce a column variable to keep a track of the current column value.

    See updated removeExtraCommas function below, this should resolve the data shift:

    // Function to remove extra commas from a line
function removeExtraCommas(line) {
  let withinQuotes = false;
  let result = '';

  // String variable to keep track of current column value as it iterates through the line
  let column = '';

  for (let i = 0; i < line.length; i++) {
    const char = line[i];

    if (char === '"') {
      withinQuotes = !withinQuotes;
      column += char; 
      // appends the character to the column string

    } else if (char === ',' && withinQuotes) {
      column += char; 
      // encountered an extra comma, maintain existing column

    } else if (char === ',' && !withinQuotes) {
      const nextChar = line[i + 1];

      if (nextChar === ',') {
        result += column + ',';
        column = '';
        // column appended to result with additional comma to maintain alignment, then column reset to empty string for next column.

      } else {
        column += char;
      }
    } else {
      column += char;
    }
  }

  result += column; // Append the last column to the result

  return result;
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search