How to use JavaScript to turn csv file with hierarchy relationships to an object?

Question

I have an csv file that contains an organization hierarchy data, one of the fields is the id of that employee's subordinates' ids.

The csv file is something looking like this:

id,name,title,children
n1,Lao Lao,general manager,"n2,n3,n9,n10"
n2,Bo Miao,department manager
n3,Su Miao,department manager,"n4,n5,n8"
n4,Tie Hua,senior engineer
n5,Hei Hei,senior engineer,"n6,n7"
n6,Dan Dan,engineer
n7,Xiang Xiang,engineer
n8,Pang Pang,senior engineer
n9,Hong Miao,department manager
n10,Chun Miao,department manager,"n11"
n11,Yue Yue,senior engineer

The end result I am looking for is something like this:

    {
    id: "n1",
    name: "Lao Lao",
    title: "general manager",
    children: [
      { id: "n2", name: "Bo Miao", title: "department manager" },
      {
        id: "n3",
        name: "Su Miao",
        title: "department manager",
        children: [
          { id: "n4", name: "Tie Hua", title: "senior engineer" },
          {
            id: "n5",
            name: "Hei Hei",
            title: "senior engineer",
            children: [
              { id: "n6", name: "Dan Dan", title: "engineer" },
              { id: "n7", name: "Xiang Xiang", title: "engineer" },
            ],
          },
          { id: "n8", name: "Pang Pang", title: "senior engineer" },
        ],
      },
      { id: "n9", name: "Hong Miao", title: "department manager" },
      {
        id: "n10",
        name: "Chun Miao",
        title: "department manager",
        children: [{ id: "n11", name: "Yue Yue", title: "senior engineer" }],
      },
    ],
  }

What i've tried so far: So far I've used csvtojson package to parse the csv file into an array, which gives me this:

[
  {
    id: 'n1',
    name: 'Lao Lao',
    title: 'general manager',
    children: 'n2,n3,n9,n10'
  },
  { id: 'n2', name: 'Bo Miao', title: 'department manager' },
  {
    id: 'n3',
    name: 'Su Miao',
    title: 'department manager',
    children: 'n4,n5,n8'
  },
  { id: 'n4', name: 'Tie Hua', title: 'senior engineer' },
  {
    id: 'n5',
    name: 'Hei Hei',
    title: 'senior engineer',
    children: 'n6,n7'
  },
  { id: 'n6', name: 'Dan Dan', title: 'engineer' },
  { id: 'n7', name: 'Xiang Xiang', title: 'engineer' },
  { id: 'n8', name: 'Pang Pang', title: 'senior engineer' },
  { id: 'n9', name: 'Hong Miao', title: 'department manager' },
  {
    id: 'n10',
    name: 'Chun Miao',
    title: 'department manager',
    children: 'n11'
  },
  { id: 'n11', name: 'Yue Yue', title: 'senior engineer' }
]

I think some recursion is required to transform this data, but I am not super good with that, any help is appreciated!

Edit: Also I am flexible on how the columns look in csv, if there is anyway to make this transformation easier by alter the columns that is cool as well.

Edit: I am playing with the idea of having a report to field in the csv, I think this'll make it much easier;

id,name,title,report_to
n1,Lao Lao,general manager
n2,Bo Miao,department manager,n1
n3,Su Miao,department manager,n1,
n4,Tie Hua,senior engineer,n3
n5,Hei Hei,senior engineer,n3,
n6,Dan Dan,engineer,n5
n7,Xiang Xiang,engineer,n5
n8,Pang Pang,senior engineer,n3
n9,Hong Miao,department manager,n1
n10,Chun Miao,department manager,n1
n11,Yue Yue,senior engineer,n10

Edit: after I change the csv columns I was able to come up with a solution. Gonna post it below in case other people are looking for this in the future.

const source = await CSVToJSON().fromFile("./example.csv");

const dataForOrgChart = source
  .map((employee) => {
    employee.children = source.filter(
      (child) => child.report_to === employee.id
    );
    return employee;
  })
  .map((employee) => {
    delete employee.report_to;
    return employee;
  })
  .filter(
    (employee) =>
      !source
        .map((employee) => employee.children.map((child) => child.id))
        .reduce((acc, cur) => [...acc, ...cur], [])
        .includes(employee.id)
  )[0];

console.log(JSON.stringify(dataForOrgChart));

I am not even 100% sure how i got here, its just tons of trials. It seems to be working but if someone notice any bugs please point out. Also thanks a lot for the different approaches. I am going to learn them all! I am a newbie on stackoverflow and this is a warm welcome!

Azad · Accepted Answer · 2020-07-23T09:05:57.647

I created the following code snippet with the help of this link, this solution may help you.

// csv data parsing function from 
// https://stackoverflow.com/questions/1293147/javascript-code-to-parse-csv-data

var parseCSV2 = function(s, sep) {
  // http://stackoverflow.com/questions/1155678/javascript-string-newline-character
  var universalNewline = /\r\n|\r|\n/g;
  var a = s.split(universalNewline);
  for (var i in a) {
    for (var f = a[i].split(sep = sep || ","), x = f.length - 1, tl; x >= 0; x--) {
      if (f[x].replace(/"\s+$/, '"').charAt(f[x].length - 1) == '"') {
        if ((tl = f[x].replace(/^\s+"/, '"')).length > 1 && tl.charAt(0) == '"') {
          f[x] = f[x].replace(/^\s*"|"\s*$/g, '').replace(/""/g, '"');
        } else if (x) {
          f.splice(x - 1, 2, [f[x - 1], f[x]].join(sep));
        } else f = f.shift().split(sep).concat(f);
      } else f[x].replace(/""/g, '"');
    }
    a[i] = f;
  }
  return a;
}

//the CSV data
var csvData = `id,name,title,children
n1,Lao Lao,general manager,"n2,n3,n9,n10"
n2,Bo Miao,department manager
n3,Su Miao,department manager,"n4,n5,n8"
n4,Tie Hua,senior engineer
n5,Hei Hei,senior engineer,"n6,n7"
n6,Dan Dan,engineer
n7,Xiang Xiang,engineer
n8,Pang Pang,senior engineer
n9,Hong Miao,department manager
n10,Chun Miao,department manager,"n11"
n11,Yue Yue,senior engineer`;

//add nested employeess
function addtoDepartment(employeesArray, employee) {

  if (!employee.addedToDepartement) {

    let departmentEmp = {
      id: employee.id,
      name: employee.name,
      title: employee.title
    };

    if (employee.childrenId) {
      employee.childrenId.split(",").forEach(id => {

        let emp = employeesArray.find(e => e.id == id);
        let dep = addtoDepartment(employeesArray, emp);

        if (dep) {
          if (!departmentEmp.children)
            departmentEmp.children = [];
          departmentEmp.children.push(dep)
        }
      })
    }

    employee.addedToDepartement = true;
    return departmentEmp;
  }
  return false;
}

let employeParsed = parseCSV2(csvData, ",");
let employees = [];

//add CSV emplyee data as employee object to an array
for (let i = 1; i < employeParsed.length; i++) {

  empData = employeParsed[i];

  employees.push({
    id: empData[0] || '',
    name: empData[1] || '',
    title: empData[2] || '',
    childrenId: empData[3],
    addedToDepartement: false
  });
}

//add employees to departments or under supervisor
var department = [];
for (let i = 0; i < employees.length; i++) {

  let depEmp = addtoDepartment(employees, employees[i]);
  if (depEmp)
    department.push(depEmp);
}

//FINAL output
console.log(department);

score 0 · Answer 2 · answered Jul 23 '20 at 07:58

If the ids are all different, you could convert the csv into a nice map: You could try doing:

let parsed = [
  {
    id: 'n1',
    name: 'Lao Lao',
    title: 'general manager',
    children: 'n2,n3,n9,n10'
  },
  { id: 'n2', name: 'Bo Miao', title: 'department manager' },
  {
    id: 'n3',
    name: 'Su Miao',
    title: 'department manager',
    children: 'n4,n5,n8'
  },
  { id: 'n4', name: 'Tie Hua', title: 'senior engineer' },
  {
    id: 'n5',
    name: 'Hei Hei',
    title: 'senior engineer',
    children: 'n6,n7'
  },
  { id: 'n6', name: 'Dan Dan', title: 'engineer' },
  { id: 'n7', name: 'Xiang Xiang', title: 'engineer' },
  { id: 'n8', name: 'Pang Pang', title: 'senior engineer' },
  { id: 'n9', name: 'Hong Miao', title: 'department manager' },
  {
    id: 'n10',
    name: 'Chun Miao',
    title: 'department manager',
    children: 'n11'
  },
  { id: 'n11', name: 'Yue Yue', title: 'senior engineer' }
];
let mapping = {};
parsed.forEach(v => mapping[v.id] = v); // Maps out employees

function organize(key) {
    if (!mapping[key]) return;
    if (typeof mapping[key].children === 'string') {
        let children = {};
        mapping[key].children.split(',').forEach(child => {
            child = mapping[child];
            children[child.id] = organize(child.id);
            delete mapping[child.id];
        });
        mapping[key].children = children;
    }
    return mapping[key];
}

Object.keys(mapping).forEach(organize);
console.log(mapping);

How to use JavaScript to turn csv file with hierarchy relationships to an object?

2 Answers2