0

I have a Excel file with content like the showed in the image:

enter image description here

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:

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());

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?

UPDATE

This is what I have done so far. First here 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).

I created this function to read the array data:

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 < $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}\[(.+?)\] \=\> /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 => $where) {
            $ret[$key] = print_r_reverse(substr($in, $where[0], $where[1] - $where[0]));
        }
        return $ret;
    }
}

And this is the code I have created to read the array values and write the SQL sentences:

$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->t_name = $tableName;
        $this->array_to = $array;
    }

    function walk_children($value) {
        $query = "";
        $key_result = 0;
        $index_count = 0;
        foreach ($value as $key => $val)
            if (strlen($val) > 1) {
                $index_count++;
                $key_result = $key;
            };

        if ($index_count > 1) {
            foreach ($value as $key => $val) {

                if (strlen($val) > 1) {
                    $query = $query . "INSERT INTO `" . $this->t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this->count . ",`" . $val . "`, " . ($key + 1) . ", 1, NULL); <br>";
                    $this->count++;
                }
            }
            $this->last_count_parent = $this->count;
        } else
        if ($index_count == 1) {
            $query = $query . "INSERT INTO `" . $this->t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this->count . ",`" . ($value[$key_result]) . "`, " . ($this->last_count_parent - 1) . ", 1, NULL); <br>";
            $this->count++;
        }
        return $query;
    }

    function get_queries() {
        $this->count = 2;
        $query = "INSERT INTO `" . $this->t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (1,`root`, NULL, NULL); <br>";

        foreach ($this->array_to as $key => $value) {
            $query = $query . "INSERT INTO `" . $this->t_name . "` (`id`, `name`, `parent`, `deletedAt`) VALUES (" . $this->count . ",`" . $key . "`, 0, NULL); <br>";
            $this->count++;
            foreach ($value as $key => $value2) {
                $query = $query . $this->walk_children($value2);
            }
        }
        print($query);
    }

}

$qgen = new RPMQGen('category', $result);
$qgen->get_queries();

But parent > children relations aren't right and I can't find the cause

5
  • How you are defining relationship in Excel, can you please explain. I want to know how to define name, parent, status, modified. Commented Aug 21, 2013 at 12:51
  • @DeepakBiswal what you mean with "How you are defining relationship in Excel"? I don't follow you Commented Aug 21, 2013 at 12:55
  • I mean as per the excel sheet 1st row is name. Then what about parent and status? Commented Aug 21, 2013 at 12:56
  • Well status will be always the same meaning will get 1 as a value and parent should be calculated based on column position and row position I tough this is what I ask for some help Commented Aug 21, 2013 at 13:01
  • See this. I think this will solve your problem! Commented Aug 21, 2013 at 13:04

1 Answer 1

1

Make 4 loops, one for each column. If the value is not empty, it means that the product is part of that column category. Not that hard really.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.