logo标签列表

如何在php中将Json转换为CSV

phparraysjsoncsv
6

你好，我正在尝试将一个具有不同于常规结构的json文件转换为csv文件。我一直在试图找到将其转换为csv文件的解决方案，但我找不到解决方案。

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

我尝试查看stackoverflow上大部分类似的问题，但没有一个与我的数组相同，所以它们对我没有太大用处。

如果我使用单个foreach，我会得到错误Array to String Conversion failed，并且Array被打印为值而不是实际数据写入csv文件中。

如果我使用两个foreach，我会得到错误fputcsv() expects parameter 2 to be array string given

解码json后var_dump或print_r的结果如下：

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
    )
)
首先要摆脱数组（将author_*转换为字符串），然后就不会抛出“数组转换为字符串”的错误了。 - moped
@moped 感谢您的评论，您能再详细解释一下吗？我是否需要手动转换每个没有键值的数组，还是有其他方法可以做到？请耐心等待，我刚开始学习 PHP。 - miststudent2011
检查我的答案，如果有什么不清楚的地方，请在评论中提问。 - moped
3个回答
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');

执行后的脚本输出如下：
[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
感谢您提供的解决方案，但是没有任何内容被写入CSV文件。我有写入该文件的权限，但只创建了一个空文件，并且print_r('<pre>'); print_r($line); print_r('</pre>');也没有提供任何输出。 - miststudent2011
在我的开发环境中运行良好，所以我认为问题出在你的源代码上。请复制/粘贴此部分内容到你的 JSON 字符串后面，然后再次检查文件。 - moped
我已经编辑了我的答案，以便包含您的所有源代码和所需的修复程序，请确保在文件顶部包含您的 $jsondata。 - moped
谢谢 @moped，我已经努力尝试了三天才转换成功，第二个foreach是问题所在，非常感谢，现在它可以正常工作了。也许是缓存问题，在清除浏览器缓存并重新启动服务器后，它就可以工作了。如何向文件添加标题？这样写是否正确 //在文件中创建标题。 $firstLineKeys = false; foreach ($jsonDecoded as $line) { if (empty($firstLineKeys)) { $firstLineKeys = array_keys($line); fputcsv($fh, $firstLineKeys); } } - miststudent2011
0

我使用了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');
谢谢你的回答，但根据自述文件，它无法读取JSON格式。我是不是漏掉了什么，还是你需要更具体的信息？ - miststudent2011
使用JSON解码读取您的JSON，并将其存储到一个数组中，其中一个数组中的键与另一个数组中的值具有相同的索引。$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; } - Akash Kalmegh
1
这是一种过度解决方案，你本可以编辑你的答案，在注释中放置源代码很难阅读 ;) - moped
顺便提一下，你是否意识到你的回答是关于XLS而不是CSV的？只是为了澄清，CSV并不总是用于像Excel这样的表格处理器中。 - moped
-1

这是我的数据：我正在使用Drupal 8。但是，它是一样的。

数组 ( [0] => 数组 ( [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
    )

)

我的函数文件：

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;

}

完成---

1
你应该添加一个关于你所提出的解决方案的描述。 - il_raffa
@Viktor，我认为即使第二个foreach之后还有数组元素，所以fputcsv也不会接受它们。检查[author_birth_date]，你就会明白我的意思了。 - miststudent2011
另外，这也可以运行： foreach ($jsonDecoded as $row) { fputcsv($fp, (array) $row); } - Viktor
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接