title: How to parse a dirty CSV with Node.js?
body: <p>I'm scratching my head on a CSV file I cannot parse correctly, due to many errors. I extracted a sample you can download here: <a href="http://www.filedropper.com/datastack" rel="noreferrer">Test CSV File</a></p>
  
  <p>Main errors (or what generated an error) are:</p>
  
  <ul>
  <li>Quotes &amp; commas (many errors when trying to parse the file with R)</li>
  <li>Empty rows</li>
  <li>Unexpected line break inside a field</li>
  </ul>
  
  <p>I first decided to use Regular Expression line by line to clean the data before loading them into R but couldn't solve the problem and it was two slow (200Mo file)</p>
  
  <p>So I decided to use a <a href="http://csv.adaltas.com/" rel="noreferrer">CSV parser</a> under Node.js with the following code:</p>
  
  <pre><code>'use strict';
  
  const Fs  = require('fs');
  const Csv = require('csv');
  
  let input       = 'data_stack.csv';
  let readStream  = Fs.createReadStream(input);
  let option      = {delimiter: ',', quote: '"', escape: '"', relax: true};
  
  let parser = Csv.parse(option).on('data', (data) =&gt; {
      console.log(data)
  });
  
  readStream.pipe(parser)
  </code></pre>
  
  <p>But:</p>
  
  <ul>
  <li>Some rows are parsed correctly (array of strings)</li>
  <li>Some are not parsed (all fields are one string)</li>
  <li>Some rows are still empty (can be solve by adding <code>skip_empty_lines: true</code> to the options)</li>
  <li>I don't know how to handle the unexpected line break.</li>
  </ul>
  
  <p>I don't know how to make this CSV clean, neither with R nor with Node.js.</p>
  
  <p>Any help?</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>Following @Danny_ds solution, I can parse it correctly. Now I cannot stringify it back correctly.</p>
  
  <p><strong>with <code>console.log();</code></strong> I get a proper object but when I'm trying to stringify it, I don't get a clean CSV (still have line break and empty rows).</p>
  
  <p>Here is the code I'm using:</p>
  
  <pre><code>'use strict';
  
  const Fs  = require('fs');
  const Csv = require('csv');
  
  
  let input  = 'data_stack.csv';
  let output = 'data_output.csv';
  
  let readStream  = Fs.createReadStream(input);
  let writeStream = Fs.createWriteStream(output);
  
  let opt  = {delimiter: ',', quote: '"', escape: '"', relax: true, skip_empty_lines: true};
  
  
  let transformer = Csv.transform(data =&gt; {
      let dirty = data.toString();
      let replace = dirty.replace(/\r\n"/g, '\r\n').replace(/"\r\n/g, '\r\n').replace(/""/g, '"');
  
      return replace;
  });
  
  let parser = Csv.parse(opt);
  let stringifier = Csv.stringify();
  
  readStream.pipe(transformer).pipe(parser).pipe(stringifier).pipe(writeStream);
  </code></pre>
  
  <p><strong>EDIT 2:</strong> </p>
  
  <p>Here is the final code that works:</p>
  
  <pre><code>'use strict';
  
  const Fs  = require('fs');
  const Csv = require('csv');
  
  
  let input  = 'data_stack.csv';
  let output = 'data_output.csv';
  
  let readStream  = Fs.createReadStream(input);
  let writeStream = Fs.createWriteStream(output);
  
  let opt  = {delimiter: ',', quote: '"', escape: '"', relax: true, skip_empty_lines: true};
  
  
  let transformer = Csv.transform(data =&gt; {
      let dirty = data.toString();
      let replace = dirty
          .replace(/\r\n"/g, '\r\n')
          .replace(/"\r\n/g, '\r\n')
          .replace(/""/g, '"');
  
      return replace;
  });
  
  let parser = Csv.parse(opt);
  
  let cleaner = Csv.transform(data =&gt; {
      let clean = data.map(l =&gt; {
          if (l.length &gt; 100 || l[0] === '+') {
              return l = "Encoding issue";
          }
          return l;
      });
      return clean;
  });
  
  let stringifier = Csv.stringify();
  
  readStream.pipe(transformer).pipe(parser).pipe(cleaner).pipe(stringifier).pipe(writeStream);
  </code></pre>
  
  <p>Thanks to everyone!</p>