All geek questions in one place

How to convert json to csv in php

Hi, I am trying to convert a json file with a different array structure than normal to a csv file. I tried to find a solution to convert it to a csv file, but I can not find a 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 to study most of these issues in stackoverflow, but none of them have an array that is not very useful to me.

If I use a single foreach, I get an Array to String Conversion failed error message and the Array is printed as a value instead of the actual data in the csv file.

If I use two foreach, I get an error fputcsv () expects parameter 2 to be set as an array string

The result of var_dump or print_r 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
    )
)
+4
3

, , ( , , 2 , csv).

:

$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 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
+1

PHPExcel, json excel-. 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');
0

: Drupal 8. , .

(   [0] = >       (           [webform_id] = > -           [sid] = > 1           [name] = > Question_1           [property] = > A1           [delta] = > 0           [] = > 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
    )

)

:

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

// CSV .  $ 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;

}

---

0

Source: https://habr.com/ru/post/1689187/

More articles:


All Articles