Structuring JSON data to process CSV

Question

I have a question regarding saving JSON data to CSV format. My JSON takes the following structure.

{
  "element_1": [
    {
      "field1": "Something",
      "field2": 123,
      "field3": "Something",
      "field4": "Something"
    },
  ],
  "element_2": [
    {
      "field1": "Something",
      "field2": 345,
      "field3": "Something"
    },
    {
      "field1": "Something",
      "field2": 365,
      "field3": "Something"
    },
    {
      "field1": "Something",
      "field2": 334,
      "field3": "Something",
      "field4": "Something",
      "field5": "Something"
    },
    ...
]

Now I am interested in saving the data from element_2 in CSV format. As such, I have the following

//convert JSON data to array
$array = json_decode($data, true);

fputcsv($handle, array_keys($array['element_2'][0]), ',');

foreach ($array['element_2'] as $key => $row) {
    fputcsv($handle, $row);
}

Now the problem with this approach is that it obtains the keys only from element 0, which means it misses the keys field4 and field5 in element 2.

The other problem is that it does not match the value to the key. So if element 1 has no field2, it will place the value of field3 in the column for field2.

How can I overcome these issues to make sure all keys are used, and the values are placed in the correct columns for their key?

Thanks

Nigel Ren · Accepted Answer · 2019-08-11 13:58:19Z

If you first went through all of the elements and built up a list of all of the fields, you can then make sure that each row has all of the possible elements.

Then it creates an empty template array with all the keys in (using array_fill_keys()) and for each row will populate the items present...

$array = json_decode($data, true);
// Scan all rows adding keys to list
$keys = [];
foreach ( $array['element_2'] as $row ) {
    $keys = array_merge($keys, array_keys($row));
}
// Reduce to unique keys
$keys = array_unique($keys);

$handle = fopen("a.csv", "w");
// Write out keys as header
fputcsv($handle, $keys, ',');
// Create empty array with the keys
$template = array_fill_keys($keys, null);

foreach ($array['element_2'] as $key => $row) {
    // Uses array_merge() to copy the values from the row to the template
    fputcsv($handle, array_merge($template, $row));
}
fclose($handle);

Collectives™ on Stack Overflow

Structuring JSON data to process CSV

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