In this post I will explain how to Import Excel Sheet Data in MySQL Database using PHP. If you follow the below steps we will successfully achieve the target.

For this tutorial we are going to work with a sample CSV file, which has the following fields. I will show an example of User Table of Engelsystem which contains the following fields Nick Name, First Name, Last Name, Email, Current City, Password, Mobile Number, Age.

Steps to Import Excel File Data in MYSQL Database using PHP

Step 1

First you have to create mysql database.

mysql> CREATE DATABASE engelsystem;

Step 2

Create table in your choosen database.

mysql> use DATABASE engelsystem;

The Table schema looks something like this.

 -- Table structure for table `User`
mysql> CREATE TABLE IF NOT EXISTS `User` (
  `UID` int(11) NOT NULL AUTO_INCREMENT,
  `Nick` varchar(23) NOT NULL DEFAULT '',
  `First Name` varchar(23) NOT NULL DEFAULT '',
  `Last Name` varchar(23) NOT NULL DEFAULT '',
  `email` varchar(123) DEFAULT NULL,
  `Age` int(4) DEFAULT NULL,      
  `current_city` varchar(255) DEFAULT NULL,
  `Password` varchar(128) DEFAULT  NULL,               
  `Mobile` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`UID`),
  UNIQUE KEY `Nick` (`Nick`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

Step 3

create your php excelsheet data uploading file. This is a sample code which I used for my project.

Ex: import_data.php

<?php

function admin_import() {

  if (isset($_REQUEST['upload'])) {
    $ok = true;
    $file = $_FILES['csv_file']['tmp_name'];
    $handle = fopen($file, "r");
    if ($file == NULL) {
      error(_('Please select a file to import'));
      redirect(page_link_to('admin_export'));
    }
    else {
      while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
        {
          $nick_name = $filesop[0];
          $first_name = $filesop[1];
          $last_name = $filesop[2];
          $email = $filesop[3];
          $age = $filesop[4];
          $current_city = $filesop[5];
          $password = $filesop[6];
          $mobile = $filesop[7];
// example error handling. We can add more as required for the database.

        if ( strlen($email) && preg_match("/^[a-z0-9._+-]{1,64}@(?:[a-z0-9-]{1,63}\.){1,125}[a-z]{2,63}$/", $mail) > 0) {
          if (! check_email($email)) {
            $ok = false;
            $msg .= error(_("E-mail address is not correct."), true);
          }
        }
// error handling for password        
        if (strlen($password) >= MIN_PASSWORD_LENGTH) {
            $ok = true;
          } else {
            $ok = false;
            $msg .= error(sprintf(_("Your password is too short (please use at least %s characters)."), MIN_PASSWORD_LENGTH), true);
        }
 // If the tests pass we can insert it into the database.       
        if ($ok) {
          $sql = sql_query("
            INSERT INTO `User` SET
            `Nick Name`='" . sql_escape($nick_name) . "',
            `First Name`='" . sql_escape($first_name) . "',
            `Last Name`='" . sql_escape($last_name) . "',
            `email`='" . sql_escape($email) . "',
            `Age`='" . sql_escape($age) . "',
            `current_city`='" . sql_escape($current_city) . "',
            `Password`='" . sql_escape($password) . "',
             `mobile`='" . sql_escape($mobile) . "',");
        }
      }

      if ($sql) {
        success(_("You database has imported successfully!"));
        redirect(page_link_to('admin_export'));
      } else {
        error(_('Sorry! There is some problem in the import file.'));
        redirect(page_link_to('admin_export'));
        }
    }
  }
//form_submit($name, $label) Renders the submit button of a form
//form_file($name, $label) Renders a form file box

 return page_with_title("Import Data", array(
   msg(),
  div('row', array(
          div('col-md-12', array(
              form(array(
                form_file('csv_file', _("Import user data from a csv file")),
                form_submit('upload', _("Import"))
              ))
          ))
      ))
  ));
}
?>

Step 4

The view of import_data.php looks something like this. Now that import_data.php is up and running. Access it through server browser and select the excel file to be imported.

Step 5

Prepare the excelsheet data and save it as .csv format.

Step 6

Now just browse the csv file and upload it.

Step 7

If we see the success message after importing. Then the data is successfully imported. We can also check whether the data is inserted in phpMYAdmin

Englesystem

Development: https://github.com/fossasia/engelsystem

Issues/Bugs: Issues