Hi, this time I have come up with another useful php script, which will show you How to Import CSV File Data into MySQL Database using PHP. CSV Files commonly known as comma separated values is one of the most preferred data storage methods. And as a developer often you might find you in situation to import those huge csv data into database system. Though sounding simple, it's a nightmare in disguise. Here's where php comes to the rescue. With few lines of code, you can insert several hundreds of records in flash.
PHP supports several data formats like csv, json etc. and provides dedicated functions to handle such data formats. Hence without any third party solutions, we can to parse and store csv records in database using core php alone.
![Import CSV File into MySQL Database using PHP php script import csv into mysql database](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhWcvJMfFinmEcW-IHfRFbuDNwnTFDgXbVcEPifk4-_6CUqxF6DLTvpoGSi8I0yIw2uGoqQ19QY4SiuVRjJWVTXLMKLR6-hyPExMQNF1pp93IuJywltDs6kza6tfqzbA2wUiLdSZkXOsxPk/s1600/php-script-to-import-csv-file-data-into-mysql-database.png)
- How to Convert CSV To JSON File using PHP
- How to Import JSON File into MySQL using PHP
- How to Export MySQL to JSON File using PHP
Create Sample MySQL Database
As an example, let's use our previous 'library' database for this tutorial. Just create a new database with name 'library' in mysql. Next you should create a table 'books' to store the details of the books in the library. For that run the below sql command.
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO"; -- -- Database: `library` -- -- Table structure for table `books` -- CREATE TABLE IF NOT EXISTS `books` ( `id` int(8) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `author` varchar(30) NOT NULL, `isbn` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Sample CSV File
Below is the sample csv file containing the books data and we'll import this csv file to the 'books' table.
books.csvLearning PHP MySQL & JavaScript,Robin Nixon,ISBN-13: 978-1491918661 PHP and MySQL for Dynamic Web Sites,Larry Ullman,ISBN-13: 978-0321784070 PHP Cookbook,David Sklar,ISBN-13: 978-1449363758 Programming PHP,Kevin Tatroe,ISBN-13: 978-1449392772 Modern PHP New Features and Good Practices,Josh Lockhart,ISBN-13: 978-1491905012
Now it's time to move on to the php script part.
PHP Script to Import CSV File Data into MySQL Database
The CSV import process goes like this. With PHP open the '.csv' file, parse through it one line at a time and insert into mysql db. Following is the step by step procedure for doing so.
Step-1: Set the Database Connection Variables
First let's set the database connection variables and the right csv file path to import.
<?php //set the connection variables $hostname = "localhost"; $username = "username"; $password = "password"; $database = "library"; $filename = "books.csv"; ?>
Step-2: Establish the MySQL Database Connection
Next we open the connection to mysql database using mysqli library.
<?php //connect to mysql database $connection = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($connection)); ?>
Step-3: Open and Parse the CSV File
Now we should open the csv file in 'reading' mode and parse the record one at a time and insert into mysql table 'books'.
<?php // open the csv file $fp = fopen($filename,"r"); //parse the csv file row by row while(($row = fgetcsv($fp,"500",",")) != FALSE) { //insert csv data into mysql table $sql = "INSERT INTO tbl_books (name, author, isbn) VALUES('" . implode("','",$row) . "')"; if(!mysqli_query($connection, $sql)) { die('Error : ' . mysqli_error()); } } fclose($fp); ?>
The line $fp = fopen($filename,"r");
will open the 'books.csv' file for reading and return the file handler to the $fp variable.
Next we loop through the entire file one record at a time. Using the line $row = fgetcsv($fp,"500",",")
will fetch the complete row of the csv file one at a time, and "500" specifies the maximum length of the rows in the csv file and "," specifies the delimiter.
Finally we implode the fetched row and save it into mysql database using the INSERT query.
The line fclose($fp);
closes the file handler after completely parsing through the entire file.
Step-4: Close the MySQL Database Connection
After inserting all the csv records into mysql, we finally close the database connection we established first.
<?php //close the db connection mysqli_close($connection); ?>
Here is the Complete PHP Script to Import CSV File into MySQL Database.
<?php //set the connection variables $hostname = "localhost"; $username = "username"; $password = "password"; $database = "library"; $filename = "books.csv"; //connect to mysql database $connection = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($connection)); // open the csv file $fp = fopen($filename,"r"); //parse the csv file row by row while(($row = fgetcsv($fp,"500",",")) != FALSE) { //insert csv data into mysql table $sql = "INSERT INTO tbl_books (name, author, isbn) VALUES('" . implode("','",$row) . "')"; if(!mysqli_query($connection, $sql)) { die('Error : ' . mysqli_error()); } } fclose($fp); //close the db connection mysqli_close($connection); ?>
Now go back to mysql and check the library database after running the php snippet. You will find the csv file records inserted into the 'books' table.
And that was all about importing csv file into mysql database using php. There's also the reverse process of exporting mysql database to csv file with php. You can check it out.
Greetings! Thank you for taking the time to share. Question: When i use the code (below), only the first row imports into MySQL database. I've searched online for different answers, yet no one has any guidance. Could you please help??
ReplyDeleteGreetings! i've search everywhere to find an answer to my problem: My CSV file only imports the top line when i execute the code from php. It works fine when I manually import via phpmyadmin. I tried your code and it does the same thing. My code is below, can you shed some light into what I'm doing wrong? Thank you for your time!
ReplyDeleteHi, provide me the code and the sample csv you are trying! I'm happy to help :)
DeleteCheers.
Nice Article !
ReplyDeleteReally this will help to people of PHP & MySQL Community.
I have also prepared small demonstration on, how to import and export csv data with headers using MySQL.
You can visit my article using below link.
http://www.dbrnd.com/2015/09/mysql-import-and-export-csv-data-with-headers/
we are offering Website designs, services, and also make a Android & IOS Applications..... Buzz App
ReplyDeleteWe have IVR System for your Bank and stock account balances and transfers, Surveys and polls, Office call routing, Call center forwarding, Simple order entry transactions, Selective information lookup (movie schedules, etc.)
ReplyDeleteHosted IVR
I thought this was an excellent as well as useful document to read. We've truly enjoyed doing this very cool facts.wp plugin
ReplyDeleteThanks for your kind words!!!
DeleteI learned new concept. Thanks for sharing this post.
ReplyDeleteeCommerce website development in Chennai
Good post, Keep sharing such a informative post.
ReplyDeleteMagento eCommerce Website Development
The only thing, that confused me a little bit was tbl_books instead books in line 19.
ReplyDeleteMany Thanks! It was a great help for me!