DEV Community

Mubeen Khan
Mubeen Khan

Posted on

Your API gave you JSON. Your spreadsheet wants CSV. Here is the conversion, done right.

#beginners #javascript #tutorial #webdev

Every developer has done this: an API hands you a neat JSON array, and someone asks for it in a spreadsheet. So you write a quick script, join some keys with commas, and send the file. Then the spreadsheet opens wrong. A company name with a comma in it splits into two columns. A quote inside a note breaks a whole row. A city like Austin, TX lands in two cells. The conversion looked trivial, and it was, until the data got real.

The reason is that CSV is not "values joined by commas". It is a format with rules, RFC 4180, and the rules exist exactly for the cases that break naive conversions. Any field that contains the delimiter, a double quote, or a line break must be wrapped in double quotes. Every literal double quote inside such a field must be doubled. That is it. That is the whole escaping rule, and it is the difference between a file that opens cleanly and a file that silently scrambles rows.

Here is the rule in JavaScript, for one record:

function csvEscape(field, delimiter = ",") {
  if (
    field.includes('"') ||
    field.includes(delimiter) ||
    field.includes("\n") ||
    field.includes("\r")
  ) {
    return '"' + field.replace(/"/g, '""') + '"';
  }
  return field;
}
Enter fullscreen mode Exit fullscreen mode

Two more details before the conversion is trustworthy. Nested objects need flattening: an API record like { "user": { "name": "Ana", "city": "Austin" } } has no natural single column, so flatten it with dot notation into user.name and user.city. That is how spreadsheet users actually want nested data. And arrays inside a record need a policy, not a guess: joining with "; " keeps the data in one cell without inventing columns.

Put together, a small converter looks like this:

function flatten(obj, prefix = "", out = {}) {
  for (const [key, value] of Object.entries(obj)) {
    const path = prefix ? `${prefix}.${key}` : key;
    if (value && typeof value === "object" && !Array.isArray(value)) {
      flatten(value, path, out);
    } else if (Array.isArray(value)) {
      out[path] = value.map((v) =>
        v && typeof v === "object" ? JSON.stringify(v) : String(v)
      ).join("; ");
    } else {
      out[path] = value ?? "";
    }
  }
  return out;
}

function jsonToCsv(records, delimiter = ",") {
  const rows = records.map((r) => flatten(r));
  const columns = [...new Set(rows.flatMap((r) => Object.keys(r)))];
  const lines = [columns.map((c) => csvEscape(c, delimiter)).join(delimiter)];
  for (const row of rows) {
    lines.push(
      columns.map((c) => csvEscape(row[c] ?? "", delimiter)).join(delimiter)
    );
  }
  return lines.join("\n");
}
Enter fullscreen mode Exit fullscreen mode

One worked example, so you can check your own converter against it. This input:

[{ "id": 1, "name": "Ana Torres", "note": "She said \"hi\"" }]
Enter fullscreen mode Exit fullscreen mode

must produce exactly this, header row included:

id,name,note
1,Ana Torres,"She said ""hi"""
Enter fullscreen mode Exit fullscreen mode

If your converter produces anything else, it will corrupt real data sooner or later.

Two Excel traps are worth knowing before you send the file. First, Excel on Windows historically opens CSV as Windows-1252 instead of UTF-8, which mangles accented characters. The fix is a UTF-8 byte-order mark at the start of the file: prepend "\uFEFF" to the download and Excel reads é and ñ correctly. Second, the delimiter: comma is standard, but European-locale exports use semicolons, and a file built for one locale opens wrong in the other. When in doubt, check what the receiving tool expects before you export, not after.

If you would rather not write this yourself, there is a free converter that does the whole thing in your browser, both directions: paste JSON and get a spreadsheet-ready CSV with flattened columns and RFC 4180 escaping, or paste CSV and get JSON back, with delimiter auto-detect and an Excel-safe download. It is the JSON to CSV converter on The Utility Hub, and nothing you paste ever leaves your machine, which matters when the API response contains real customer rows.

The habit to keep: never hand-roll CSV with string joins. Escape per RFC 4180, flatten nested objects with dot notation, and test with a comma, a quote, and a line break in the data before you trust the output. The edge cases are not edge cases. They are Tuesday.

Top comments (0)