What is the PHP Code to import excel data into mysql?

The logical solution is 1. Read csv/excel contents using PHP 2. Insert into Mysql 3. Close file Example PHP code $source = fopen('email.csv', 'r') or die("Problem open file"); while (($data = fgetcsv($source, 1000, ",")) !== FALSE){         mysql_query("INSERT INTO `table` (`name`,`email`) VALUES ('".$data[0]."','".$data[1]."') "); } fclose($source);

There is a class here that can be used to import CSV file data into MySQL: http://www.phpclasses.org/package/842-PHP-convert-fields-in-a-csv-into-SQL.html

You can auto generate the PHP code for importing excel to mysql, by selecting the "import Excel To MySQL Indirectly" using @http://softgalaxy.net/index.html . try free demo version from this link : http://softgalaxy.net/excel-mysql/ExcelMySQlConverterDemo.exe

You can use php excel reader to import excel sheet in mysql database. Please follow the links  for full solution - http://www.ecomspark.com/how-to-import-excel-data-to-mysql-database-in-php/

Hi Vinay, thanks for asking question. Here are some resources which might help you.https://packagist.org/packages/php-excel-reader/spreadsheet-reader A class which allows you to read excel 97–2003 format files. Once you are able to parse XLS file into PHP array and objects using this library, it become very trivial to insert those into MySQL.https://github.com/nuovo/spreadsheet-reader is an alternate (and probably better) class for the same task as it allow to read CSV, ODS, XLS and XLSX (new version format for excel files) and gives you an array of rows, where each array element (excel row) is an array with each element being cell (php way of 2d array?)Though instead of looking for `php code`, better to look for GitHub for open source libraries and then understanding the concept! That will be helpful in long run.

Forget CSV and forget everything because your users are not going to convert it into CSV before uploading it.I recently came across similar issue and the answer to this was https://github.com/PHPOffice/PHPExcel It reads the excel sheets and you can then write your queries into those loops.It has lots of features with many php examples given on its site.

If you need single command in addition to your php code, you can use LOAD DATA INFILE. Following article have explained many use cases for http://kedar.nitty-witty.com/blog/load-delimited-data-csv-excel-into-mysql-server

db.php ---------------------- <?php define('DB_SERVER', 'localhost'); define('DB_USERNAME', 'username'); define('DB_PASSWORD', 'password'); define('DB_DATABASE', 'database'); $connection = mysqli_connect(DB_SERVER,DB_USERNAME,DB_PASSWORD,DB_DATABASE); ?> index.php ------------------------ <?php ini_set("display_errors",1); require_once 'excel_reader2.php'; require_once 'db.php'; $data = new Spreadsheet_Excel_Reader("example.xls"); echo "Total Sheets in this xls file: ".count($data->sheets)."<br /><br />"; $html="<table border='1'>"; for($i=0;$i<count($data->sheets);$i++) // Loop to get all sheets in a file. { if(count($data->sheets[$i][cells])>0) // checking sheet not empty { echo "Sheet $i:<br /><br />Total rows in sheet $i ".count($data->sheets[$i][cells])."<br />"; for($j=1;$j<=count($data->sheets[$i][cells]);$j++) // loop used to get each row of the sheet { $html.="<tr>"; for($k=1;$k<=count($data->sheets[$i][cells][$j]);$k++) // This loop is created to get data in a table format. { $html.="<td>"; $html.=$data->sheets[$i][cells][$j][$k]; $html.="</td>"; } $eid = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][1]); $name = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][2]); $email = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][3]); $dob = mysqli_real_escape_string($connection,$data->sheets[$i][cells][$j][4]); $query = "insert into excel(eid,name,email,dob) values('".$eid."','".$name."','".$email."','".$dob."')"; mysqli_query($connection,$query); $html.="</tr>"; } } } $html.="</table>"; echo $html; echo "<br />Data Inserted in dababase"; ?> db.sql ----------------------- CREATE TABLE `excel` ( `id` INT( 10 ) NOT NULL AUTO_INCREMENT PRIMARY KEY , `eid` VARCHAR( 100 ) NOT NULL , `name` VARCHAR( 200 ) NOT NULL , `email` VARCHAR( 200 ) NOT NULL , `dob` VARCHAR( 40 ) NOT NULL ) ENGINE = MYISAM ;

two solutions come to mind 1 Export data from excell as a csv and use the command line bulk loader or a script to read the csv and insert it into mysql 2 There are ADO (or whatever MS calls this now) drivers for Excell use php to read from that and write to my sql - might be slow for large numbers on inserts. BTW I think Mike is sugesting you try reading the manual  :-)