Import excel data into database using PHP
Import excel data into database using PHP
Published Oct 25,2019 by Kailash Singh
0 Comment 2451 Views
In this tutorial, we are going to learn about how to Import excel (.csv) data into database using PHP.
Step 1 :- Create tables in Database.
In this step, we are going to create table in database.
CREATE TABLE `codingmantra`.`excel_detail` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(200) NOT NULL , `phone` VARCHAR(50) NOT NULL , `email` VARCHAR(200) NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;
Step 2 :- Create .csv file.
Step 3 :- Save as .csv format.
Step 4 :- Create design to upload file.
<!doctype html>
<html>
<head>
<title>Codingmantra</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/popper.js/1.14.7/umd/popper.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row" align="center" style="margin-top: 70px;">
<div class="col-sm-3"></div>
<div class="col-sm-6">
<h2>Import excel data into database using PHP</h2><br>
<!-- Success message print here -->
<?php echo $msg; ?>
<form method="post" action="" enctype='multipart/form-data'>
<input type='file' name='file' class="form-control" /><br>
<input type='submit' class="btn btn-primary" value='Upload Data' name='import'>
</form>
</div>
<div class="col-sm-3"></div>
</div>
</div>
</body>
</html>
Step 5 : Create PHP Code to import data form the csv file.
<?php
// connection of database
$con = mysqli_connect('localhost','root','','codingmantra');
$msg = '';
if(isset($_POST['import'])){
$filename = $_FILES["file"]["tmp_name"];
if($_FILES["file"]["size"] > 0)
{
$file = fopen($filename, "r");
while (($col = fgetcsv($file, 10000, ",")) !== FALSE)
{
// echo'<pre>'; print_r($col);
$insert = "INSERT INTO excel_detail (name,phone,email)values('".$col[0]."','".$col[1]."','".$col[2]."')";
mysqli_query($con,$insert);
}
$msg = '<p style="color: green;"> CSV Data inserted successfully</p>';
}
}
?>
Hope this will help our developers.
Because we believe Mantra means CodingMantra
Click to Join
