CODEWITHSUNDEEP
javascriptjsonexcelcsvdata-conversion
mellows
mellows

Reputation: 333

Convert CSV to JSON including nested arrays of objects

In Excel I have created a spreadsheet that I would like to convert into JSON for my VS Code project. I am currently just using an online CSV to JSON converter https://www.convertcsv.com/csv-to-json.htm, however my problem is that I can't seem to figure out a way to format it so that it uses arrays of objects. e.g.

"arr": [
    {"id":1, "name": "obj1"},
    {"id":2, "name": "obj2"},
]

If I format in Excel like this: enter image description here

The output looks like this:

[
 {
   "arr": {
      "id": [
         1,
         2
      ],
      "name": [
         "obj1",
         "obj2"
      ]
   }
}
]

Does anyone know how to format in Excel to get the desired array of objects? Otherwise can someone point me in the right direction for a script that will convert it correctly? Thanks!

EDIT To add to the above, I should have been clearer. I understand that the initial rows in an Excel spreadsheet will convert to objects when parsed as JSON but what I am trying to achieve is converting to JSON with nested arrays. So for example my desired output would be:

"arr":[
     {
       "id": 1, 
       "objects":[
          {"id": 1, "name": "obj1"}
          {"id": 2, "name": "obj2"}
       ]
     }
     {
       "id": 2, 
       "objects":[
          {"id": 1, "name": "obj1"}
       ]
     }
]

Upvotes: 0

Views: 4887

Answers (1)

user8849929
user8849929

Reputation:

Given the following CSV data for example:

  id,name
  1,foo
  2,bar

You can parse it as follow, where csv is the string containing CSV data:

  // split whole CSV into separated lines
  const lines = csv.split("\n");
  
  // split the first line to get properties count and names
  const keys = lines[0].split(",");
  
  const array = [];
  
  // iterate over the rest of lines, notice we start at 1
  for(let i = 1 ; i < lines.length; ++i) {
    
    // split line to get values
    const values = lines[i].split(",");
    
    // create new object
    const dict = {};
    
    // fill object with keys and values
    for(let k = 0; k < keys.length; ++k) {
      dict[keys[k]] = values[k];
    }
    
    // add object to array
    array.push(dict);
  }

Upvotes: 2

Related Questions