Home > database >  Convert array from spreadsheet into associative array with header row as keys
Convert array from spreadsheet into associative array with header row as keys

Time:04-18

I've been having difficulty visualizing how to return a specific array pattern from my extracted array from the spreadsheet table. You may refer to the extracted arrays below.

Here are the extracted arrays from my spreadsheet table

Array
(
    [0] => Array
        (
            [0] => Order Number
            [1] => Status
        )

    [1] => Array
        (
            [0] => 1111
            [1] => Shipped
        )

    [2] => Array
        (
            [0] => 2222
            [1] => For Veri
        )

    [3] => Array
        (
            [0] => 3333
            [1] => Delivered
        )

    [4] => Array
        (
            [0] => 4444
            [1] => Problematic
        )

    [5] => Array
        (
            [0] => 5555
            [1] => Onhold
        )

)

I would like for the array to be returned as below:

 Array(
      [1111] => Array
         {  
          [Order Number] => 1111
          [Status] => Delivered
          }
       [2222] => Array
         {  
          [Order Number] => 2222
          [Status] => Delivered
          }
     )

Would like to confirm if the array_combine function would work on this? Any help would be greatly appreciated.

Edit: This has been resolved thanks to
Markus AO. Had to make some tweaks with the provided snippets and was able to get the expected result.

 //$retrieveArray = extracted arrays from the spreadsheet table.
        $index = null;
        $keys = array_shift($retrieveArray);

        //to declare new set of array
        $named = [];
        
        // to loop the remaining array 
        foreach($retrieveArray as $ln => $vals) {
            $key = !is_null($index) ? $vals[$index] : $ln;
            $named[$key] = array_combine($keys, $vals);
        }

echo "<pre>";
print_r($named);
echo "</pre>";

Updated Output:

Array
(
    [0] => Array
        (
            [Order Number] => 1111
            [Status] => Shipped
        )

    [1] => Array
        (
            [Order Number] => 2222
            [Status] => For Veri
        )

    [2] => Array
        (
            [Order Number] => 3333
            [Status] => Delivered
        )

    [3] => Array
        (
            [Order Number] => 4444
            [Status] => Problematic
        )

    [4] => Array
        (
            [Order Number] => 5555
            [Status] => Onhold
        )

)

Thanks!

CodePudding user response:

This is a common issue with spreadsheets and CSV data imported into arrays: The keys become the first array member. It's easy enough to rebuild into an associative array. All we need is to shift the keys off the first row, and then combine them with all remaining values. As follows:

function named_keys(array $array, ?int $index = null): array 
{
    // Shift the keys from the first row: 
    $keys = array_shift($array);

    $named = [];
    // Loop and build remaining rows into a new array:
    
    foreach($array as $ln => $vals) {

        // Using specific index or row numbers?
        $key = !is_null($index) ? $vals[$index] : $ln;

        // Combine keys and values:
        $named[$key] = array_combine($keys, $vals);
    }

    return $named;
}

First argument $array is the source array itself. Second argument $index (optional) is the index (numeric) for the value that should be used as the index of the resulting array. If none, row numbers from source array (starting from 1) are used. Works as follows:

$raw = [['a', 'b'], [111,'foo'], [333,'bar'], [555,'nix']];

$named = named_keys($raw, 0);

/* // results in:

array(3) {
    [111] · array(2) {
        ["a"] · int(111)
        ["b"] · string(3) "foo"
    }
    [333] · array(2) {
        ["a"] · int(333)
        ["b"] · string(3) "bar"
    }
    [555] · array(2) {
        ["a"] · int(555)
        ["b"] · string(3) "nix"
    }
} */
  • Related