How to convert Json to CSV in php

Question

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

Answer 1

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

Answer 2

Used PHPExcel library that's help me to convert my json file into excel sheet. https://github.com/PHPOffice/PHPExcel

                $keyName=array();
                $i=0;
                $current_data=file_get_contents($dir.$excelFileLocation,true);
                $excelFile=fopen($dir."ExcelTojson.xls","w+");
                $array_data=json_decode($current_data,true);
                foreach($array_data as $jsonValue)
                {
                    foreach($jsonValue as $key => $value)
                    {
                        if($i==0)
                        {
                            $keyName[$i]=$key;
                            $i++;
                        }
                        else
                        {  
                            $check=0;
                            for($j=0;$j<count($keyName);$j++)
                            {
                                if($keyName[$j]==$key)
                                {   
                                    $check=1;
                                    break;
                                }
                            }
                            if($check==0)
                            {
                                $keyName[$i]=$key;
                                $i++;
                            }
                        }
                    }
                }
                $excelSheet = PHPExcel_IOFactory::load($dir."ExcelTojson.xls");
                $excelSheet->setActiveSheetIndex(0);
                $ascii=65;
                for($i=0;$i<count($keyName);$i++)
                {
                    $excelSheet->getActiveSheet()->setCellValue(chr($ascii)."1",stripslashes($keyName[$i]));
                    $ascii+=1;
                }
                $cellValue=2;
                foreach($array_data as $jsonValue)
                {   
                    $ascii=65;
                    foreach($jsonValue as $key => $value)
                    {
                        $excelSheet->getActiveSheet()->setCellValue(chr($ascii).$cellValue,stripslashes($value));
                        $ascii+=1;
                    }
                    $cellValue+=1;
                }

                $filename="ExcelTojson.xls";
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="'.$filename.'"');
                header('Cache-Control: max-age=0');
                $objWriter = PHPExcel_IOFactory::createWriter($excelSheet, 'Excel5');
                $objWriter->save('php://output');

Answer 3

This is my data: I am using Drupal 8. However, it is the same.

Array ( [0] => Array ( [webform_id] => webform [sid] => 1 [name] => Question_1 [property] => A1 [delta] => 0 [value] => 6 )

[1] => Array
    (
        [webform_id] => webform
        [sid] => 1
        [name] => Question_2
        [property] => A2
        [delta] => 0
        [value] => 4
    )

[2] => Array
    (
        [webform_id] => webform
        [sid] => 1
        [name] => Question_3
        [property] => A3
        [delta] => 0
        [value] => 2
    )

)

My function file:

public function DisplayData() {
// Fetch the data.

$connection = \Drupal::service('database');
$query = $connection->query("SELECT * FROM {webform}");
$result = $query->fetchAll();

$jsonDecoded = json_decode(json_encode($result), TRUE);

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

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

//Loop through the associative array.
foreach($jsonDecoded as $rows){
  //Write the row to the CSV file. fputcsv(file,fields,separator,enclosure)
  foreach ($rows as $key => $value){
    if (is_array($value)){
      $rows[$key] = $value[0];
    }

  }
  if (is_array($rows)) {
    fputcsv($fp, $rows);
    $roka = array_flip($rows);

  }

}
fputcsv($fp, array_merge($roka, $rows));
fclose($fp);

$build = [
  '#theme' => 'webpage',
];
return $build;

}

Done---