node.jscsvcsvtojson

Getting [sep=;] Extra stuff while converting a CSV file into JSON using Nodejs

I have a CSV file which contains 3 files as can be seen in the attached image:

CSV

I'm downloading it on runtime and trying to convert it into JSON format using NodeJS.

My expected result in JSON format is:

[
    [
        "PO_NO",
        "PO_LINE_ID",
        "MAT_ID",
    ],
    {
        "PO_NO": "2044344",
        "PO_LINE_ID": "1",
        "MAT_ID": "XZZ04152-100-994"
    },
    {
         "PO_NO": "2044344",
        "PO_LINE_ID": "1",
        "MAT_ID": "XZZ04152-100-992"
    }
]

But actually I'm getting this, due to which I can play with my JSON data:

[
  [
    "sep=;"
  ],
  {
    "sep=;": "PO_NO;PO_LINE_ID;MAT_ID"
  },
  {
    "sep=;": "2044344;1;XZZ04152-100-994"
  },
  {
    sep=;": "2044344;2;XZZ04152-100-992"
  }
]

How I can avoid it and bring it into proper JSON format?

Here is the code Nodejs/Javascript code I am using to convert csv into Json

const csvFilePath = 'cypress/logger/Dataset.csv'; // Path to the CSV file
const fs = require('fs');
const csv = require('csv-parser');


const results = [];


fs.createReadStream(csvFilePath)
    .pipe(csv())
    .on('headers', (headers) => {
        results.push(headers); // Add headers as the first element in the results array
    })
    .on('data', (data) => {
        results.push(data);
    })
    .on('end', () => {
        const jsonData = JSON.stringify(results);
        fs.writeFile('cypress/logger/Datasetfile1.json', jsonData, (err) => {
            if (err) {
                console.error('Error writing JSON file:', err);
            } else {
                console.log('CSV to JSON conversion completed.');
            }
        });
    });

Note: One Important thing which I want to highlight If I open CSV file in Visual Studio code, it has extra sep=; line Which is causing the issue.

sep=;
PO_NO;PO_LINE_ID;MAT_ID;
0002044344;1;XZZ04152-100-994;
0002044344;2;XZZ04152-100-992;
0002044344;3;XZZ04152-997-992;
Solution

  • If the headers are handled as 1 big header, then you need to define the separator

    .pipe(csv({ separator: ';' }))

    Source: https://www.npmjs.com/package/csv-parser#usage

    To skip also the first line, probably this works:

    .pipe(csv({ skipLines: 1, separator: ';' }))

    TL; DR;

    CSV is a very annoying standard, in that it does not enforce a specific separator (could be any of ,, ;, |, ...). Sometimes it uses quotes around the data or brackets. And it does not inform us about its file encoding (e.g. Latin-1 or UTF-8). Many things could go wrong. So, configuration is definitely needed.

    In this case, it looks like the application that generated this CSV file tried to help out by specifying the separator on the first line. sep=;. However, once again, this isn't a standard thing to do.