How to import excel data to mysql database in php?

In this tutorial, we will learn how to import excel to MySQL database.

Many times we face such problems when an organization want to upload data from Microsoft Excel sheet to directly database.

For example all employee database or all file records related to work or all vendor payment done etc.

So, if you have excel file data need to imported into MySQL, you can easily import data easily with PHP by using excel reader API.

Another method– Import excel sheet data to mysql database using phpExcel API.

Steps to import excel  to mySQL

First download the php excel reader form the following link – http://sourceforge.net/projects/phpexcelreader/

Lets start writing code for excel file uploading. First we need to create a form for file upload – Lets name is upload.php

<form action=”uploadfile.php” enctype=”multipart/form-data” method=”post”>

<input id=”fileToUpload” name=”fileToUpload” type=”file” />

<input name=”submit” type=”submit” value=”Upload” />

</form>

This form will have action to uploadfile.php, so we will write our import excel php code in this file that will upload excel spreadsheet to MySQL database.

PHP code uploadfile.php  –

require_once 'Excel/reader.php';
require 'dbconnect.php';

//Add reader.php to uploadfile.php and don't forget to add db connection file.

// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();

// Set output Encoding.
$data->setOutputEncoding('CP1251');

$data->read($target_file);   // $target_file will be your excel file path

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";

   mysql_query($sql);

}

if you have a big excel sheet with a big table then you can use –

$sql = "INSERT INTO `mytable` (";
for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
{
    $sql .= "`" . mysql_real_escape_string($data->sheets[0]['cells'][1][$j]) . "`,";
}
$sql = substr($sql, 0, -1) . ") VALUES\r\n";
//cells
for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++)
{
    $sql .= "(";
    for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++)
    {
    $sql .= "'" . mysql_real_escape_string($data->sheets[0]['cells'][$i][$j]) . "',";
    }
    $sql = substr($sql, 0, -1) . "),\r\n";
}
$sql =  substr($sql, 0, -3) . ";";

echo '<pre>';

mysql_query($sql);

//print_r($data);
//print_r($data->formatRecords);

When you run this script then each row will be added to the database dynamically.

Please share your comments if you have any problems.

I have written a post that describes another method to import excel sheet data to mysql database using phpexcel api.

For any help, drop me a message, I will happy to assist you. Thanks..:)