Import data into mysql from excel file using phpexcel – the developer logs
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. Database cleaner 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. Data recovery hard disk 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. Data recovery jakarta PHPExcel. 7 data recovery suite registration code It’s easy to use, has a ton of examples, has a lot of support and Q&A on stackoverflow. Data recovery raw What I’m telling is no different than what’s already out there, but it should be a part of a developers blog. Database library 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.
Data recovery ssd The info he receives is provided to him daily in the lot of 100s and he has to upload them into the system. Data recovery on iphone 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. A data recovery tool We’ll run the following query for that:
No our table is ready. Data recovery expert For importing the data we’ll need the latest PHPExcel Library. Data recovery jobs The latest stable release is PHPExcel 1.8.0, which we can download at:
In addition to this I’m also using a MySQLi library for db management, taken directly from opencart, as it’s light and reliable. Database concepts 6th edition 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. Database kernel 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. Raid 5 data recovery software 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). Data recovery pro We’ll use the “getWorksheetIterator” function of the PHPExcel class to iterate through the cells and get the data in a desired array.
Now, all we have to do is insert the data into our MySQL database. Data recovery tools mac 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.
$query = $db->query(“INSERT INTO employees SET fname = ‘” . Z wave database $db->escape($val[‘1’]) . Database orm “‘, lname = ‘” . Database workbench $db->escape($val[‘2’]) . Database best practices “‘, email = ‘” . Database schema $db->escape($val[‘3’]) . Data recovery external hard drive “‘, phone = ‘” . Database 1 to many $db->escape($val[‘4’]) . Data recovery galaxy s6 “‘, company = ‘” . Os x data recovery software $db->escape($val[‘5’]) . Database technology “‘”);
That’s it. Database queries definition If there’s an error the DB library will print it out. Data recovery mac free 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. Data recovery phone You can download the entire code and required files from HERE