CODEWITHSUNDEEP
phparrayscsv
katie hudson
katie hudson

Reputation: 2893

Structuring JSON data to process CSV

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

Upvotes: 0

Views: 20

Answers (1)

Nigel Ren
Nigel Ren

Reputation: 57131

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);

Upvotes: 1

Related Questions