title: Read Excel file in array or get adyacent columns values with PHPExcel
body: <p>I have a Excel file with content like the showed in the image:</p>
  
  <p><img src="https://i.stack.imgur.com/Cr0vT.png" alt="enter image description here"></p>
  
  <p>As you may see there is basically three columns (could be four or more) which represents categories and subcategories. I need to read this Excel file for then write some SQL sentences like for example:</p>
  
  <pre><code>INSERT INTO `category` (name, parent, status, modified) VALUES('Electronica', NULL, 1, NOW());
  INSERT INTO `category` (name, parent, status, modified) VALUES('Celulares y Telefonos', 1, 1, NOW());
  INSERT INTO `category` (name, parent, status, modified) VALUES('Accesorios para celulares', 2, 1, NOW());
  </code></pre>
  
  <p>I know how to read the file using PHPExcel_IO but don't know how to get the parent > childrens relationship in order to build the proper SQL sentences, any help or advice?</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>This is what I have done so far. First <a href="https://mega.co.nz/#!8tUiRbaR!FWrgaQWo7Wu7IGbMHY2Tr16-FeRreHPP_cOHukzYG04" rel="nofollow noreferrer">here</a> is the complete source if yours have any problems please feel free to contact me in order to send the file (I do this because the Array is to large and services like pastie.org doesn't allow to publish it and here will ugly the post).</p>
  
  <p>I created this function to read the array data:</p>
  
  <pre><code>function print_r_reverse($in) {
      $lines = explode("\n", trim($in));
      if (trim($lines[0]) != 'Array') {
          return $in;
      } else {
          if (preg_match("/(\s{5,})\(/", $lines[1], $match)) {
              $spaces = $match[1];
              $spaces_length = strlen($spaces);
              $lines_total = count($lines);
              for ($i = 0; $i &lt; $lines_total; $i++) {
                  if (substr($lines[$i], 0, $spaces_length) == $spaces) {
                      $lines[$i] = substr($lines[$i], $spaces_length);
                  }
              }
          }
          array_shift($lines); // Array 
          array_shift($lines); // ( 
          array_pop($lines); // ) 
          $in = implode("\n", $lines);
          preg_match_all("/^\s{4}\[(.+?)\] \=\&gt; /m", $in, $matches, PREG_OFFSET_CAPTURE | PREG_SET_ORDER);
          $pos = array();
          $previous_key = '';
          $in_length = strlen($in);
          foreach ($matches as $match) {
              $key = $match[1][0];
              $start = $match[0][1] + strlen($match[0][0]);
              $pos[$key] = array($start, $in_length);
              if ($previous_key != '')
                  $pos[$previous_key][1] = $match[0][1] - 1;
              $previous_key = $key;
          }
          $ret = array();
          foreach ($pos as $key =&gt; $where) {
              $ret[$key] = print_r_reverse(substr($in, $where[0], $where[1] - $where[0]));
          }
          return $ret;
      }
  }
  </code></pre>
  
  <p>And this is the code I have created to read the array values and write the SQL sentences:</p>
  
  <pre><code>$result = print_r_reverse($arrayData);
  
  class RPMQGen {
  
      var $array_to = "";
      var $count = 1;
      var $last_count_parent = -1;
      var $t_name = "";
  
      function RPMQGen($tableName, $array) {
          $this-&gt;t_name = $tableName;
          $this-&gt;array_to = $array;
      }
  
      function walk_children($value) {
          $query = "";
          $key_result = 0;
          $index_count = 0;
          foreach ($value as $key =&gt; $val)
              if (strlen($val) &gt; 1) {
                  $index_count++;
                  $key_result = $key;
              };
  
          if ($index_count &gt; 1) {
              foreach ($value as $key =&gt; $val) {
  
                  if (strlen($val) &gt; 1) {
                      $query = $query . "INSERT INTO `" . $this-&gt;t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this-&gt;count . ",`" . $val . "`, " . ($key + 1) . ", 1, NULL); &lt;br&gt;";
                      $this-&gt;count++;
                  }
              }
              $this-&gt;last_count_parent = $this-&gt;count;
          } else
          if ($index_count == 1) {
              $query = $query . "INSERT INTO `" . $this-&gt;t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this-&gt;count . ",`" . ($value[$key_result]) . "`, " . ($this-&gt;last_count_parent - 1) . ", 1, NULL); &lt;br&gt;";
              $this-&gt;count++;
          }
          return $query;
      }
  
      function get_queries() {
          $this-&gt;count = 2;
          $query = "INSERT INTO `" . $this-&gt;t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (1,`root`, NULL, NULL); &lt;br&gt;";
  
          foreach ($this-&gt;array_to as $key =&gt; $value) {
              $query = $query . "INSERT INTO `" . $this-&gt;t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this-&gt;count . ",`" . $key . "`, 0, NULL); &lt;br&gt;";
              $this-&gt;count++;
              foreach ($value as $key =&gt; $value2) {
                  $query = $query . $this-&gt;walk_children($value2);
              }
          }
          print($query);
      }
  
  }
  
  $qgen = new RPMQGen('category', $result);
  $qgen-&gt;get_queries();
  </code></pre>
  
  <p>But parent > children relations aren't right and I can't find the cause</p>