Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to convert Json to CSV in php

Tags:

json

arrays

php

csv

Hi I am trying to convert a json file with differrent array structure than the normal one into csv file. I had been trying to find out a solution to convert it into a csv file but I cant find the solution.

  <?php 
    $jsondata = '[{
        "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"
      }]';

    $jsonDecoded = json_decode($jsondata);
    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){
            print_r('<pre>'); print_r($line); print_r('</pre>');
        print_r('<-------- data variable output-------->');
        if (is_array($line)||is_object($line)){
          foreach($line as $data){
            fputcsv($fp,$data);
            }
          }
        }
      }
    }

   fclose($fh);
   print_r('Converted Successfully');
?>

I tried looking into most of the similar questions in the stackoverflow but none has a array of my kind so they are not much useful to me.

If I use single foreach I am getting error Array to String Conversion failed and Array is being printed as value instead of actual data into the csv file.

If I use two foreach I am getting error fputcsv() expects parameter 2 to be array string given

var_dump or print_r result of decoded json is as follows

Array
(
[0] => stdClass Object
    (
        [accession_number_original] => 2012.11.45
        [author_birth_date] => Array
            (
                [0] => 1932
            )

        [author_date] => Array
            (
                [0] => 1932
            )

        [author_death_date] => Array
            (
                [0] => 
            )

        [author_description] => Array
            (
                [0] => American
            )

        [author_display] => Array
            (
                [0] => Day yon
            )

        [author_names_first] => Array
            (
                [0] => Day
            )

        [author_names_last] => Array
            (
                [0] => yon
            )

        [author_names_middle] => Array
            (
                [0] => 
            )

        [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] => Array
            (
                [0] => 12 1/2 x 18 1/4
            )

        [physical_description_material] => Array
            (
                [0] => Gelatin silver print
            )

        [physical_description_technique] => Array
            (
                [0] => Gelatin silver print
            )

        [pid] => bdr:123456
        [title] => As Demonstrators
    )
)
like image 568
miststudent2011 Avatar asked Nov 13 '17 10:11

miststudent2011


1 Answers

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
like image 170
moped Avatar answered Sep 19 '22 16:09

moped