How to parse a dirty CSV with Node.js?
<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>