Convert CSV to nested JSON objects with NodeJS

Question

I am trying to convert a csv text file into a JSON object with a specific structure. I have been using the csvtojson nodejs module and I have it really close, I just need some help with the fine tuning.

This is what my input file looks like:

PoHeader.hdrComment,PoHeader.poNumber,PoHeader.shipDate,PoHeader.rushOrder,PoLines.0.item,PoLines.0.itemDesc,PoLines.0.line,PoLines.0.poNumber,PoLines.0.qtyOrder
"FILL AND KILL","00100XXXXX","12/31/22","false","0000-SBAN","Henry s SFBAN Swamp Fox SFBAN",10,"00100XXXXX",1.000
"FILL AND KILL","00100XXXXX","12/31/22","false","0001-0099","Daiwa GC80 Goldcast Spincast R",20,"00100XXXXX",2.000
"FILL AND KILL","00100XXXXX","12/31/22","false","0001-1544","Daiwa MINISPIN Minispin Spinni",30,"00100XXXXX",3.000

I need the end result to follow this structure:

{
    "PoHeader": {
        "hdrComment": "My comment",
        "orderClass": "SO",
        "poNumber": "test02",
        "shipDate": "06/14/18",
        "rushOrder": false,
        "rushOption": 1,
        "adultSignature": true,
    },
    "PoLines": [
        {
            "item": "0001-3121",
            "itemDesc": "Some item",
            "linComment": "For John Doe",
            "line": 1,
            "poNumber": "test02",
            "qtyOrder": 1
        },
    ]
}

This is my code to get the JSON object:

csv()
    .fromFile(csvFilePath)
    .then((jsonObj)=>{
    //console.log(jsonObj);
    sendPO(jsonObj);
})

When I run my code, this is the jsonObj I get:

[
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0000-SBAN',
        itemDesc: 'Henry s SFBAN Swamp Fox SFBAN',
        line: '10',
        poNumber: '00100XXXXX',
        qtyOrder: '1.000'
      }
    ]
  },
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0001-0099',
        itemDesc: 'Daiwa GC80 Goldcast Spincast R',
        line: '20',
        poNumber: '00100XXXXX',
        qtyOrder: '2.000'
      }
    ]
  },
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0001-1544',
        itemDesc: 'Daiwa MINISPIN Minispin Spinni',
        line: '30',
        poNumber: '00100XXXXX',
        qtyOrder: '3.000'
      }
    ]
  }
]
[
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0000-SBAN',
        itemDesc: 'Henry s SFBAN Swamp Fox SFBAN',
        line: '10',
        poNumber: '00100XXXXX',
        qtyOrder: '1.000'
      }
    ]
  },
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0001-0099',
        itemDesc: 'Daiwa GC80 Goldcast Spincast R',
        line: '20',
        poNumber: '00100XXXXX',
        qtyOrder: '2.000'
      }
    ]
  },
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0001-1544',
        itemDesc: 'Daiwa MINISPIN Minispin Spinni',
        line: '30',
        poNumber: '00100XXXXX',
        qtyOrder: '3.000'
      }
    ]
  }
]
[
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0000-SBAN',
        itemDesc: 'Henry s SFBAN Swamp Fox SFBAN',
        line: '10',
        poNumber: '00100XXXXX',
        qtyOrder: '1.000'
      }
    ]
  },
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0001-0099',
        itemDesc: 'Daiwa GC80 Goldcast Spincast R',
        line: '20',
        poNumber: '00100XXXXX',
        qtyOrder: '2.000'
      }
    ]
  },
  {
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0001-1544',
        itemDesc: 'Daiwa MINISPIN Minispin Spinni',
        line: '30',
        poNumber: '00100XXXXX',
        qtyOrder: '3.000'
      }
    ]
  }
]

The most obvious problem is that it is returning 3 identical objects. But what I really need is one object with the same PoHeader object and a PoLines list of objects for each line item. My ideal output would be:

{
    PoHeader: {
      hdrComment: 'FILL AND KILL',
      poNumber: '00100XXXXX',
      shipDate: '12/31/22',
      rushOrder: 'false'
    },
    PoLines: [
      {
        item: '0000-SBAN',
        itemDesc: 'Henry s SFBAN Swamp Fox SFBAN',
        line: '10',
        poNumber: '00100XXXXX',
        qtyOrder: '1.000'
      },
      {
        item: '0001-0099',
        itemDesc: 'Daiwa GC80 Goldcast Spincast R',
        line: '20',
        poNumber: '00100XXXXX',
        qtyOrder: '2.000'
      },
      {
        item: '0001-1544',
        itemDesc: 'Daiwa MINISPIN Minispin Spinni',
        line: '30',
        poNumber: '00100XXXXX',
        qtyOrder: '3.000'
      }
    ]
  }

Any Help with this would be appreciated and I would be happy to share more code if needed.

traynor · Accepted Answer · 2022-10-18 15:04:37Z

1

It seems that you want to merge multiple rows, and as per docs, it works only per single line:

csvtojson is able to convert csv line to a nested JSON

So, it doesn't seem possible to nest multiple lines...

You might do it yourself after the parsing.

For example, group by PoHeader and PoLines. Here's a quick and dirty solution:

.then((jsonObj) => {

    const result = jsonObj.reduce((prev, curr) => {
        prev['PoHeader'] = curr['PoHeader'];
        if (prev['PoLines'] && prev['PoLines'].length > 0) {
            prev['PoLines'].push(curr.PoLines[0]);
        } else {
            prev['PoLines'] = curr.PoLines;
        }
        return prev;
    }, {});

    console.log(result);

})

answered Oct 18, 2022 at 15:04

traynor

10.4k3 gold badges15 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Convert CSV to nested JSON objects with NodeJS

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related