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:
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.
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.
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.
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.
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.
We are a Website Development, WebApp Development, and Digital Marketing Company, providing services to enterprises of all shapes and sizes, across the world. In our blog, we talk about the latest in Tech, Technical Tutorials, and our general opinions, among other things 🙂 Please feel free to reach us through the comment box or via the contact us page if you’d like to know more about our services.
Sir Please help me. i want to first take input of excel file then save in mysql database. Please send me the Code
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.
sir please help me how to insert excel and some input fields data together in table so how to insert
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 🙂
nice one loved it
Thanks 🙂
In the ‘INSERT’ query you are calling $db->escape(), what is that?
$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 🙂
WHat if i want to make the excel file is uploaded at the front end?
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.
nice thax a lot
Glad you liked it 🙂
How much rows i can upload in Mysql using PHP Excel?
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… 🙂
oke
Is it possible to update the db compare with excel file?
Yes, of course. Connect with database, upload the file and create your query.
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
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.
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…!!
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?
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:
[php]
$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;
}
}
}
[/php]
Hope this helps anyone. Cheerss.. Happy coding.. 🙂
the 1 row A column can’t read correctly，is it normal？
You have to explain more. What do you mean by can’t read correctly? Is it giving any error?
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!!
Muchas gracias, me sirvió mucho
De nada querida 🙂
I have uploaded excel with 1000 rows. It loads full rows and displayed. But while submit to insert into database it inserts only 247 rows. Is this phpexcel error?
Please make sure you have error reporting turned on just in case there’s an error. See if the upload is taking quite a long time in which case it could be an execution time issue. if you need any professional help, please feel free to reach me via the PPH link.