Convert CSV to JSON including nested arrays of objects

Question

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"}
 ]
 }
]

• enhzflep: Is there a specific reason you've got multiple objects in a single row? Why not a single ID column and a single NAME column? That way, each object is on it's own line and the converter doesn't have to try to work out how you've stored your data.
• mellows: The example I've given is a much more simplified version of what I'm actually looking to parse. So the end data will have a row for each 'room', then within each room will be a load of data, including arrays of objects. It splits the initial rows into their own objects, but I am trying to figure out how to get nested object arrays...
• dataman: Using convertcsv.com, convert your desired JSON format to CSV, then reverse the process. ie. arr/id,arr/objects/0/id,arr/objects/0/name,arr/objects/1/id,arr/objects/1/name However - using code - the headers aren't important other than knowing what column your are looking at. Create your own names via a dictionary.

Answer 1

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);
 }

• mellows: Thank you for the answer. Would this work if there are nested object arrays in the csv file? Not sure how to format the Excel sheet for nested object arrays (sorry I should have been clearer in my question)
• user8849929: CSV is made to store data as a two dimensional array, columns, rows, that's all. Indeed I don't know how you would format your CSV to create nested arrays, as far as I know, CSV format is simply not suitable for that.