Parse csv file with merged cells in javascript

Question

I'm working with csv files because we will have that as a source of truth (I know, not the best way but its what marketing familiar with and no time available to build a CMS)

Anyway, lets say I have an excel file with merged cells as bellow:

Plain csv file looks like:

Main ,MainDescription,Secondry,Code
First,First  Description,First Secondry 1,CODE 1
,,First Secondry 2,CODE 2
,,First Secondry 3,CODE 3
,,First Secondry 4,CODE 4
,,First Secondry 5,CODE 5
,,First Secondry 6,CODE 6
,,First Secondry 7,CODE 7
,,First Secondry 8,CODE 8
Second,Second Description,Second Secondry 1,CODE 9
,,Second Secondry 2,CODE 10
,,Second Secondry 3,CODE 11
,,Second Secondry 4,CODE 12
,,Second Secondry 5,CODE 13
,,Second Secondry 6,CODE 14
,,Second Secondry 7,CODE 15
,,Second Secondry 8,CODE 16

Is there any "smart" javascript csv parser I can use? I can write the logic myself, I just hope that there is something out there.

Ideally I would expect this to be parsed as bellow:

[
  {
    "Main": "First",
    "MainDescription": "First  Description",
    "Secondry": [
      "First Secondry 1",
      "First Secondry 2",
      "First Secondry 3",
      "..."
    ],
    "Code": ["Code 1", "...", "Code 8"]
  },
  {
    "Main": "Second",
    "MainDescription": "Second  Description",
    "Secondry": [
      "Second Secondry 1",
      "Second Secondry 2",
      "Second Secondry 3",
      "..."
    ],
    "Code": ["Code 9", "...", "Code 16"]
  }
]

darklightcode · Accepted Answer · 2021-10-21 10:45:43Z

I've put up a demo below on how you can achieve what you need, it's messy, but you need to find how many empty columns you have so you'll know on which key to group the values into an array:

let text = `Main ,MainDescription,Secondry,Code
First,First  Description,First Secondry 1,CODE 1
,,First Secondry 2,CODE 2
,,First Secondry 3,CODE 3
,,First Secondry 4,CODE 4
,,First Secondry 5,CODE 5
,,First Secondry 6,CODE 6
,,First Secondry 7,CODE 7
,,First Secondry 8,CODE 8
Second,Second Description,Second Secondry 1,CODE 9
,,Second Secondry 2,CODE 10
,,Second Secondry 3,CODE 11
,,Second Secondry 4,CODE 12
,,Second Secondry 5,CODE 13
,,Second Secondry 6,CODE 14
,,Second Secondry 7,CODE 15
,,Second Secondry 8,CODE 16
`

let csvToObject = (csvContents = '') => {

  let str = csvContents + '';
  return str.split(/\n/)
    .reduce((acc, itm, idx) => {

      let row = itm.split(/,/).map(i => i.trim());

      if (idx === 0) {
        acc['header'] = row;
        acc['list'] = [];
      } else {
        let h = acc['header']
        let findEmptyCols = 0;
        let rowLen = row.length;
        for (let i = 0; i < rowLen; i++) {
          if ((!row[i] || !row[i].length)) {
            findEmptyCols++
          } else {
            break;
          }
        }

        if (row.filter(i => i.trim().length).length) {

          if (!findEmptyCols) {

            acc.list.push(h.reduce((a, i, x) => {
              a[i] = row[x];
              return a;
            }, {}))

          } else {

            let last = acc.list[acc.list.length - 1];

            h.forEach((i, x) => {

              if (x >= findEmptyCols) {

                if (!Array.isArray(last[i])) {
                  last[i] = [last[i]];
                }
                last[i].push(row[x]);

              }
            })

            acc.list[acc.list.length - 1] = last;

          }

        }

      }
      return acc;

    }, {}).list;

}
console.log('csvToObject', csvToObject(text));

So there is no library and have to do from the scratch. Thanks.

Collectives™ on Stack Overflow

Parse csv file with merged cells in javascript

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related