Breaking
Coding Birds Online
Tag

Import CSV file data to the MySQL using PHP

Browsing
How to Do

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

By

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.

Keep Reading
By