If you are searching for the code which will help you to import the CSV data into your MYSQL database using PHP then your search is over here. MYSQL provides so many formats while exporting data from the database. The formats are like CSV, JSON, PDF, PHP Array, SQL, XML, etc. If you want to export the data in CSV then select the CSV format from the list and click on go button you will get one CSV file which will contain your data. We will see how to Import CSV data into MYSQL using PHP which will export or created as CSV file.
Note : Default SQL format is set for export data.
CSV : Short for Comma-separated values, CSV is tabular data that has been saved as plain text data separated by commas.
Why we need a script for Import CSV data into MYSQL using PHP?
In the current world, there are so many places where CSV files are used for data storing. Suppose you have only 10 records then it’s a not a big deal to add those data in table manually, however, if there will be 10,000 records then we cannot do those entries in database manually because it’s time-consuming process and there will be a chance of mistake in data entry if the data entry person will lose his concentration. To avoid this kind of mistakes we will use the script which will add data directly to a database.
For i.e in Magento, they will provide the product data in CSV file so it will be easy to add into a database.
Suppose we exported one file as CSV format from the database called sample.CSV which has data same like below.
Now we are going to import this data into new database. So for that, we need to create a new database called csv_import_demo. Inside that, I need to create one table called option_data_master.
Notes :
- The CSV file must be well formatted (which follow the “,” for separate the data).
- The CSV file must contain field name as first row (option_id,option_name,option_value).
Create Database :
CREATE DATABASE csv_import_demo;Create Table :
CREATE TABLE IF NOT EXISTS `option_data_master` (
`option_id` int(11) NOT NULL AUTO_INCREMENT,
`option_name` varchar(250) NOT NULL,
`option_value` varchar(250) NOT NULL,
PRIMARY KEY (`option_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;Here, I have created table which contain primary key and auto increment for option_id field.
Import csv data into mysql using php :
Direct import the CSV file data from the file path :
There are mainly two ways for it.
1. Using Sql Loader [Sql Feature]
2. Using fgetcsv [PHP Feature]
Using Sql Loader :
<?php
$connect = mysqli_connect('localhost','root','','csv_import_demo'); // First paramater stands for host, Second for Database-user, Third stand for Database-password, Forth Database-name.
if (!$connect) { //Connection is possible using above setting or not
die('Could not connect to MySQL: ' . mysqli_error());
}
$query = "LOAD DATA INFILE 'C:\wamp\www\importdata\sample.csv' INTO TABLE option_data_master FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' IGNORE 1 LINES;";
$result = mysqli_query($connect ,$query);
fclose($getdata);
?>Using fgetcsv :
<?php
$connect = mysqli_connect('localhost','root','','csv_import_demo'); // First paramater stands for host, Second for Database-user, Third stand for Database-password, Forth Database-name.
if (!$connect) { //Connection is possible using above setting or not
die('Could not connect to MySQL: ' . mysqli_error());
}
$filepath = "C:\wamp\www\importdata\sample.csv";
if (($getdata = fopen($filepath, "r")) !== FALSE) {
fgetcsv($getdata);
while (($data = fgetcsv($getdata)) !== FALSE) {
$fieldCount = count($data);
for ($c=0; $c < $fieldCount; $c++) {
$columnData[$c] = $data[$c];
}
$option_name = mysqli_real_escape_string($connect ,$columnData[0]);
$option_value = mysqli_real_escape_string($connect ,$columnData[1]);
$import_data[]="('".$option_name."','".$option_value."')";
// SQL Query to insert data into DataBase
}
$import_data = implode(",", $import_data);
$query = "INSERT INTO option_data_master(option_name,option_value) VALUES $import_data ;";
$result = mysqli_query($connect ,$query);
fclose($getdata);
}
echo "Data imported successfully.";
?>Upload the file and import the data from that specific file :
<?php
$connect = mysqli_connect('localhost','root','','csv_import_demo'); // First paramater stands for host, Second for Database-user, Third stand for Database-password, Forth Database-name.
if (!$connect) { //Connection is possible using above setting or not
die('Could not connect to MySQL: ' . mysqli_error());
}
$class="";
$message='';
$error=0;
$target_dir = dirname(__FILE__)."/Uploads/";
if(isset($_POST["import"]) && !empty($_FILES)) {
$target_file = $target_dir . basename($_FILES["fileToUpload"]["name"]);
$fileType = pathinfo($target_file,PATHINFO_EXTENSION);
if($fileType != "csv") // here we are checking for the file extension. We are not allowing othre then (.csv) extension .
{
$message .= "Sorry, only CSV file is allowed.<br>";
$error=1;
}
else
{
if (move_uploaded_file($_FILES["fileToUpload"]["tmp_name"], $target_file)) {
$message .="File uplaoded successfully.<br>";
if (($getdata = fopen($target_file, "r")) !== FALSE) {
fgetcsv($getdata);
while (($data = fgetcsv($getdata)) !== FALSE) {
$fieldCount = count($data);
for ($c=0; $c < $fieldCount; $c++) {
$columnData[$c] = $data[$c];
}
$option_name = mysqli_real_escape_string($connect ,$columnData[0]);
$option_value = mysqli_real_escape_string($connect ,$columnData[1]);
$import_data[]="('".$option_name."','".$option_value."')";
// SQL Query to insert data into DataBase
}
$import_data = implode(",", $import_data);
$query = "INSERT INTO option_data_master(option_name,option_value) VALUES $import_data ;";
$result = mysqli_query($connect ,$query);
$message .="Data imported successfully.";
fclose($getdata);
}
} else {
$message .="Sorry, there was an error uploading your file.";
$error=1;
}
}
}
$class="warning";
if($error!=1)
{
$class="success";
}
?>
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css">
</head>
<body>
<div class="container" style="margin-top:20px; margin-bottom:20px;padding:10px;">
<?php
if(!empty($message))
{
?>
<div class="btn-<?php echo $class;?>" style="width:30%;padding:10px;margin-bottom:20px;">
<?php
echo $message;
?>
</div>
<?php } ?>
<form role="form" action="<?php echo $_SERVER['REQUEST_URI'];?>" method="post" enctype="multipart/form-data">
<fieldset class="form-group">
<div class="form-group">
<input type="file" name="fileToUpload" id="fileToUpload">
<label for="image upload" class="control-label">Only .csv file is allowed. </label>
</div>
<div class="form-group">
<input type="submit" class="btn btn-warning" value="Import Data" name="import">
</div>
</fieldset>
</form>
</div>
</body>
</html>
I hope my this article will help you to import data from the CSV file. If you will get any problem while using it contact me on [email protected].
How to add external PHP file in PHP ? To understand this in detail click here.
I have tried your code. It’s working fine. Can you please write how to import excel to sql ?
Wonderful explanation.
Code is working awesome for me.
Hi ,
Thank’s for the code but it doesn’t work for me , i always get the error : Sorry, there was an error uploading your file. Can you tell me why ?
Thx for your answer 🙂
Hi Julien,
Can you please tell me which process you are following ?
If you are using wamp server then, first of all, create one folder XYZ in C:\wamp\www folder. After that create Uploads folder inside XYZ folder. So the Uploads folder resides like C:\wamp\www\XYZ\Uploads.
If you are LINUX OS user please give 777 permission to Uploads and XYZ folders.
Now Please check the file size which you are going to import. PHP default allows MAX 2MB size for upload. You can increase it by changing param “max_upload_size” params in php.ini file.
here you can find the source for above code. Please download it from Download Source.
If you still find any error please give your skype Id Or mail me at [email protected].
Thank you so much for your valuable feedback.
Thank you for the help and this is really great. came at a time when i need it most.
Thank you for the code because its really great and works well.
Was wondering if you can help to check for available details that were already
uploaded before inserting more into the database table.
Thank you Joy.
This is my first article that I have read on readmyviews.com .
Find helpful…..
A powerful share, I simply given this onto a colleague who was doing just a little analysis on this. And he in actual fact purchased me breakfast as a result of I found it for him.. smile. So let me reword that: Thnx for the deal with! However yeah Thnkx for spending the time to discuss this, I feel strongly about it and love studying more on this topic. If doable, as you grow to be expertise, would you mind updating your blog with more details? It is highly useful for me. Huge thumb up for this weblog put up!
Hello,
I am feeling happy by doing this. Thank you so much for your valuable reply. It’s encouraged me to do my best efforts for you guys.
Thank you for any other informative blog. The place else may just I am getting that kind of info written in such a perfect means? I’ve a challenge that I’m just now working on, and I have been on the look out for such info.
Hi there very cool web site!! Guy .. Excellent .. Superb .. I will bookmark your blog and take the feeds also…I’m happy to search out a lot of helpful information right here within the put up, we’d like work out extra techniques in this regard, thank you for sharing. . . . . .
please how to export mysql data in csv file using php code..
Hello Ghanshyam,
I will look into that and will help you to make this possible.
hi, i tried your code for “Direct import the CSV file data from the file path using php” and I got this “Data imported successfully.” it’s not working for me. When I checked the database table, the table is still empty.
Hello,
Can you please mail me your code with description. All the code which is mentioned here is tested twice.
Contact Me on [email protected]