Although rare, but there might come a case when you might have to ask someone to upload an excel file and use it’s data for storage purposes. Most of the times you’ll get a request for exporting data into an excel file and we’ll get there in the next post. But for now, let’s just say that we have a client that wants us to have his employees upload some data which he can save it in a database for further processing.

For this there are many PHP libraries available, but I”m are going to use the most common i.e. PHPExcel. It’s easy to use, has a ton of examples, has a lot of support and Q&A on stackoverflow. What I’m telling is no different than what’s already out there, but it should be a part of a developers blog. So here it is:

So, the client says that he has an excel spreadsheet having employees info which he would like in a MySQL database. The info he receives is provided to him daily in the lot of 100s and he has to upload them into the system. You can easily create a file upload system for him and then when the file is uploaded all you have to do is read it and save its content.

So we have a MySQL database `workforce` and in it we have to create an `employees` table with the given columns. We’ll run the following query for that:

CREATE TABLE IF NOT EXISTS `employees` (
  `id` mediumint(8) unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `fname` varchar(255) DEFAULT NULL,
  `lname` varchar(255) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `phone` varchar(100) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

No our table is ready. For importing the data we’ll need the latest PHPExcel Library. The latest stable release is PHPExcel 1.8.0, which we can download at:
PHPExcel by MarkBaker. It’s also available on github at PHPExcel on github, where you can also find it’s DOCUMENTATION.

In addition to this I’m also using a MySQLi library for db management, taken directly from opencart, as it’s light and reliable. You can download it along with the source files for this code.

Now, once your file is uploaded, we can manage it in our php code. The first thing to do is to include the required PHPExcel and MySQLi DB libraries.

/** Include PHPExcel and MySQLi db */
require_once dirname(__FILE__) . '/../Classes/DB.php';
require_once dirname(__FILE__) . '/../Classes/PHPExcel.php';

//Create DB object
use DB\MySQLi;

Next thing is to read the the excel cell contents. For this we’ll load the uploaded file for our library, if your uploaded file is not in the same directory as that of this php file, remember to use the absolute path for loading the excel file like (/var/file/html/upload/file.xlsx). We’ll use the “getWorksheetIterator” function of the PHPExcel class to iterate through the cells and get the data in a desired array.

// Create new PHPExcel object
$objPHPExcel = PHPExcel_IOFactory::load("employees.xlsx");

$dataArr = array();

foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
    $worksheetTitle     = $worksheet->getTitle();
    $highestRow         = $worksheet->getHighestRow(); // e.g. 10
    $highestColumn      = $worksheet->getHighestColumn(); // e.g 'F'
    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
    
    for ($row = 1; $row <= $highestRow; ++ $row) {
        for ($col = 0; $col < $highestColumnIndex; ++ $col) {
            $cell = $worksheet->getCellByColumnAndRow($col, $row);
            $val = $cell->getValue();
        	$dataArr[$row][$col] = $val;
        }
    }
}

No we’ll just unset the first row as that’s a header row which we won;t need to insert, at least in our example.

unset($dataArr[1]); // since in our example the first row is the header and not the actual data

Now, all we have to do is insert the data into our MySQL database. As we are using an OpenCart MySQLi library, all we have to do is make a connection and write a loop with the insert query.

$hostname = 'localhost';
$username = 'root';
$password = '';
$database = 'workforce';

$db = new MySQLi($hostname, $username, $password, $database);

foreach($dataArr as $val){
	$query = $db->query("INSERT INTO employees SET fname = '" . $db->escape($val['1']) . "', lname = '" . $db->escape($val['2']) . "', email = '" . $db->escape($val['3']) . "', phone = '" . $db->escape($val['4']) . "', company = '" . $db->escape($val['5']) . "'");
}

That’s it. If there’s an error the DB library will print it out. You also don’t have to escape the data as you can do so by using the $db->escape method.

If you have any question, feedback, requests or suggestions, feel free to say hello in the comment box below. You can download the entire code and required files from HERE

 
 

29 Comments

  1. Sir Please help me. i want to first take input of excel file then save in mysql database. Please send me the Code

    Reply

    • Can you be a bit more specific about ” i want to first take input of excel file then save in mysql database. “, what do you mean by first take the input of excel? You get to read the content of the cells in PHP and then you can manipulate it any way you want. If you can explain your issue in detail, I might be able to help.

      Reply

      • sir please help me how to insert excel and some input fields data together in table so how to insert

        Reply

        • Brajinder Singh

          Create a form and upload the excel using that form. You’ll have the input fields and the excel in the backend and then manipulate the fields as you please. Thanks 🙂

          Reply

  2. nice one loved it

    Reply

  3. In the ‘INSERT’ query you are calling $db->escape(), what is that?

    Reply

    • $db is the object created from MySQLi class in the above line. The $db->escape() should be equal to mysqli_real_escape_string(). If you are at it, please check and let me know if it’s working or giving any error, as I will check it later when I’ll have time and update accordingly. Thanks 🙂

      Reply

  4. WHat if i want to make the excel file is uploaded at the front end?

    Reply

    • Brajinder Singh

      Well! In that case you first have to upload the file and move it in a known location and then read the file from that location and import the data. There are many jQuery plugins available to upload files using AJAX. Use any one of them and return ‘import successful’ after the import is complete. IF you want to upload in a more traditional or basic way, my first post How to create a simple image upload form using PHP and MySQL, might help you. Although, it’s about image uploading but should work with uploading excel sheet as well.

      Reply

  5. nice thax a lot

    Reply

  6. How much rows i can upload in Mysql using PHP Excel?

    Reply

    • Brajinder Singh

      PHP Excel is quite an heavy library. I would not suggest you to use it if your rows are in the order of ten thousands. It really depends on the settings in the php.ini. You see, PHP excel converts every cell into an object and that takes execution time and increased memory limit. For larger files I would suggest another library:
      Spreadsheet Reader
      Git Link: https://github.com/nuovo/spreadsheet-reader

      Just try both and please share what worked for you the best.

      Happy Coding… 🙂

      Reply

  7. Is it possible to update the db compare with excel file?

    Reply

  8. Hello Brajinder,

    I’ve created two separate pages for importing the excel sheet. On page 1, I’m using jquery for moving the file to the desired file folder and redirecting user to PAGE 2 with file id. I’d like to dynamically call the value (EXCEL file name) on the IMPORT page that has the above code.

    I tried using GET ID and running query to fetch its name from database. The only bit is that im not able to get the dynamic value in the following field.

    $objPHPExcel = PHPExcel_IOFactory::load(“arr212($filename)”);

    Can you suggest the best possible way around this?

    thanks

    Reply

    • Please ignore the above error as i tweated the code a little and it is working now.

      For those, interested to know what i did;

      i changed

      $objPHPExcel = PHPExcel_IOFactory::load(“arr212($filename)”);

      to

      $objPHPExcel = PHPExcel_IOFactory::load($arr212[‘filename’]);

      and voila, it worked.

      Reply

      • Brajinder Singh

        Glad you figured it out. I’m not active on this blog anymore, so check in months only. Thank you for posting your solutions as well. I’m sure it will help any other someday.

        Happy Coding. Cheerrssss…!!

        Reply

  9. Any idea on how should we handle the dates? My excel sheet has date in the dd-mm-yyyy format (it has two date columns) and when doing this import, the date column doesn’t get any entry hence displaying a default 0000-00-00 entry in the mysql database. How do I address that?

    Also it is also skipping the data from the next column which comes right after the date column. for example, the format is;
    Id >> date >> code >> name

    And it is entering the
    ID = as AI entry
    Date = 0000-00-00
    code = has entry for name
    name = has entry for next column.. and so on so forth…

    Any idea on how can this be addressed?

    Reply

    • Brajinder Singh

      When your file has been uploaded and you have your array of the columns and rows, loop through it using foreach or while, isolate the date value and pass the value through a custom function which would ‘explode’ the date string on ‘-‘ and then using the array indexes change the position of the date, month and year to match the MySQL format of Y-m-d. I’m not going to test it but in theory the following should work:

      $date = '15-04-2018';
      
      echo $req_date = changeDateFormat($date);
      
      function changeDateFormat($date){
       if(!empty($date)){ 
         $date_arr = explode('-', $date);
         $req_date_rev = array_reverse($date_arr);
         $req_date = implode('-',$req_date_rev);
         return $req_date;  
       }else{
         return false;   
       }    
      }
      }
      

      Hope this helps anyone. Cheerss.. Happy coding.. 🙂

      Reply

  10. the 1 row A column can’t read correctly,is it normal?

    Reply

  11. Brajinder Singh

    Thank you for the appreciation 🙂 The design is actually a free theme and I also used it for the simplicity, and as it provided the functionality I needed. I wish to write more but my full time job doesn’t leave me with enough time to write much.

    Thanks again. Happy coding!!

    Reply

  12. With thanks! Valuable information!

    Reply

  13. Muchas gracias, me sirvió mucho

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.