Export JSON data to CSV file for Excel

Question

Given an array of data objects

const data = [{
    "id": "CT20",
    "type": "a11y-unknown",
    "urls": ["https://www.example.com/test/"]
 },
 {
    "id": "BC192",
    "type": "a11y-true",
    "urls": [
      "https://www.example.com/something/",
      "https://www.example.com/another-thing/"
    ]
 }
]

I'm trying to convert the objects to a CSV file that can be imported to Excel so that it shows as:

id     |     type    |    urls
CT20   | a11y-unknown| https://www.example.com/test/

I'm using the following to get the keys: const keys = Object.keys(data[0]);

then map over the data like so:

const commaSeparatedString = [keys.join(","),data.map(row => keys.map(key => row[key]).join(",")).join("\n")].join("\n");

However, this returns the following:

'id,type,urls\nCT20,a11y-unknown,https://www.example.com/test/\nBC192,a11y-true,https://www.example.com/something/,https://www.example.com/another-thing/'

When imported to Excel as a CSV file, and delimited with \, it appears like this:

How can I correctly map the objects so that they are delimited and line break after each set of urls?

const data = [{
    "id": "CT20",
    "type": "a11y-unknown",
    "urls": ["https://www.example.com/test/"]
 },
 {
    "id": "BC192",
    "type": "a11y-true",
    "urls": [
      "https://www.example.com/something/",
      "https://www.example.com/another-thing/"
    ]
 }
]

const keys = Object.keys(data[0]);
const commaSeparatedString = [keys.join(","),data.map(row => keys.map(key => row[key]).join(",")).join("\n")].join("\n");
console.log(commaSeparatedString)

IT goldman IT goldman · Accepted Answer · 2022-07-28T22:07:02

You need to have fixed number of columns. So either JSON.stringify the urls array, or designate columns such as url1, url2, url3...

EDIT: naturally if you don't escape commas by enclosing them in quotes, it will break the CSV. Genrally speaking you should use a libray for this such as papaparse

const data = [{
    "id": "CT20",
    "type": "a11y-unknown",
    "urls": ["https://www.example.com/test/"]
  },
  {
    "id": "BC192",
    "type": "a11y-true",
    "urls": [
      "https://www.example.com/something/",
      "https://www.example.com/another-thing/"
    ]
  }
]

const keys = Object.keys(data[0]);
const commaSeparatedString = [keys.join(","), data.map(row => keys.map(key => {
  return typeof row[key] === "string" ? row[key] : JSON.stringify(row[key])
}).join(",")).join("\n")].join("\n");
console.log(commaSeparatedString)

Export JSON data to CSV file for Excel

1 Answers