How to convert csv to JSON when a row has two values for one column in node js

Question

I have a csv file in which a row sometimes has two values for a column. Currently I m trying to split using , but is not working as expected. Can someone throw light on how to achieve the expected output. I do not want to use any npm library.

//users.csv
name,age,hobbies
james,20,"reading,playing"
marie,18,singing
peter,19,watching movies

//index.js
const fs = require('fs');
const {promisify} = require("util");
const readFile = promisify(fs.readFile)
const readSheet = async () =>{
    const result = await readFile("./users.csv", "utf-8");
    const csv = result.split("\n").map(ele=>ele.split(","))
    let keys = csv.slice(0,1)[0];
    const values  = csv.slice(1)
    keys.map(keys=>keys.split(''))
    let usersArr = [];
    for(let i = 0;i<values.length;i++){
        let usersObj = {};
        keys.forEach((key,j)=>usersObj[key] = values[i][j])
        usersArr.push(usersObj)
    }
    return usersArr


}
//current output
[ { name: 'james', age: '20', hobbies: '"reading' },
  { name: 'marie', age: '18', hobbies: 'singing' },
  { name: 'peter', age: '19', hobbies: 'watching movies' } ]
  //expected output object
[
 {
   "name": "james",
   "age": 20,
   "hobbies": ["reading","playing"]
 },
 {
   "name": "marie",
   "age": 18,
   "hobbies": ["singing"]
 },
 {
   "name": "peter",
   "age": 19,
   "hobbies": ["watching movies"]
 }
]

score -1 · Answer 1 · answered Nov 18 '19 at 02:55

You could use the csv function described in this blog article: https://www.bennadel.com/blog/1504-ask-ben-parsing-csv-strings-with-javascript-exec-regular-expression-command.htm

Afterwards convert the arrays to objects:

let csv = ... // csv data
for(let i=1;i<csv.length;i++){
    let obj = {};
    for(let b in csv[i]){
        obj[csv[0][b]] = csv[i][b];
    }
    csv[i] = obj;
}
csv.shift(); // remove the first row (headers)

Afterwards just split the hobbies column in the array by comma:

for(let entry of csv){
    csv.hobbies = csv.hobbies.split(','); 
}

score -1 · Answer 2 · answered Nov 18 '19 at 03:06

Splitting each line by comma will not work since you have comma surrounding quotes. Your best option would be using a library from npm such as fast-csv. Since you don't want to use a library, you can use a regular expression to split the line as discussed in this question:

Here is a sample working code (be aware of the limitations though):

const fs = require('fs');
const {promisify} = require("util");
const readFile = promisify(fs.readFile)

const CSV_CONTENT = "name,age,hobbies\njames,20,\"reading,playing\"\nmarie,18,singing\npeter,19,watching movies\n";

const readSheet = async () => {
    //const lines = await readFile("./users.csv", "utf-8");

    const lines = CSV_CONTENT.split("\n")
                              .filter(l => !!l) // filter empty lines

    let keys = lines.slice(0,1)[0].split(',') // assuming there's no quotes in keys
    const valueLines  = lines.slice(1)

    let usersArr = [];
    for(let i=0; i<valueLines.length; i++) {
        let usersObj = {};
        let values = splitLine(valueLines[i]); // splitLine handles quotes
        keys.forEach((key,j) => usersObj[key] = trimQuotes(values[j]))
        usersArr.push(usersObj)
    }
    return usersArr

}

function splitLine(line) {
        var matches = line.match(/(\s*"[^"]+"\s*|\s*[^,]+|,)(?=,|$)/g);
        for (var n = 0; n < matches.length; ++n) {
            matches[n] = matches[n].trim();
            if (matches[n] == ',') matches[n] = '';
        }
        if (line[0] == ',') matches.unshift("");
        return matches;
}

function trimQuotes(input) {
  let expr = /^(")?(.*?)(")?$/g
  let groups = expr.exec(input)
  return groups[2]
}

(async () => {
  let result = await readSheet();
  console.log(result);
})();

How to convert csv to JSON when a row has two values for one column in node js

2 Answers2