parsing xml file using simplexml and create array with named keys(not numerical keys) using php

Question

i had already created multidimensional array from parsed xml file using simplexml with numerical keys but i want them to be named keys instead of numbers .

xml file is as below:

<workbook  xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" >
<Worksheet ss:Name="tab1">
<Table>
   <Row>
        <Cell><Data>Id</Data></Cell>   // names which i want to be array keys. 
        <Cell><Data>Company</Data></Cell>  //
        <Cell><Data>Year</Data></Cell>    //
   </Row>
   <Row>
        <Cell><Data>120</Data></Cell>   //values 
        <Cell><Data>Apple</Data></Cell>
        <Cell><Data>2011</Data></Cell>
   </Row>
   <Row>
        <Cell><Data>121</Data></Cell>
        <Cell><Data>Samsung</Data></Cell>
        <Cell><Data>2010</Data></Cell>
   </Row>
</Table>
</Worksheet>
<Worksheet ss:Name="tab2">
 <Table>
    <Row>
        <Cell><Data>Id</Data></Cell>      
        <Cell><Data>Company</Data></Cell>   
        <Cell><Data>Year</Data></Cell>
   </Row>
   <Row>
       <Cell><Data>320</Data></Cell>  
       <Cell><Data>Sony</Data></Cell>
       <Cell><Data>2001</Data></Cell> 
   </Row>
   <Row>
       <Cell><Data>321</Data></Cell>
       <Cell><Data>HTC</Data></Cell>
       <Cell><Data>2001</Data></Cell> 
   </Row> 
  </Table>
</Worksheet>
</workbook>

and her is my code for parsing xml file and creating array

$xml=simplexml_load_file($fileData);
$result= array();
$i=0;
foreach($xml->Worksheet as $worksheet ):
  $result['tab'][$i] = array();
  $result['tab'][$i]['name']=(string)$worksheet->attributes("ss", true)->Name;
  foreach($worksheet as $table):
    $k =0;
    unset($table->Row[0]); //removing first row which i want to be keys of array
    foreach($table as $row):
      foreach($row as  $cell):
         $result['tab'][$i]['data'][$k][] =(string)$cell->Data;
      endforeach;
      $k++;
    endforeach;
  endforeach;
  $i++;
endforeach;
return $result;

array which i am getting :

  Array
  (
  [tab] => Array
    (
        [0] => Array
            (
                [name] => tab1
                [data] => Array
                    (
                        [0] => Array
                            (
                                [0] => 120   //keys should be name of first row of xml 
                                [1] => Apple
                                [2] => 2011
                            )

                        [1] => Array
                            (
                                [0] => 121
                                [1] => Samsung
                                [2] => 2010
                            )

                    )

            )

        [1] => Array
            (
                [name] => tab2
                [data] => Array
                    (
                        [0] => Array
                            (
                                [0] => 320
                                [1] => Sony
                                [2] => 2001
                            )

                        [1] => Array
                            (
                                [0] => 321
                                [1] => HTC
                                [2] => 2001
                            )

                    )

            )

    )

)

i want array to be as below :

  Array
 (
  [tab] => Array
    (
        [0] => Array
            (
                [name] => tab1
                [data] => Array
                    (
                        [0] => Array
                            (
                                [Id] => 120          // named keys instead of numbers
                                [Company] => Apple
                                [Year] => 2011
                            )

                        [1] => Array
                            (
                                [Id] => 121
                                [Company] => Samsung
                                [Year] => 2010
                            )

                    )

            )

        [1] => Array
            (
                [name] => tab2
                [data] => Array
                    (
                        [0] => Array
                            (
                                [Id] => 320
                                [Company] => Sony
                                [Year] => 2001
                            )

                        [1] => Array
                            (
                                [Id] => 321
                                [Company] => HTC
                                [Year] => 2001
                            )

                    )

            )

    )

)

it's a bit long question . but well explained . Thanks.

score 3 · Accepted Answer · edited May 23 '17 at 10:34

Convert each table row into an array and then use the first row as keys for the subsequent rows:

$table_to_array = function(SimpleXMLElement $table) {
    $keyed = function($table) {
        $keys = NULL;
        foreach ($table->Row as $row) {
            $array = array_map('trim', $row->xpath('Cell/Data'));
            $keys  ? (yield array_combine($keys, $array))
                : $keys = $array;
        }
    };
    return iterator_to_array($keyed($table));
};

$xml = simplexml_load_file($path_to_xml_file);

$array = $table_to_array($xml->Worksheet->Table);

print_r($array);

Output:

Array
(
    [0] => Array
        (
            [Id] => 120
            [Company] => Apple
            [Year] => 2011
        )

    [1] => Array
        (
            [Id] => 121
            [Company] => Samsung
            [Year] => 2010
        )

)

Apply this onto as many tables as you like.

If you have PHP < 5.5 then you can not make use of Generators yet, however, you can still make use of Iterators. That more or less only means that you need to write more code:

// this example uses some code from:
// https://github.com/hakre/Iterator-Garden/blob/master/src/IndexIteration.php
require_once 'IndexIteration.php';

class TableRowIterator extends IteratorIterator
{
    public function __construct(SimpleXMLElement $table) {
        parent::__construct(new IndexIteration($table->Row));
    }

    public function current() {
        return array_map('trim', parent::current()->xpath('Cell/Data'));
    }

    public function key() {
        return $this->getInnerIterator()->getIndex();
    }
}

class KeyedArrayIterator extends IteratorIterator
{
    private $keys;

    public function rewind()
    {
        parent::rewind();
        $this->keys = parent::current();
        parent::next();
    }

    public function current()
    {
        return array_combine($this->keys, parent::current());
    }
}

$table_to_array = function($table) {
    $rows  = new TableRowIterator($table);
    $keyed = new KeyedArrayIterator($rows);
    return iterator_to_array($keyed);
};

$xml = simplexml_load_file($path_to_xml_file);

$array = $table_to_array($xml->Worksheet->Table);

print_r($array);

As it shows, some more lines of code, but the same way to process the data. Compare the KeyedArrayIterator with the CSVFile Iterator class in the commented answer, it's that way of processing: Use the first row as keys for all subsequent rows.

The output is exactly the same as in the PHP 5.5 example with the generator.

wow, closure creates generator that yields array_combine and iterator_to_array :) — pce, May 07 '14 at 17:49
Thanks! [My answer to the question *Process CSV Into Array With Column Headings For Key*](http://stackoverflow.com/a/10181302/367456) shows already how easy that is once you managed to turn each row into an array, it also works with PHP below 5.5 so less closures and generators. Just in case for some other way to write that down. It's also where the idea comes from, the question here reminded me to that one. — hakre, May 07 '14 at 17:55
No generators are PHP 5.5. See the linked question for code that works with Iterators that are not a generator. — hakre, May 07 '14 at 19:07
No, it is for a CSV file however it shows how to keep the first row (at that place it's an array already, so it's not the point whether it's CSV or XML or a file at all) as keys for all subsequent rows. — hakre, May 07 '14 at 19:24
there is no easier way to solve this problem ?...even if i use php 5.5 it does not return the array which i expect . is it possible to solve it using my code ? — nohan, May 07 '14 at 19:35
does it return the array which i want ? ...your output is totally different than mine — nohan, May 07 '14 at 19:41
This converts a single table element into the array as you specified it *for a single `` element*. Apply it on every table you want to convert, use it as a sub-array. It's a function, so use it where it fits. — hakre, May 07 '14 at 19:42

parsing xml file using simplexml and create array with named keys(not numerical keys) using php

1 Answers1