Import Excel File Data in MySQL Database using PHP
In this tutorial we will learn how to “Import Excel Sheet Data in MySQL Database using PHP. When we develop some database linked website where we have to enter data in database, sometime we have to upload bulk data in database. If we insert data one by one it will be very difficult and time consuming. Here You will learn how to insert bulk of data in database from excel sheet in a couple of minutes.
This script will import data from .csv file, so save your excel sheet in .csv file. The code is very simple we have two file here
- index.php
- connection.php
connection.php contain our MySQL connection detail that is localhost, username, password and obviously database name.
1
2
3
4
5
6
7
8
|
$hostname = "localhost";
$username = "root";
$password = "";
$database = "test";
$conn = mysql_connect("$hostname","$username","$password") or die(mysql_error());
mysql_select_db("$database", $conn);
|
index.php, it contain simple form to let user to upload file and some PHP code to insert that data in our MySQL database. So lets have a view on our index.php file
HTML form:
1
2
3
4
|
<form name="import" method="post" enctype="multipart/form-data">
<input type="file" name="file" /><br />
<input type="submit" name="submit" value="Submit" />
</form>
|
Please make sure that you have added enctype=”multipart/form-data” attribute in your form tag. So it can handle file uploading.
Here is our PHP code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
if(isset($_POST["submit"]))
{
$file = $_FILES['file']['tmp_name'];
$handle = fopen($file, "r");
$c = 0;
while(($filesop = fgetcsv($handle, 1000, ",")) !== false)
{
$name = $filesop[0];
$email = $filesop[1];
$sql = mysql_query("INSERT INTO csv (name, email) VALUES ('$name','$email')");
}
if($sql){
echo "You database has imported successfully";
}else{
echo "Sorry! There is some problem.";
}
}
|
In my case I have a database “test” and a table “csv”, csv table have three fields id, name and email. You can use your existing database and configure your query in above code and the important thing to note is that when you are making .csv file make it according to your MySQL database pattern. For example if you have name first in your query then make first column of excel sheet for name and up-to soon.
OK there is one question that how we can get the column address of excel sheet or .csv file. We are getting our data in form of an array. Then obviously we should have to treat it like an array (if you don’t know about array please read it at php.net). So it will work like this.
1
|
$filesop[number_of_column];
|
$filesop is our array name and number _of_column refers to our address of column of excel sheet. And array always start from 0 it means that the address of 1st column of excel sheet is 0 here.
Fell free to ask question in comment 
Comments
Post a Comment