Spreadsheet File Node - Escaping double quota

Questions
1

I noticed that Spreadsheet File node was not returing all records and it turned out one of the records has doube quotation and the node is not able to escape and parse the remaining records. here is a sample example with three records which the Spreadsheet File Node only returns 1 record. any solutions?

here is a sample


first,last,comment
1,2,3
3,4,"this is a
3,4,5
3

Hey @roozbehk,

I would have expected an error message looking at that as the file is not strictly valid, We would need to see if the package we use has an option to ignore errors but in this case to read the file you may need to manually do it with a code node.

2 Likes
4

i was not able to realibilty use the spreadsheet file node as it neither skips bad records nor provides an error on bad data. I used the function node with loading a custom node module papaparse to remove bad data and accomplish what I need. leaving the code here for others that might come across the issue I had

const fs = require('fs');
const Papa = require('papaparse');

const csvFilePath = items[0].json.srcPath
var finalResult=[];
const readCSV = async (filePath) => {
  const csvFile = fs.readFileSync(filePath)
  var csvData = csvFile.toString().replaceAll("\'",""); 
  return new Promise(resolve => {
    Papa.parse(csvData, {
      header: true,
      skipEmptyLines: true,
      step: function(result) {
        finalResult.push({json: result.data});
      },
      complete: function(results)  {
       // console.log('Complete', results.data.length, 'records.'); 
       // resolve(results.data);
      }
    });
  });
};

const getCSV = async () => {
  await readCSV(csvFilePath); 
}

getCSV()

return finalResult
2 Likes