Data not completely convert from JSON to CSV

Question

I have a set of JSON format as below. During converting from JSON to CSV, the function is worked but not all key is exist in the CSV file. How to allow all the key available in the CSV file?

Reference: How to convert JSON to CSV format and store in a variable

JSON

var data={
  "items": [
    {
      "bandwidth": "",
      "destination_ipv4": "",
      "destination_ipv6": ""
    },
    {
      "interface": "",
      "alarm": false,
      "service": [
        
      ]
    },
    {
      "unit": {
        "name": "operation",
        "shaping-rate": {
          "rate": "1G"
        }
      },
      "ipv6_address": ""
    },
    {
      "vrf": ""
    }
  ]
};

Current result

Expected result

JS

function download_file(data) {
    const items = data.items
    const replacer = (key, value) => value === null ? '' : value
    const header = Object.keys(items[0])
    let csv = items.map(row => header.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(','))
    csv.unshift(header.join(','))
    csv = csv.join('\r\n')

    var link = document.createElement("a");    
    link.id="lnkDwnldLnk";
    document.body.appendChild(link);
    blob = new Blob([csv], { type: 'text/csv' }); 
    var csvUrl = window.webkitURL.createObjectURL(blob);
    var filename = 'Result.csv';
    jQuery("#lnkDwnldLnk")
    .attr({
        'download': filename,
        'href': csvUrl
    });
    jQuery('#lnkDwnldLnk')[0].click();
    document.body.removeChild(link);
}

You only use the first element in the array `items[0]`. Instead you probably want to iterate over `items` and then use the keys from each object in the array. — phuzi, Jun 22 '23 at 10:44
Do you mean `Object.keys(items[0])` to be like this `Object.keys(items)` ? — mastersuse, Jun 22 '23 at 10:53
No, `items` doesn't have the keys you want. More like `items.flatMap(item => Object.keys(item))` - UNTESTED — phuzi, Jun 22 '23 at 10:56
Change ` Object.keys(items[0])` to `items.reduce( (a, v) => a.concat(Object.keys(v)), [])` — KooiInc, Jun 22 '23 at 11:16

score 2 · Answer 1 · edited Jun 22 '23 at 11:04

2

Instead of just using the first element in data.items you will need to get all keys from all elements in the array.

You can do this using Array.prototype.flatMap

const data = {
  "items": [{
      "bandwidth": "",
      "destination_ipv4": "",
      "destination_ipv6": ""
    },
    {
      "interface": "",
      "vlan": null,
      "service": ""
    },
    {
      "ipv4_address": "",
      "ipv6_address": ""
    },
    {
      "vrf": ""
    }
  ]
};

const items = data.items;
const header = items.flatMap(item => Object.keys(item));

console.log(header);

You should use this instead of const header = Object.keys(items[0]);

edited Jun 22 '23 at 11:04

mplungjan

169,008
28
173
236

answered Jun 22 '23 at 11:03

phuzi

12,078
3
26
50

hi thanks for answering, but @mplungjan solution's is works for me. your solution its help to appear the key but the value is not in a row, it's something like creating a new row in excel for every objects. – mastersuse Jun 22 '23 at 11:40
No worries, this is just the first step and specifically only tackles the problem in your code. It is a good answer to be fair – phuzi Jun 22 '23 at 12:00

score 2 · Accepted Answer · answered Jun 22 '23 at 11:05

2

Here is my version

var data = { "items": [{ "bandwidth": "A", "destination_ipv4": "B", "destination_ipv6": "C" }, { "interface": "D", "vlan": null, "service": "E" }, { "ipv4_address": "F", "ipv6_address": "G" }, { "vrf": "H" } ] };
const items = data.items;
let rows = [];
const header = items.map(item => {
    rows = rows.concat(Object.values(item));
    return Object.keys(item);
  })
  .flat()
  .join(",")
const csv = [header, rows.join(",")].join("\n");
console.log(csv)

answered Jun 22 '23 at 11:05

mplungjan

169,008
28
173
236

1

Thanks so much! this solution works for me.. – mastersuse Jun 22 '23 at 11:36
Hi, could you assist me. I received an update from the designer that the JSON response is gonna change as my updated JSON post above. It is possible to display in Excel such thing? I have tested your given code above but not support for my current case. thanks. – mastersuse Jun 26 '23 at 05:19

score 0 · Answer 3 · answered Jun 22 '23 at 10:54

In the provided JavaScript code, the header variable is used to store the keys of the first object in the items array. This means that only the keys of the first object will be used as the header in the CSV file. To include all the keys from all the objects in the CSV file, you can modify the code as follows:

function download_file(data) {
  const items = data.items;
  const replacer = (key, value) => value === null ? '' : value;
  
  // Get all unique keys from all objects in the items array
  const allKeys = items.reduce((keys, obj) => {
    Object.keys(obj).forEach(key => {
      if (!keys.includes(key)) keys.push(key);
    });
    return keys;
  }, []);
  
  const csv = items.map(row => allKeys.map(fieldName => JSON.stringify(row[fieldName], replacer)).join(','));
  
  // Add the header row with all keys
  csv.unshift(allKeys.join(','));
  
  const csvString = csv.join('\r\n');
  
  var link = document.createElement("a");    
  link.id="lnkDwnldLnk";
  document.body.appendChild(link);
  blob = new Blob([csvString], { type: 'text/csv' }); 
  var csvUrl = window.webkitURL.createObjectURL(blob);
  var filename = 'Result.csv';
  jQuery("#lnkDwnldLnk")
    .attr({
      'download': filename,
      'href': csvUrl
    });
  jQuery('#lnkDwnldLnk')[0].click();
  document.body.removeChild(link);
}

This modified code uses the reduce method to iterate over each object in the items array and collect all the unique keys. It then uses these keys to create the header row in the CSV file.

With this modification, all the keys from all the objects in the items array will be included in the CSV file.

hi thanks for answering, but @mplungjan solution's is works for me. your solution its help to appear the key but the value is not in a row, it's something like creating a new row in excel for every objects. — mastersuse, Jun 22 '23 at 11:40

Data not completely convert from JSON to CSV

3 Answers3