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 🙂