How to import CSV into MySQL using PHP

How to import CSV into MySQL using PHP

PHP is the most common website language to create dynamic and high-end website. Most of the applications that are built with PHP also have a database. In this article we will see how to import CSV into MySQL using PHP.

1. Create a MySQL table

In this article we will take an example of vendors table to implement the import CSV data into MySQL database. This table also involves enums to make a little complex scenario.

CREATE TABLE vendors (
   id int(11) NOT NULL,
   name varchar(150) NOT NULL,
   country varchar(75) DEFAULT NULL,
   vendor_type enum('Individual','Small Company','Medium Company','Big Company') NOT NULL DEFAULT 'Individual',
   state varchar(150) NOT NULL,
   email varchar(150) NOT NULL,
   phone varchar(45) NOT NULL,
   preferred enum('Yes','No') NOT NULL DEFAULT 'Yes',
   address varchar(256) NOT NULL,
   tax_id varchar(75) NOT NULL,
   notes text DEFAULT NULL,
   amount int(11) NOT NULL,
   is_active int(1) NOT NULL DEFAULT 1,
   created_at timestamp NOT NULL DEFAULT current_timestamp(),
   updated_at timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 ALTER TABLE vendors
   ADD PRIMARY KEY (id);
 ALTER TABLE vendors
   MODIFY id int(11) NOT NULL AUTO_INCREMENT;
 COMMIT;

We will use the vendors table to upload data in it using the CSV file.

2. Write some HTML

Create a file index.php and add the following code in it:

<form method="post" action="" enctype="multipart/form-data" name="upload_csv">
   <p><input type="file" name="file" class="form-control" accept=".csv" required></p>
   <input type="submit" value="IMPORT" name="Import" class="btn btn-success">
</form>

The accept=".csv" will tell to select the CSV files only. It is mandatory to add enctype="multipart/form-data" to accept the uploaded files in the form.

3. Create database connection

Create a file config.php and paste the following code. Don’t forget to change the credentials as per your preferences.

<?php
// DB credentials
   const DB_HOST = 'localhost';
   const DB_USER = 'root';
   const DB_PASSWORD = '';
   const DB_NAME = 'my_db';
   
// create DB connection 
   $connection = mysqli_connect(DB_HOST,DB_USER,DB_PASSWORD,DB_NAME);

// CSV with header (set it to false if you do not have headers in the CSV file)
   const ACCEPT_CSV_HEADERS = true;
?>

4. The action

Now we will create the processing part (main logic) for importing CSV file into MySQL using PHP.

Open the index.php file and add the following code above the HTML code:

<?php
// include the configuration file
include "config.php";

// check if form is submitted
if(isset($_POST["Import"])){
   $created_at = date('Y-m-d h:i:s'); // to keep track when record is created     
   $filename = $_FILES["file"]["tmp_name"]; // get temporary CSV file name
   
   // check if file is not empty
   if($_FILES["file"]["size"] > 0){  
      // open and read the file       
      $file = fopen($filename, "r");  

      // define variables for tracking/stats       
      $success = 0; $error = 0;

      // recursively iterate through each row of data
      while (($getData = fgetcsv($file, 10000, ",")) !== FALSE) {
         // fetch data based on indexes
         $name = $getData[0];
         $country = $getData[1];
         $vendor_type = $getData[2];
         $state = $getData[3];
         $email = $getData[4];
         $phone = $getData[5];
         $preferred = $getData[6];
         $address = $getData[7];
         $tax_id = $getData[8];
         $notes = $getData[9];
         
         // the amount is integer, set it to 0 as default if found empty in CSV
         if ($getData[10] != '' ) {                 
            $amount = $getData[10];             
         }else{                 
            $amount = 0;             
         }

         // create MySQL insert statement
         $sql = "INSERT INTO erp_vendors (name, country, vendor_type, state, email, phone, preferred, address, tax_id, notes, amount, is_active, created_at) values ('$name', '$country', '$vendor_type', '$state', '$email', '$phone', '$preferred', '$address', '$tax_id', '$notes', '$amount', 1, '$created_at'); ";
         
         // execute the above created SQL
         if(mysqli_query($connection, $sql)) { 
            $success++; // count inserted records
            echo $success." records has been imported successfully.";
         }
         else {
            $error++; // count rejected records

            // default 1 error for CSV headers
            if($error > 1 && ACCEPT_CSV_HEADERS == true){
               echo $error." records didn't imported because of format mismatch.";
            }
         }
      }

      // close the CSV file
      fclose($file);  
   }
}   
?>

Once the form is submitted, the uploaded CSV will be stored temporarily in the memory and the control will iterate through each single row of CSV file. We have used while loop which will iterate the records of imported CSV file.

You will see that we have created 2 variables $error and $success, these variables will track the number of inserted and rejected records in the database.

A sample CSV file is attached here

Enhancement in import CSV script

This article is a very basic implementation for importing CSV file data into MySQL using PHP. You can add more logics in your application according to the requirements. Some of the enhancements could be:

  • Import bulk data (thousands of records)
  • Discard duplicate records to keep unique data
  • Highlight the problem of the rejected records
  • Display summary of inserted and rejected records in a better way, etc.

Now, its up to the application usage.

Also see: Date range filters in server side jQuery dataTables using PHP and AJAX

Related Posts