Convert json to csv using php

Question

Hello i have small problem with converting json to csv. Here is my code:

$jsonString = '{"cod":"200","calctime":0.3107,"cnt":15,"list":[{"id":2208791,"name":"Yafran","coord":{"lon":12.52859,"lat":32.06329},"main":{"temp":9.68,"temp_min":9.681,"temp_max":9.681,"pressure":961.02,"sea_level":1036.82,"grnd_level":961.02,"humidity":85},"dt":1485784982,"wind":{"speed":3.96,"deg":356.5},"rain":{"3h":0.255},"clouds":{"all":88},"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"}]}]}';

//Decode the JSON and convert it into an associative array.
$jsonDecoded = json_decode($jsonString, true);

//Give our CSV file a name.
$csvFileName = 'file.csv';

//Open file pointer.
$fp = fopen($csvFileName, 'w');

//Loop through the associative array.
foreach($jsonDecoded as $row){
    //Write the row to the CSV file.
    fputcsv($fp, $row);
}

//Finally, close the file pointer.
fclose($fp);

?>

I have tried with another json format like this [{"name":"Wayne","age":28},{"name":"John","age":21},{"name":"Sara","age":24}] and its working perfect. How to modify my code to save it correctly in csv format.

Pictures: Now it save it like this: I need to save it like this:

Can someone help me ?

Understand your data structure i.e. the $jsonDecoded array and then you will see what you are doing wrong — RiggsFolly
– RiggsFolly, Commented Mar 14, 2017 at 12:08

Sahil Gulati · Accepted Answer · 2017-03-14 16:03:23Z

1

Hope this will work..

<?php

$jsonString = '{"cod":"200","calctime":0.3107,"cnt":15,"list":[{"id":2208791,"name":"Yafran","coord":{"lon":12.52859,"lat":32.06329},"main":{"temp":9.68,"temp_min":9.681,"temp_max":9.681,"pressure":961.02,"sea_level":1036.82,"grnd_level":961.02,"humidity":85},"dt":1485784982,"wind":{"speed":3.96,"deg":356.5},"rain":{"3h":0.255},"clouds":{"all":88},"weather":[{"id":500,"main":"Rain","description":"light rain","icon":"10d"}]}]}';

$jsonDecoded = json_decode($jsonString, true);
$csvHeader=array();
$csvData=array();
jsontocsv($jsonDecoded);
print_r($csvHeader);
print_r($csvData);



$csvFileName = 'file.csv';
$fp = fopen($csvFileName, 'w');
fputcsv($fp, $csvHeader);
fputcsv($fp, $csvData);
fclose($fp);

function jsontocsv($data)
{
    global $csvData,$csvHeader;
    foreach($data as $key => $value)
    {
        if(!is_array($value))
        {
            $csvData[]=$value;
            $csvHeader[]=$key;
        }
        else 
        {
            jsontocsv($value);
        }
    }
}

Json 2

<?php

$jsonString =file_get_contents("http://samples.openweathermap.org/data/2.5/box/city?bbox=12,32,15,37,10&appid=b1b15e88fa797225412429c1c50c122a1");;
$jsonDecoded = json_decode($jsonString, true);
$csvHeader=array();
$csvData=array();
$csvFileName = 'file.csv';
$fp = fopen($csvFileName, 'w');
$counter=0;
foreach($jsonDecoded["list"] as $key => $value)
{
    jsontocsv($value);
    if($counter==0)
    {
        fputcsv($fp, $csvHeader);
        $counter++;
    }
    fputcsv($fp, $csvData);
    $csvData=array();
}
fclose($fp);

function jsontocsv($data)
{
    global $csvData,$csvHeader;
    foreach($data as $key => $value)
    {
        if(!is_array($value))
        {
            $csvData[]=$value;
            $csvHeader[]=$key;
        }
        else 
        {
            jsontocsv($value);
        }
    }
}

edited Mar 14, 2017 at 16:03

answered Mar 14, 2017 at 12:10

Sahil Gulati

15.2k4 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

RiggsFolly Over a year ago

Please dont post answer OFF-SITE. How are they going to be of any use to future visitors to SO

Ledeniqt Over a year ago

This is working perfectly Thanks!! But why is not working if we use json like this: samples.openweathermap.org/data/2.5/box/…

Sahil Gulati Over a year ago

@GeorgiBangeev I have given my answer based on that question but json which you are referring contains multiple rows and nested data which can't be done through this code. I am updating my post for that particular json.

Christian Felix · Accepted Answer · 2017-03-14 12:45:08Z

try this:

$rowNr = 0;
$prevKey = "";
$target = [];

$iterator = new RecursiveArrayIterator($jsonDecoded['list'][$rowNr]);
iterator_apply($iterator, 'traverseStructure', array($iterator,$prevKey,&$target));

function traverseStructure($iterator, $prevKey, &$target) {

while ( $iterator -> valid() ) {
    if ( $iterator -> hasChildren() ) {         
        $prevKey = $iterator->key();
        traverseStructure($iterator -> getChildren(), $prevKey, $target);            
    }
    else {                       
        if(isset($prevKey) && !is_int($prevKey)){               
            $row1 = $prevKey."/".$iterator->key();              
        }else{
            $row1 = $iterator->key();
        }           
        $row2 = $iterator->current();           
        $target[$row1] = $row2;         
    }
    $iterator -> next();        
   }
}

fputcsv($fp, array_keys($target));
fputcsv($fp, array_values($target));

Collectives™ on Stack Overflow

Convert json to csv using php

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related