Converting CSV to JSON where some JSON keys have multiple values

Question

I type data in CSV using excel. I want to convert the CSV into a JSON that looks like this:

[
  {
    "Sr": 1,
    "Name": ["Steven ", " Smith "],
    "Age": 5
  },
  {
    "Sr": 2,
    "Name": ["Mark ", " Wood "],
    "Age": 2
  }
]

None of the online converters convert the CSV in a way where the values of the key 'Name' are within square brackets and double quotes separated by a comma like in the example above.

I tried using online converters to the above JSON to CSV to see how I should have the values in the CSV.

It turned out like this:

Sr,Name,Age
1,"Steven , Smith ",5
2,"Mark , Wood ",2

Now converting that into JSON again gives a result like this:

[
    {
        "Sr": "1",
        "Name": "Steven , Smith ",
        "Age": "5"
    },
    {
        "Sr": "2",
        "Name": "Mark , Wood ",
        "Age": "2"
    },
    {
        "Sr": ""
    }
]

As you can see, it's not like the example at the top. That's how I need to data to be in the JSON. Any help would be appreciated. I simply want to type data in a CSV file and convert it into JSON whose data looks like the example at the top.

Thank you!

To your last result, what is stopping you to use a map and create an array for each "Name" key? — prabhatojha
– prabhatojha, Commented Aug 3, 2019 at 7:59

Oleksandr K · Accepted Answer · 2019-08-03 07:52:14Z

1

You should select a CSV separator another from comma (it is possible for LibreOffice and MS Office). So your's CSV would be like: Sr;Name;Age 1;"Steven, Smith";5 2;"Mark, Wood";2

And to parse CSV to json there are many ways. Look at a simple function like this

const parseCsv = async (url, delimiter) => {
  const file = await fetch(url);
  const text = await file.text();
  const [header, ...strings] = text.split("\n");
  const headers = header.replace(/\r$/, "").split(delimiter);
  const result = [];
  strings.reduce((jsonArray, stringLine) => {
    const line = {};
    stringLine
      .split(delimiter)
      .forEach(
        (cellData, idx) => (line[headers[idx]] = cellData.replace(/\r$/, ""))
      );
    jsonArray.push(line);
    return jsonArray;
  }, result);
  return result;
};

answered Aug 3, 2019 at 7:52

Oleksandr K

3574 silver badges9 bronze badges

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

Collectives™ on Stack Overflow

Converting CSV to JSON where some JSON keys have multiple values

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