The problem is that JSON is unstructured, while CSV is structured.
To clear this hurdle you must first of all gather all the JSON fields in all the structure, and since the header must be written first, you need to cycle through the JSON twice.
$columns = [ ];
// This could be a foreach
// foreach($data as $row) { $columns = array_merge($columns, array_keys($row)); }
array_map(function($row) use (&$columns) {
$columns = array_unique(array_merge($columns, array_keys($row)));
}, $data);
// Now columns contain all columns in all rows of the JSON.
$fp = fopen($cfilename, 'w');
fputcsv($fp, $columns);
// Set all empty fields to some default
$template = array_fill_keys($columns, '');
foreach ($data as $row) {
fputcsv($fp, array_values(array_merge($template, $row)));
}
fclose($fp);
The above will not function out of the box for complex data (if a column has sub-information, like in your example). There you need a more complex step:
foreach ($data as $row) {
$collapsed = array_map(function($value) {
if (is_array($value)) {
return implode(', ', $value);
}
return $value;
}, $row);
fputcsv($fp, array_merge($template, $collapsed));
}
Still more complex information in the JSON is a clear indication that you're doing this wrong. Your best bet is then to re-encode the complex value as JSON and store it as is in the CSV field (use json_encode() instead of implode, above).
The Great Column Name Massacre
For those cases where you need to throw bad money after worse, you can implement what I called the Great Column Name Massacre. In its easiest form, you code
{
"address": {
"street": "Piazza Vieusseux",
"number": 2,
"locality" : {
"type": "city",
"name": "Florence"
}
}
}
as
[
"address_street" => "Piazza Vieusseux",
"address_number" => 2,
"address_locality_type" => "city",
"address_locality_name" => "Florence"
]
I'm feeling of two minds about this. Please do not take this wrong, but I'm feeling sort of like you asked me why your Smith&Wesson battery-operated hair dryer is not working, even if you put all six batteries in the drum, pointed it to your head and pulled the trigger.
And I feel like I'm telling you "Oh, there's a safety switch on the side. You need to move it from SAFE to FIRE, or it will not work".
So bearing in mind that this looks like a very bad idea, the folding function I mentioned in the comments is this (you can adapt it to your needs, see later):
function fold($arry, $prefix = '') {
$retval = [ ];
foreach ($arry as $key => $value) {
$newkey = $prefix.$key;
if (is_array($value)) {
$folded = fold($value, $newkey . '_');
foreach ($folded as $subkey => $subval) {
$retval[$subkey] = $subval;
}
} else {
$retval[$newkey] = $value;
}
}
return $retval;
}
Once each element of the array has been folded, it can be analyzed to find out the column names (you can do this while folding) and then everything proceeds like above.
Testing
The folding function works properly with the provided JSON sample, and yields
Array
(
[BILLING_SOFTWARE_API_KEY] => ABCD1234
[DISTRIBUTOR_API_KEY] => 11380
[SALESMANS_0_sm_code] => 1
[SALESMANS_0_sm_name] => DEEPAK MEHTA 7044524144
[SALESMANS_1_sm_code] => 2
[SALESMANS_1_sm_name] => Juned Ansari
)
This of course immediately raises the first problem; "DISTRIBUTOR_API_KEY" is what we would expect from {"DISTRIBUTOR": {"API": {"KEY": 11380}}}. It works, but the decoding is ambiguous.
To overcome this limitation the quickest way is to change the delimiter from '_' to something else, or encode it differently in the keys.
Be warned: there will be no end of problems with this approach. If I had the time, I flatter myself fantasizing that I might end up with a post to rival the Famous Answer.
Having not the time, I hereby decline all responsibility for the frightful damage, loss of property, loss of productivity, loss of time, dereliction of duty, alienation of spouse and family ties and dire health consequences (included, but not limited to, a painful and prolonged death) that I believe are sure to follow.
