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 · Accepted Answer · 2022-07-28 22:20:42Z

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 library for parsing CSV 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/"
    ]
  }
]

var keys = Object.keys(data[0]);
var arr = [keys, ...data.map(row => keys.map(key => {
  return typeof row[key] === "string" ? row[key] : JSON.stringify(row[key])
}))];

// don't!
// .join(",")).join("\n")].join("\n");

// instead
var csv = Papa.unparse(arr);
console.log(csv)

<script src="https://cdnjs.cloudflare.com/ajax/libs/PapaParse/5.1.0/papaparse.min.js"></script>

n-- · Accepted Answer · 2022-07-28 22:33:43Z

As a general rule, the csv must be delimited with the same delimiter used when importing it, also for reliability all the fields should be included into quotes, so since in your case the delimiter is \, your attempt can be rewritten as below:

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]].flat().join()}"`).join("\\")).join("\n")].join("\n");
console.log(commaSeparatedString)

Collectives™ on Stack Overflow

Export JSON data to CSV file for Excel

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related