Upload a CSV(excel) file and import into database using PHP and MYSQL

One of the best thinks to import data from the excel file into the MYSQL database. To save the data of the CSV file in the database, you have to use this type of PHP code. when you will click on the button(import) to save the CSV file data in the database table, for that you have to create a small web application in PHP. CSV file is a format type of excel file. It is important that the CSV file is imported and all data to be stored in the MySQL database. You can convert HTML form data into a CSV file. Let's learn about how to upload a CSV file and import excel file data into the database table.

Create a database table for importing data into a database table using PHP

To import excel file data, you have to create a MYSQL database. We store data from the excel file through the import a fle.

First of all create a database , where the CSV file data will be imported(saved )

Let's create a database table using the MYSQL query.

			
CREATE TABLE `ts_excel` (
 `id` int(40) NOT NULL,
 `ts_name` varchar(30) NOT NULL,
 `ts_email` varchar(40) NOT NULL
);

Create an excel file and fill the demo data

To insert data from an excel file into a database, you have to create demo data in an excel file. After creating demo data, you can insert excel data into a mysql database. Let's create demo data into an excel file and save as CSV format.

Make an HTML form for upload a CSV(excel) file

We create an HTML form to upload an excel file. In the form, we use the post method. With the help of the post method, we handle form data. With the help of the Post Method, the data of the CSV file gets converted and transferred on the server. Whenever we upload a file up, encryption is required. Let's make the HTML form.

HTML Form Code


<!DOCTYPE html>  
<html>  
<head>  
Techno Smarter Tutorials
</head> 
<body> 

Select CSV File:





</body>  
</html>

Create a connection file to connect PHP and MYSQL database

Create a connection file with the name config.php. A connection file helps in connect PHP script to the MYSQL database. After connection, we can easily import excel file data into the database.


<?php
$dbHost = 'localhost';
$dbName = 'tutorials';
$dbUsername = 'root';
$dbPassword = '';

$connect = mysqli_connect($dbHost, $dbUsername, $dbPassword, $dbName); 
?>

1. Define your host(localhost ,127.0.0.1 or global server host) .

2. Define your database name.

3. Define your username . (Root for localhost )

4. Define your databse password.(Blank for localhost )

Create PHP script code for upload an excel file and important into MYSQL database table

Now, we create a PHP script for CSV file, parse it in and display into database table.

In the PHP script code- 1. We include a config file using include() function. You can use require() function also.

2. We use the if condition (if file is set) .
3.Create code for upload a file.

4. Handle the CSV file fgetcsv() function.

5. Use MYSQL insert query to insert data into database table.

6. Use fclose() function to cloase file. We discussed fcloase() function in file operations.

At the end of code, we will pass the statement when the file is imported so print this statement.(File successfully imported )
In simple words, you will get a message after CSV file data parse and saved in database table.

PHP code Here

<?php  
include("config.php");
if(isset($_POST["submit"]))
{
	
if($_FILES['file']['name'])
 {
$filename = explode(".", $_FILES['file']['name']);
if($filename[1] == 'csv')
{
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle))//handling csv file 
{
$item1 = mysqli_real_escape_string($connect, $data[0]);  
$item2 = mysqli_real_escape_string($connect, $data[1]);
//insert data from CSV file 
$query = "INSERT into ts_excel(ts_name, ts_email) values('$item1','$item2')";
mysqli_query($connect, $query);
}
fclose($handle);
echo "File sucessfully imported";
}
}
}
?>

Final Code

<?php  

$connect = mysqli_connect("localhost", "root", "", "ts_data");//databse connectivity
if(isset($_POST["submit"]))
{
	
if($_FILES['file']['name'])
{
$filename = explode(".", $_FILES['file']['name']);
if($filename[1] == 'csv')
{
$handle = fopen($_FILES['file']['tmp_name'], "r");
while($data = fgetcsv($handle))//handling csv file 
{
$item1 = mysqli_real_escape_string($connect, $data[0]);  
$item2 = mysqli_real_escape_string($connect, $data[1]);
//insert data from CSV file 
$query = "INSERT into ts_excel(ts_name, ts_email) values('$item1','$item2')";
mysqli_query($connect, $query);
}
fclose($handle);
echo "File sucessfully imported";
}
}
}
?>

<!DOCTYPE html>  
<html>  
<head>  
TechnoSmarter Tutorial
</head> 
<body> 

Select CSV File:





</body>  
</html>

Run

Please Share