reading xls file in php using codeigniter with Spreadsheet_Excel_Reader library

Question

am trying to read an excel file (i.e xls) and import the data to mysql database and am using codeigniter 2.0.1 framework.

here are the codes to generate query

    public function read_file($table = 'organization', $filename = 'test.xls') {

    $pathToFile = './uploads/' . $filename;
    $this->load->library('Spreadsheet_Excel_Reader');
    $data = new Spreadsheet_Excel_Reader($pathToFile);
    $sql = "INSERT INTO $table (";
    for($index = 1;$index <= $data->sheets[0]['numCols']; $index++){
        $sql.= strtolower($data->sheets[0]['cells'][1][$index]) . ", ";
    }

    $sql = rtrim($sql, ", ")." ) VALUES ( ";
    for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
        for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
            $sql .= "\"" . $data->sheets[0]['cells'][$i][$j] . "\", ";
        }
        echo  rtrim($sql, ", ")." ) <br>";
    }

}

the values generated in the loop are duplicated , i cant find the problem with the loop...

Here is the result after running the function above:

INSERT INTO organization (userid, datestamp, kata, kaya, kaya_zenye_vuoo, vyoo_vyenye_bamba, mifuniko, matumizi_mifuniko, uzuiaji_inzi, usafishikaji_sakafu, usitiri, uezekaji, milango_inayofungika, harufu, wadudu, sabuni, maji, vibuyuchirizi ) VALUES ( "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8" ) 
INSERT INTO organization (userid, datestamp, kata, kaya, kaya_zenye_vuoo, vyoo_vyenye_bamba, mifuniko, matumizi_mifuniko, uzuiaji_inzi, usafishikaji_sakafu, usitiri, uezekaji, milango_inayofungika, harufu, wadudu, sabuni, maji, vibuyuchirizi ) VALUES ( "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8" ) 
INSERT INTO organization (userid, datestamp, kata, kaya, kaya_zenye_vuoo, vyoo_vyenye_bamba, mifuniko, matumizi_mifuniko, uzuiaji_inzi, usafishikaji_sakafu, usitiri, uezekaji, milango_inayofungika, harufu, wadudu, sabuni, maji, vibuyuchirizi ) VALUES ( "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", "[email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", " [email protected]", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8" )

Sorry i cant post images as i'm a newbie.

Thanx in advance..Cheers

CristiC · Accepted Answer · 2011-05-17 09:55:31Z

1

I cannot test it, but... :

    public function read_file($table = 'organization', $filename = 'test.xls') {

    $pathToFile = './uploads/' . $filename;
    $this->load->library('Spreadsheet_Excel_Reader');
    $data = new Spreadsheet_Excel_Reader($pathToFile);
    $sql = "INSERT INTO $table (";
    for($index = 1;$index <= $data->sheets[0]['numCols']; $index++){
        $sql.= strtolower($data->sheets[0]['cells'][1][$index]) . ", ";
    }

    $sql = rtrim($sql, ", ")." ) VALUES ( ";
    for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
        $valuesSQL = '';
        for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
            $valuesSql .= "\"" . $data->sheets[0]['cells'][$i][$j] . "\", ";
        }
        echo $sql . rtrim($valuesSql, ", ")." ) <br>";
    }
}

answered May 17, 2011 at 9:55

CristiC

22.7k13 gold badges62 silver badges89 bronze badges

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

2 Comments

Sam Samson Over a year ago

thanx a lot for helping out..The values of $valuesSQL is overwiten as we are assigning different values to the same variable i think putting them in an array will be the best practice.. Can u please explain to me why the codes failed in the first place?..thanx u very much u saved my day..

CristiC Over a year ago

First in your $sql you kept INSERT INTO... VALUES ( and then you concatenate to this string all the rows and cols. Instead, I was using for every row the initial $sql and concatenate it with cells from that row.

Collectives™ on Stack Overflow

reading xls file in php using codeigniter with Spreadsheet_Excel_Reader library

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related