Converting JSON to CSV Format Using PHP

Convert json to csv using php

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

How to convert Json to CSV in php

As I mentioned in the comment, first step would be to take care of array values, so each line needs to have values converted (it only counts with the format you provided, if there is an array with 2 values, only first will be passed to csv).

Your modified source code:

$jsonDecoded = json_decode($jsondata, true); // add true, will handle as associative array    
print_r('<pre>');
print_r($jsonDecoded);
print_r('</pre>');
$fh = fopen('fileout.csv', 'w');
if (is_array($jsonDecoded)) {
  print_r('<-------- line variable output-------->');   
  foreach ($jsonDecoded as $line) {
    // with this foreach, if value is array, replace it with first array value
    foreach ($line as $key => $value) {
        if (is_array($value)) {
            $line[$key] = $value[0];
        }
    }
    print_r('<pre>'); print_r($line); print_r('</pre>');
    // no need for foreach, as fputcsv expects array, which we already have
    if (is_array($line)) {
      fputcsv($fh,$line);
    }
  }
}
fclose($fh);
print_r('Converted Successfully');

Script output after execution:

[output of your print_r($jsonDecoded);]

<-------- line variable output-------->

Array
(
    [accession_number_original] => 2012.11.45
    [author_birth_date] => 1932
    [author_date] => 1932
    [author_death_date] => 
    [author_description] => American
    [author_display] => Day yon
    [author_names_first] => Day
    [author_names_last] => yon
    [author_names_middle] => 
    [image_height] => 12 1/2
    [image_width] => 18 1/4
    [jp2_image_url] => 
    [location_physical_location] => Art Gallery
    [location_shelf_locator] => Unknown
    [master_image_url] => 
    [note_provenance] => Gift of Gary Ginsberg and Susanna Aaron
    [object_date] => 1963/2010
    [object_depth] => 
    [object_height] => 
    [object_width] => 
    [origin_datecreated_end] => 1963
    [origin_datecreated_start] => 1963
    [physical_description_extent] => 12 1/2 x 18 1/4
    [physical_description_material] => Gelatin silver print
    [physical_description_technique] => Gelatin silver print
    [pid] => bdr:123456
    [title] => As Demonstrators
)

Converted Successfully

PHP Library to convert JSON to CSV?

i generally agree with the commenters, but if you're data is prepared this way, isn't this pseudo-code all you need?

$json_str = "{'aintlist':[4,3,2,1], 'astringlist':['str1','str2']}";

$json_obj = json_decode ($json_str);

$fp = fopen('file.csv', 'w');

foreach ($json_obj as $fields) {
    fputcsv($fp, $fields);
}

fclose($fp);

How to convert JSON data to CSV format on the fly with out using csv file

If you can already convert the json into csv, then just append the output strings together assigned to a string variable instead of writing it to a file. Or am I misunderstanding what you want?

Instead of:

fwrite($f, "$outputString\n");

you can put:

$csv .= $outputString;

And finish it with:

$array = explode("\n", $csv);

Convert JSON to CSV and save from browser to computer

I am not sure about the pdo part, but to write it to a file you would do something like this

$json = '{"data":[{"ID":1,"br":"1-2015","kupac":"ADAkolor","datum":"2015-05-19","rok":"2015-05-21","status":"placeno"},{"ID":2,"br":"2-2015","kupac":"Milenk","datum":"2015-05-27","rok":"2015-05-28","status":""}]}';

$out = fopen('file.csv', 'w');
foreach(json_decode($json, true)['data'] as $key => $value) {
  fputcsv($out, $value);
}
fclose($out);

Converting this json data into a csv file in PHP

  • First step is to get the maximum no. of clubs. This is important to maintain the order of the column headers.

Snippet:

<?php

function getMaxClubs($data){
    $max_clubs = 0;
    foreach($data as $value){
        $max_clubs = max($max_clubs, count($value['Clubs']));
    }
    return $max_clubs;
}
  • Second is to get the column headers. In here, we only proceed to the keys after the Clubs key once we have processed all the club keys which has maximum no. of clubs. We do this to maintain the order of the keys, a.k.a column headers.

Snippet:

<?php

function getHeadersRow($data, $max_clubs){
    $columns = [];

    foreach($data as $value){
        foreach($value as $k => $v){
            if($k === 'Classification'){
                $columns['SM'] = $columns['SB'] = $columns['S'] =  true;
            }elseif($k === 'Clubs'){
                if(count($v) !== $max_clubs) break;
                $max_clubs = -1; // indicating club keys are processed to avoid repetition
                $cnt = 0;
                foreach($v as $club_data){
                    $cnt++;
                    foreach($club_data as $club_key => $club_value){
                        $columns[ $club_key. $cnt] = true;
                    }
                }
            }else{
                $columns[ $k ] = true;
            }
        }
    }

    return array_keys($columns);
}
  • Now, we loop row by row. We append additional empty row entries for Clubs values if they are not having a count of $max_clubs.

Full Code:

<?php

$json = <<<EOD
{
    "Contestants": [
        {
            "Province": "Ontario",
            "Classification": "LSPI,,",
            "ClassificationDate": "2021",
            "RegistrationStatus": "Registered",
            "FirstName": "Kyle",
            "LastName": "Straunf",
            "Gender": "M",
            "AGE": null,
            "DOB": "02/08/2003",
            "Clubs": [
                {
                    "Clubname": "Penguins",
                    "Code": "MPNO",
                    "Clubid": "200"
                },
                {
                    "Clubname": "What Dolphins",
                    "Code": "AIZG",
                    "Clubid": "498"
                }
            ],
            "Email": null,
            "Language": "E",
            "ChallengeData": null
        },
        {
            "Province": "Alberta",
            "Classification": "LSPI,TEST2,TEST3",
            "ClassificationDate": "2021",
            "RegistrationStatus": "Registered",
            "FirstName": "Alexander",
            "LastName": "Kentwood",
            "Gender": "M",
            "AGE": null,
            "DOB": "08/16/2005",
            "Clubs": [
                {
                    "Clubname": "Elegant Dolphins",
                    "Code": "ZGIA",
                    "Clubid": "300"
                },
                {
                    "Clubname": "Weird Dolphins",
                    "Code": "ZGIA2",
                    "Clubid": "301"
                },
                {
                    "Clubname": "Favorite Dolphins",
                    "Code": "ZGIA3",
                    "Clubid": "302"
                }
            ],
            "Email": null,
            "Language": "E",
            "ChallengeData": null
        }  
    ]
    
}
EOD;

$data = json_decode($json, true);

$max_clubs = getMaxClubs($data['Contestants']);
$headers = getHeadersRow($data['Contestants'], $max_clubs);

$fp = fopen('test.csv', 'w+');

fputcsv($fp, $headers);
fputcsv($fp, array_fill(0, count($headers), ''));// empty next line for elegance

foreach($data['Contestants'] as $contestant_data){
    $row = [];

    foreach($contestant_data as $key => $value){
        if($key === 'Classification'){
            $row = array_merge($row, explode(",", $value));
        }elseif($key === 'Clubs'){
            foreach($value as $club){
                $row = array_merge($row, array_values($club));
            }
            $row = array_merge($row, array_fill(0, ($max_clubs - count($value)) * 3, ''));
        }else{
            $row[] = $value;
        }
    }

    fputcsv($fp, $row);
}

fclose($fp);

function getMaxClubs($data){
    $max_clubs = 0;
    foreach($data as $value){
        $max_clubs = max($max_clubs, count($value['Clubs']));
    }
    return $max_clubs;
}

function getHeadersRow($data, $max_clubs){
    $columns = [];

    foreach($data as $value){
        foreach($value as $k => $v){
            if($k === 'Classification'){
                $columns['SM'] = $columns['SB'] = $columns['S'] =  true;
            }elseif($k === 'Clubs'){
                if(count($v) !== $max_clubs) break;
                $max_clubs = -1; // indicating club keys are processed to avoid repetition
                $cnt = 0;
                foreach($v as $club_data){
                    $cnt++;
                    foreach($club_data as $club_key => $club_value){
                        $columns[ $club_key. $cnt] = true;
                    }
                }
            }else{
                $columns[ $k ] = true;
            }
        }
    }

    return array_keys($columns);
}

Converting Parse JSON output to CSV with large datasets

If you're able to run a script in the browser, check out the PapaParse JavaScript library -- it supports chunking and multi-threading for larger datasets and can convert JSON to CSV.

Specific config options that may be relevant:

  • worker
  • chunk
  • fastMode

Alternatively, there is a fork of PapaParse for Node.js, though without the worker and chunk options.

I have no affiliation with this library, but have used it successfully for CSV to JSON conversions on large datasets.


Related Topics

Creating a File Progress Bar in PHP

Disable Doctrine 2 Lazy Loading When Using Jms Serializer

Php: How to See "Invisible" Characters Like \N

Composer:How to Add a Dependency Without Network Connection

Phpexcel Ziparchive Not Found

Str_Replace() for Multiple Value Replacement

Phpstorm: How to Add Method Stubs from a Pecl Library That PHPstorm Doesn't Currently Support

Exploding by Array of Delimiters

PHP to Easyphp MySQL Server 1 Second Connection Delay

Save CSV Files into MySQL Database

Changes to Upload_Max_Filesize in Ubuntu PHP.Ini Will Not Take Effect

PHP MySQL - Insert into Without Using Column Names But with Autoincrement Field

Error with .Htaccess and Mod_Rewrite

When Do/Should I Use _Construct(), _Get(), _Set(), and _Call() in PHP

How Does PHP Max_Execution_Time Work

Is SQL Injection a Risk Today

Setting Up PHPmailer with Office365 Smtp

Custom Repository Class in Symfony2


Leave a reply



Submit