How to import excel file into mysql using php

With the help of php we can easily import our excel or csv file data in our Mysql database.
So i am going to show you some methods doing this easily

How to import excel file into mysql using php

To import excel data into php-mysql records first create a table with required fields. Make database connection. Open excel file and read columns one by one and store in variables.


Method-1: Import excel data using php script

$handle = fopen("BooksList.csv", "r");
while (($data = fgetcsv($handle)) !== FALSE) {
$num = count($data);
$row;
echo "INSERT into importing(text,number)values('$data[0]','$data[1]')";
echo "<br>";
}


Method-2:

$handle = fopen("BooksList.csv", "r");
$fields=array('category','datatype','date','value');
$table='test';
$sql_query = "INSERT INTO $table(". implode(',',$fields) .") VALUES(";
while (($data = fgetcsv($handle)) !== FALSE) {
    foreach($data as $key=>$value) {
            $data[$key] = "'" . addslashes($value) . "'";
        }
           $rows[] = implode(",",$data);
  }
$sql_query .= implode("),(", $rows);
$sql_query .= ")";
echo $sql_query;


Method:3 Using third party library like php-excel-reader

Download form http://code.google.com/p/php-excel-reader/downloads/list

require_once 'Excel/reader.php'; 
$data = new Spreadsheet_Excel_Reader();
$data->setOutputEncoding('CP1251');
$data->read('BooksList.xls');
for ($x = 2; $x<=count($data->sheets[0]["cells"]); $x++) {
    $name = $data->sheets[0]["cells"][$x][1];
    $extension = $data->sheets[0]["cells"][$x][2];
    $email = $data->sheets[0]["cells"][$x][3];
    $sql = "INSERT INTO mytable (name,extension,email) VALUES ('$name',$extension,'$email')";
    echo $sql."\n";
    echo "<br>";
 }

Hope this will help you to import your excel file into mysql db.
Thanks :)