How to Do

how to upload excel(CSV) file to a database with PHP& MySQL

Pinterest LinkedIn Tumblr

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

ATTACHMENT DETAILS how-to-upload-excelCSV-file-to-a-database-with-PHP-MySQL-table-coding-birds-online.png

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.

I am a professional Web / Software developer, B.Tech CSE, who has been working in PHP & CodeIgniter and WordPress for more than 2+ years and know it inside and out, including the creation of Web Apps, WordPress websites from scratch and their customization.

21 Comments

  1. This is a great tutorial and very useful code. The sky is the limit with excel. Thank you for sharing

  2. 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!

  3. Suanlian Tangpua Reply

    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.

Write A Comment