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 ( "mpwapwa@live.com", "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 ( "mpwapwa@live.com", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", "annie@yahoo.com", "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 ( "mpwapwa@live.com", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", "annie@yahoo.com", "2011-04-10 08:21", "Chunyu", "2", "4", "5", "3", "56", "5", "6", "8", "45", "7", "8", "9", "8", "9", "8", " sam@yahoo.com", "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
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>";
}
}