PHP: Output a complicated json to csv

Question

i am searching for an solution to render out a json string like this:

 {"result_count":5,"total_count":"1102","next_offset":5,"entry_list":[{"id":"00003010000000","module_name":"Contacts","name_value_list":{"id":{"name":"id","value":"00003010000000"},"name":{"name":"name","value":"Undefiniert"},"phone_work":{"name":"phone_work","value":""},"phone_mobile":{"name":"phone_mobile","value":"01"}}},{"id":"00003015000003","module_name":"Contacts","name_value_list":{"id":{"name":"id","value":"00003015000003"},"name":{"name":"name","value":"Christian X"},"phone_work":{"name":"phone_work","value":""},"phone_mobile":{"name":"phone_mobile","value":"0043 699 23231"}}},{"id":"00003015000005","module_name":"Contacts","name_value_list":{"id":{"name":"id","value":"00003015000005"},"name":{"name":"name","value":"Mario X"},"phone_work":{"name":"phone_work","value":"0043 1 232 151"},"phone_mobile":{"name":"phone_mobile","value":"0043 699 1112"}}},{"id":"00003015000006","module_name":"Contacts","name_value_list":{"id":{"name":"id","value":"00003015000006"},"name":{"name":"name","value":"Renate X"},"phone_work":{"name":"phone_work","value":"0043 1232 133"},"phone_mobile":{"name":"phone_mobile","value":""}}},{"id":"00003015000007","module_name":"Contacts","name_value_list":{"id":{"name":"id","value":"00003015000007"},"name":{"name":"name","value":"Harald KrX"},"phone_work":{"name":"phone_work","value":"0043 232 150"},"phone_mobile":{"name":"phone_mobile","value":"00423"}}}],"relationship_list":[{"link_list":[{"name":"accounts","records":[{"link_value":{"name":{"name":"name","value":"(X) X"}}}]}]},{"link_list":[{"name":"accounts","records":[{"link_value":{"name":{"name":"name","value":"(Y) Y"}}}]}]},{"link_list":[{"name":"accounts","records":[{"link_value":{"name":{"name":"name","value":"(C) C"}}}]}]},{"link_list":[{"name":"accounts","records":[{"link_value":{"name":{"name":"name","value":"(D) D"}}}]}]},{"link_list":[{"name":"accounts","records":[{"link_value":{"name":{"name":"name","value":"(E) E"}}}]}]}]}

into a CSV file where the resulting content is

"ID","NAME","PHONE_WORK","PHONE_MOBILE","COMPANY"

so in this sample case:

"00003015000003","Christian X","","0043 699 23231","X"<br/>
....<br/>
...

I guess this is quite complicated... but maybe someone can help me out here.

`I guess this is quite complicated` nothing is really complicated when you know what you're doing, but you need to show minimum effort and try yourself and then tell us what you did to answer your own question. We help debug, and not do all your work for you :-) see what and [How to Ask](https://stackoverflow.com/help/how-to-ask). Do some research, search for related topics on SO; if you get stuck, post a [Minimal, Complete, and Verifiable example](https://stackoverflow.com/help/mcve) of your attempt, noting input and expected output. — Alexandre Elshobokshy, Feb 05 '19 at 13:59
use json_decode($json, true) to transform you json to array and then iterate trough the array["entry_list"]; — Viktar Pryshchepa, Feb 05 '19 at 14:05
@IslamElshobokshy you are right. but i tried many hours (and i am nowhere near a real coder) so i failed hard - and i am so glad that someone posted a code sample which works, i could never ever write it myself. but i can use that sample now to get my stuff finished :) thank you all! — max_impact, Feb 06 '19 at 14:21
This is not a [mcve]. If it was, you would need to yatta-yatta the desired output. — mickmackusa, Sep 26 '22 at 12:10

score 0 · Answer 1 · answered Feb 05 '19 at 14:12

0

Not so difficult:

<?php

$json = '....';
$data = json_decode($json);

# print_r($data); #de-comment to debug

# print title
fputcsv(STDOUT,array("ID","NAME","PHONE_WORK","PHONE_MOBILE","COMPANY"));

foreach ( $data->entry_list as &$rec )
{
    $val = &$rec->name_value_list;
    fputcsv(STDOUT,array( $val->id->value,
                          $val->name->value,
                          $val->phone_work->value,
                          $val->phone_mobile->value ));
    # no data available for COMPANY!
}
?>

answered Feb 05 '19 at 14:12

Wiimm

2,971
1
15
25

1

Why `&$rec` and not simply `$rec`? – A.L Feb 05 '19 at 14:15
General decision to use a reference instead a copy if I don't change the data. In this case it is not needed, because objects are always referenced. So see it as hint "copy not needed here". – Wiimm Feb 05 '19 at 14:20
Years ago, a reference was clearly faster than a copy of array. A fast test of today can't find any time advantage of it. Seems, that copy-on-write is now better implemented. – Wiimm Feb 05 '19 at 14:43
thank you, this is not so complicated - true, because company is missing... the difficult part is getting the company into it (because its in the json!) ... glad a user above your post, posted a code which works including the company! :) thank you anyway for your time and code sample! – max_impact Feb 06 '19 at 14:20

davidloper · Accepted Answer · 2019-02-05T15:32:27.840

0

This is what you want, with company name:

$array = json_decode($JSON,true);

/*get companies name*/
$companies = array_map(function($arr){
  array_walk_recursive($arr,function($a,$key) use (&$return) { return $return[$key] = $a;});
  return preg_replace('/\(\w\) /','', $return['value']);
},$array['relationship_list']);

/*get employees details*/
$array = array_column($array['entry_list'],'name_value_list');

$headers = array_map('strtoupper',array_merge(array_keys($array[0]),['company']));

$out = fopen('file.csv','w');
fputcsv($out,$headers);

foreach($array as $key => $info){
  fputcsv($out, array_merge(array_column($info,'value'),[$companies[$key]]));
}
fclose($out);

edited Feb 05 '19 at 15:32

answered Feb 05 '19 at 14:44

davidloper

181
1
6

wow! this is exactly what i was searching for... and its as "complicated" as I thought with a array_map to work with... my coding skills are not good enough to code this myself I guess... so I thank you for your time to get this! – max_impact Feb 06 '19 at 14:19
one thing i recognized when I tried the code sample was, that sometimes its adding " around the field data, and sometimes not... is it maybe possible to always add the " " as surrounding character for the field data? – max_impact Feb 06 '19 at 14:24
@xanthos84 the code sample was missing the quote sometimes is because `fputcsv()` only enclose the field with quotes under certain conditions [read this](https://stackoverflow.com/a/2490512/10546440), and [this](https://stackoverflow.com/a/40994125/10546440) is what you are looking for. – davidloper Feb 06 '19 at 15:13

PHP: Output a complicated json to csv

2 Answers2