How to export a multi dimensional array to a specific .csv layout with fputcsv PHP

Question

I know the answer to this will be obvious but I have spent the last 3 days trying to figure it out. I am having trouble getting a Multi-Dimensional array to export into the correct layout in the exported .csv file.

I seem to able to either get all the data but not in the correct layout or I can get the correct layout but not all the data.

This is the array

array (size=106)
  0 => 
    array (size=6)
      0 => string 'Title' (length=5)
      1 => string 'image_url' (length=9)
      3 => string 'SKU CODE' (length=8)
      4 => string 'TITLE SIZE' (length=10)
      5 => string 'DESCRIPTION' (length=11)
      6 => string 'BASE SKU' (length=8)
  1 => 
    array (size=6)
      0 => string 'A witch and her cat live here' (length=29)
      1 => string 'https://beautifulhomegifts.com/a-witch-and-her-cat-live-here/' (length=61)
      3 => 
        array (size=4)
          0 => string 'BHG-MS-AWAHCLH030720' (length=20)
          1 => string 'BHG-MS-AWAHCLH030720-A5' (length=23)
          2 => string 'BHG-MS-AWAHCLH030720-A4' (length=23)
          3 => string 'BHG-MS-AWAHCLH030720-A3' (length=23)
      4 => 
        array (size=4)
          0 => string 'A witch and her cat live here' (length=29)
          1 => string 'A witch and her cat live here - 150mm x 200mm' (length=45)
          2 => string 'A witch and her cat live here - 201mm x 305mm' (length=45)
          3 => string 'A witch and her cat live here - 305mm x 400mm' (length=45)
      5 => 
        array (size=4)
          0 => string 'A witch and her cat live here' (length=29)
          1 => string 'A witch and her cat live here' (length=29)
          2 => string 'A witch and her cat live here' (length=29)
          3 => string 'A witch and her cat live here' (length=29)
      6 => 
        array (size=3)
          1 => string 'BHG-MS-AWAHCLH030720' (length=20)
          2 => string 'BHG-MS-AWAHCLH030720' (length=20)
          3 => string 'BHG-MS-AWAHCLH030720' (length=20)
  2 => 
    array (size=2)
      0 => string '' (length=0)
      1 => string '' (length=0)
  3 => 
    array (size=2)
      0 => string '' (length=0)
      1 => string '' (length=0)
  4 => 
    array (size=2)
      0 => string '' (length=0)
      1 => string '' (length=0)
  5 => 
    array (size=6)
      0 => string 'Autism House Rules' (length=18)
      1 => string 'https://beautifulhomegifts.com/autism-house-rules/' (length=50)
      3 => 
        array (size=4)
          0 => string 'BHG-MS-AHR030720' (length=16)
          1 => string 'BHG-MS-AHR030720-A5' (length=19)
          2 => string 'BHG-MS-AHR030720-A4' (length=19)
          3 => string 'BHG-MS-AHR030720-A3' (length=19)
      4 => 
        array (size=4)
          0 => string 'Autism House Rules' (length=18)
          1 => string 'Autism House Rules - 150mm x 200mm' (length=34)
          2 => string 'Autism House Rules - 201mm x 305mm' (length=34)
          3 => string 'Autism House Rules - 305mm x 400mm' (length=34)
      5 => 
        array (size=4)
          0 => string 'Autism House Rules' (length=18)
          1 => string 'Autism House Rules' (length=18)
          2 => string 'Autism House Rules' (length=18)
          3 => string 'Autism House Rules' (length=18)
      6 => 
        array (size=3)
          1 => string 'BHG-MS-AHR030720' (length=16)
          2 => string 'BHG-MS-AHR030720' (length=16)
          3 => string 'BHG-MS-AHR030720' (length=16)
  6 => 
    array (size=2)
      0 => string '' (length=0)
      1 => string '' (length=0)
  7 => 
    array (size=2)
      0 => string '' (length=0)
      1 => string '' (length=0)
  8 => 
    array (size=2)
      0 => string '' (length=0)
      1 => string '' (length=0)
  9 =>

I have tried multiple ways to get this to work and this is the closest I have got to it being correct

$f = fopen('new.csv', 'a'); // Configure fOpen to create, open and write only.

if ($f != false){

// Loop over the array and passing in the values only. 
foreach ($the_big_array as $row){
    
 
        fputcsv($f, $row);
    
}
}

fclose($f);

This gives me this layout but it just shows there is a child array and does not output the data of the child arrays.

[Image 1] Above is the output I am getting.

Below is the layout I want to achieve.

[Image 2]

I have also tried a foreach loop inside a foreach loop to get the data, when I do this I get all the data but not in the same layout. I have looked through all the posts on here and so many get close to what I want to achieve but none of them give the correct layout.

To summarise, I want to export $the_big_array to a .csv file that has the layout of the second image of a .csv in a spreadsheet. Thank you

array (
  0 => 
  array (
    0 => 'Title',
    1 => 'image_url',
    3 => 'SKU CODE',
    4 => 'TITLE SIZE',
    5 => 'DESCRIPTION',
    6 => 'BASE SKU',
  ),
  1 => 
  array (
    0 => 'A witch and her cat live here',
    1 => 'https://beautifulhomegifts.com/a-witch-and-her-cat-live-here/',
    3 => 
    array (
      0 => 'BHG-MS-AWAHCLH030720',
      1 => 'BHG-MS-AWAHCLH030720-A5',
      2 => 'BHG-MS-AWAHCLH030720-A4',
      3 => 'BHG-MS-AWAHCLH030720-A3',
    ),
    4 => 
    array (
      0 => 'A witch and her cat live here',
      1 => 'A witch and her cat live here - 150mm x 200mm',
      2 => 'A witch and her cat live here - 201mm x 305mm',
      3 => 'A witch and her cat live here - 305mm x 400mm',
    ),
    5 => 
    array (
      0 => 'A witch and her cat live here',
      1 => 'A witch and her cat live here',
      2 => 'A witch and her cat live here',
      3 => 'A witch and her cat live here',
    ),
    6 => 
    array (
      1 => 'BHG-MS-AWAHCLH030720',
      2 => 'BHG-MS-AWAHCLH030720',
      3 => 'BHG-MS-AWAHCLH030720',
    ),
  ),
  2 => 
  array (
    0 => '',
    1 => '',
  ),
  3 => 
  array (
    0 => '',
    1 => '',
  ),
  4 => 
  array (
    0 => '',
    1 => '',
  ),
  5 => 
  array (
    0 => 'Autism House Rules',
    1 => 'https://beautifulhomegifts.com/autism-house-rules/',
    3 => 
    array (
      0 => 'BHG-MS-AHR030720',
      1 => 'BHG-MS-AHR030720-A5',
      2 => 'BHG-MS-AHR030720-A4',
      3 => 'BHG-MS-AHR030720-A3',
    ),
    4 => 
    array (
      0 => 'Autism House Rules',
      1 => 'Autism House Rules - 150mm x 200mm',
      2 => 'Autism House Rules - 201mm x 305mm',
      3 => 'Autism House Rules - 305mm x 400mm',
    ),
    5 => 
    array (
      0 => 'Autism House Rules',
      1 => 'Autism House Rules',
      2 => 'Autism House Rules',
      3 => 'Autism House Rules',
    ),
    6 => 
    array (
      1 => 'BHG-MS-AHR030720',
      2 => 'BHG-MS-AHR030720',
      3 => 'BHG-MS-AHR030720',
    ),
  ),

Why don't you want to duplicate the Title field for every row? Your desired output is not sortable or filterable in any meaningful way. Also, what's up with the empty rows in the Array? Are they placeholders for CSV rows that should be inserted? Can you alter the array to filter them out? — Duroth, Jul 03 '20 at 12:26
The empty ones can be ignored as long as the layout is the same as the second image — Biwwabong, Jul 03 '20 at 12:28

score 1 · Answer 1 · answered Jul 03 '20 at 12:26

Your starting array is bad-formed, because it is not consistent in the dimensions of the child array and in the indexes. That's a valid solution, but it's very fragile because there are a lot of assumption about the array structure.

$f = fopen('new.csv', 'a');

// Write the header
fputcsv($f, array_values(array_shift($the_big_array)));

foreach($the_big_array as $baseRow) {
  if (empty($baseRow[0]) continue
  
  $subRowsCount = count($baseRow[3])

  if (
    count($baseRow[4]) !== $subRowsCount 
    || count($baseRow[5]) !== $subRowsCount 
    || count($baseRow[6]) !== $subRowsCount - 1)
  } {
    // Check that the sub-arrays dimensions are consistent or ignore the row
    continue;
  }

  for($i = 0; $i < $subRowsCount; $i++) {
    fputcsv($f, [
      $i === 0 ? $baseRow[0] : '', // Title
      $i === 0 ? $baseRow[1] : '', // image_url
      $baseRow[3][$i],  // SKU code
      $baseRow[4][$i],  // Title size
      $baseRow[5][$i],  // Description
      $i === 0 ? '' : $baseRow[6][$i-1] // Base sku
    ])
  }

}

score 1 · Accepted Answer · answered Jul 03 '20 at 14:07

Ok since the array is malformed and the code is a bit lengthy, I would like to say that we

First, print the headers by popping the first entry in the array.
Make each row have same number of entries by getting the max depth/ max count that a row entry could go with entry values.
Print each new row which is symmetrically arranged by using array_column(). You can print $final_row_data in the code to get a better view of how it is symmeterically arranged.

Snippet:

<?php

$the_big_array = array (
    0 =>
    array (
        0 => 'Title',
        1 => 'image_url',
        3 => 'SKU CODE',
        4 => 'TITLE SIZE',
        5 => 'DESCRIPTION',
        6 => 'BASE SKU',
    ),
    1 =>
    array (
        0 => 'A witch and her cat live here',
        1 => 'https://beautifulhomegifts.com/a-witch-and-her-cat-live-here/',
        3 =>
        array (
            0 => 'BHG-MS-AWAHCLH030720',
            1 => 'BHG-MS-AWAHCLH030720-A5',
            2 => 'BHG-MS-AWAHCLH030720-A4',
            3 => 'BHG-MS-AWAHCLH030720-A3',
        ),
        4 =>
        array (
            0 => 'A witch and her cat live here',
            1 => 'A witch and her cat live here - 150mm x 200mm',
            2 => 'A witch and her cat live here - 201mm x 305mm',
            3 => 'A witch and her cat live here - 305mm x 400mm',
        ),
        5 =>
        array (
            0 => 'A witch and her cat live here',
            1 => 'A witch and her cat live here',
            2 => 'A witch and her cat live here',
            3 => 'A witch and her cat live here',
        ),
        6 =>
        array (
            1 => 'BHG-MS-AWAHCLH030720',
            2 => 'BHG-MS-AWAHCLH030720',
            3 => 'BHG-MS-AWAHCLH030720',
        ),
    ),
    2 =>
    array (
        0 => '',
        1 => '',
    ),
    3 =>
    array (
        0 => '',
        1 => '',
    ),
    4 =>
    array (
        0 => '',
        1 => '',
    ),
    5 =>
    array (
        0 => 'Autism House Rules',
        1 => 'https://beautifulhomegifts.com/autism-house-rules/',
        3 =>
        array (
            0 => 'BHG-MS-AHR030720',
            1 => 'BHG-MS-AHR030720-A5',
            2 => 'BHG-MS-AHR030720-A4',
            3 => 'BHG-MS-AHR030720-A3',
        ),
        4 =>
        array (
            0 => 'Autism House Rules',
            1 => 'Autism House Rules - 150mm x 200mm',
            2 => 'Autism House Rules - 201mm x 305mm',
            3 => 'Autism House Rules - 305mm x 400mm',
        ),
        5 =>
        array (
            0 => 'Autism House Rules',
            1 => 'Autism House Rules',
            2 => 'Autism House Rules',
            3 => 'Autism House Rules',
        ),
        6 =>
        array (
            1 => 'BHG-MS-AHR030720',
            2 => 'BHG-MS-AHR030720',
            3 => 'BHG-MS-AHR030720',
        ),
    )
);

$headers = array_shift($the_big_array);
$header_keys = array_keys($headers);

$fhandle = fopen("sample.csv","a+");// have w+ if you want to override each time.

fputcsv($fhandle,$headers);// add headers first

foreach($the_big_array as $row_data){
   $insert_row = [];
   
   // making consistent with all header keys
   foreach($header_keys as $key){
       if(isset($row_data[$key])){
           $insert_row[$key] = $row_data[$key];
       }else{
           $insert_row[$key] = '';
       }       
   }   
   
   if(count(array_filter($insert_row)) == 0) continue;
   
   $final_row_data = [];
   $max_depth_size = 0;
   foreach($insert_row as $value){
       if(is_array($value)){
           $max_depth_size = max($max_depth_size,count($value));
       }
   }
   
   foreach($insert_row as $key => $value){
       $temp = [];
       if(is_array($value)){
           $value = array_values($value); // since data is malformed(would work even if it is ok)
           $val_size = count($value);           
           for($i = 0; $i < $max_depth_size; ++$i){
               if($i >= $val_size) $temp[$i] = '';
               else $temp[$i] = $value[$i];
           }
       }else{
           $temp = array_merge(array($value),array_fill(0, $max_depth_size - 1, ''));
       }
       
       $final_row_data[] = $temp;
   }
   
   for($column = 0;$column < $max_depth_size; ++$column){
       fputcsv($fhandle,array_column($final_row_data, $column)); // add all formatted data to CSV
   }
}

fclose($fhandle);

score 0 · Answer 3 · answered Jul 03 '20 at 22:25

The first row of each "group" contains the max number of columns so it can be used to reliably fetch column data.

Tear off the title and url values as you iterate your input array so that the remaining data in the subarray has a consistent and easily manipulated structure.

Rows that have missing trailing columns do not matter when pushing csv rows into a file, so it is a waste of code to bother generating empty strings. Conversely, leading empty column values will be a problem -- this is why I add two empty strings when not adding the first row of a respective group.

Read the PSR coding standards to see recommendations on spacing and curly brace usage.

Code: (Demo)

$headers = array_shift($array);

$fhandle = fopen("new.csv", "a");

fputcsv($fhandle, $headers);

foreach ($array as $row) {
    if (empty($row[0])) {
        continue;
    }

    $titleAndUrl = array_splice($row, 0, 2);
   
    foreach ($row[0] as $column => $notUsed) {
        fputcsv(
            $fhandle,
            array_merge(
                !$column ? $titleAndUrl : ['', ''],
                array_column($row, $column)
            )
        );
    }
}

fclose($fhandle);

See demo for output in array form.

How to export a multi dimensional array to a specific .csv layout with fputcsv PHP

3 Answers3