If you want to learn how to upload excel(CSV) file to a database with PHP & MySQL in an easy way then you are in the right place. Also, read this how to Upload Multiple Images
Welcome to this blog, my name is Ankit and today I will tell how to do this. Saving an entry of data one by one to the database is the most difficult task in software or web application. Check this demo.
Why we need it?
Generally, We save data via FORM contains some fields like name, email, contact, gender or anything else.
But imagine if there are hundreds or thousands of records of person or students, then manually saving the data is much complicated. But don’t worry we have something very interesting in PHP.
Then Solution?
The solution to this problem is that, If you can combine that data in an excel sheet or CSV format, then we can write a code in such a way that every entry is in an excel sheet will be saved to a database.
Now that’s enough to understand. Let’s dive into code.
The process of how to upload excel(CSV)
- Create index.php. This will be a form to upload
- Create excel-script.php. This is the logic of the code
- Create a config.php. This is the connection file to the database
- Create a Common.php. This is class has the function to upload in the database
- Create a table named bird_excel_users.sql to the database that will save the data of the file
Now let’s create these files.
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>Upload Excel(CSV) file with PHP - CodingBirdsOnline.com</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
<link rel="icon" href="https://codingbirdsonline.com/wp-content/uploads/2019/12/cropped-coding-birds-favicon-2-1-192x192.png" type="image/x-icon">
</head>
<body>
<div class="jumbotron text-center">
<h1>Upload Excel(CSV) file with PHP</h1>
<p>https://codingbirdsonline.com</p>
</div>
<div class="container">
<form action="excel-script.php" method="post" enctype="multipart/form-data">
<div class="row">
<div class="col-md-4">
<div class="form-group">
<input type="file" name="excelDoc" id="excelDoc" class="form-control" />
</div>
</div>
<div class="col-md-4">
<input type="submit" name="uploadBtn" id="uploadBtn" value="Upload Excel" class="btn btn-success" />
</div>
</div>
</form>
</div>
</body>
</html>This index.php file has form action uses excel-script.php.
excel-script.php
<?php
include "config.php";
include_once "Common.php";
if($_FILES['excelDoc']['name']) {
$arrFileName = explode('.', $_FILES['excelDoc']['name']);
if ($arrFileName[1] == 'csv') {
$handle = fopen($_FILES['excelDoc']['tmp_name'], "r");
$count = 0;
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
$count++;
if ($count == 1) {
continue; // skip the heading header of sheet
}
$name = $connection->real_escape_string($data[0]);
$mobile = $connection->real_escape_string($data[1]);
$email = $connection->real_escape_string($data[2]);
$common = new Common();
$SheetUpload = $common->uploadData($connection,$name,$mobile,$email);
}
if ($SheetUpload){
echo "<script>alert('Excel file has been uploaded successfully !');window.location.href='index.php';</script>";
}
}
}
config.php
<?php
$connection = new mysqli("localhost","root","","codingbirds");
if (! $connection){
die("Error in connection".$connection->connect_error);
}Common.php
<?php
/**
* Created by PhpStorm.
* User: kc
* Date: 11/29/2018
* Time: 7:50 PM
*/
class Common
{
public function uploadData($connection,$name,$contact,$email)
{
$mainQuery = "INSERT INTO bird_excel_users SET name='$name',contact='$contact',email='$email'";
$result1 = $connection->query($mainQuery) or die("Error in main Query".$connection->error);
return $result1;
}
}
bird_excel_users.sql
If you face any errors in coding, then you can comment and I will surely help. And you can download the source code from here.
If you still face any problem, you can watch my video also.
22 Comments
Great technical information!
Great help there – it was really useful for me. Thanks.
I had to learn how to do this in my php class. It was a very valuable skill to learn.
Pretty cool resource for someone like me…
Wow this is very helpful and thorough! Thanks for the post 🙂
This is a great tutorial and very useful code. The sky is the limit with excel. Thank you for sharing
thaks for the tips
Great read. What I understand anyway..lol. learning!!
Great tech info! I don’t know much about coding, but your post taught me a lot. Thanks for sharing!
very helpful information here for me!
A very useful tutorial. Thank you for providing the coding.
Thanks for sharing this informative article! It’s good to know.
This tutorial is really helpful for a complete coding dumbass like me. Thanks for sharing!
It’s times when you think you know what to do and you realise you have no idea, that these blogs are gold dust! Great info!
Great tutorial! I am trying to learn as much as I can. Your post is very helpful.
I never faced any situations which needed this code but I’m sure this will be useful in future.
I adore coding skills! Keep it up! Learned while browsing here!
ooooooooooooh! How interesting. I have never heard of this 🤷♂️
Thank you! Your post is very resourceful…But I would not try to mess with my database because I am not confident in doing something in the database or MySQL.
Very clear set of steps – thank you! Well-written and the codes are helpful.
Clean code – Salute to You for this Dear…. Really Helpful
Thanks for sharing, but some error “The filename test.xls is not readable”