How to Import CSV file data into MySQL using PHP


This examples shows how to import CSV(Comma Separated Value) file data into MySQL database using PHP. Here we are importing products name and price from CSV file.

products.sql

First We have to create 'products' table in 'db_sample' database.

CREATE TABLE  `products` (
  `pid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `pname` varchar(45) NOT NULL DEFAULT '',
  `price` double(10,2) NOT NULL DEFAULT '0.00',
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Sample CSV File Format to Import (fruits.csv)

Import CSV file data into MySQL using PHP

config.php

<?php 
  //MySQL Db Connection
  $host_name="localhost";
  $user_name="root";
  $password="";
  $db_name="db_sample";
  $con=mysqli_connect($host_name, $user_name, $password, $db_name);
?>

importcsv.php

<?php include "config.php"; ?>
<html>
  <head>
    <title>Import CSV file data into mysql using php</title>
  </head>
  <body>
    <form method='post' action='importcsv.php' enctype='multipart/form-data'>
      <p>
        <label>Browse CSV File : </label>
        <input type="file"  name="file" required class="form-control input-sm" accept=".csv">
      </p>
      <button type="submit" name="submit" class="btn btn-primary btn-sm "><i class="fa fa-save"></i> Submit</button>
    </form>
    <?php
      if(isset($_POST['submit'])){
        // Allowed types
        $csvTypes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
        
        $sql="INSERT INTO products(pname,price) VALUES ";
        $rows=Array();
        
        //Table
        $tableRows='';
        
        //Check selected file is csv
        if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvTypes)){
          
          //file upload
          if(is_uploaded_file($_FILES['file']['tmp_name'])){
            
            //Open CSV file with read only mode from tmp
            $file = fopen($_FILES['file']['tmp_name'], 'r');
            
            // Skip the first line
            fgetcsv($file);
            

            //Read CSV data Line by Line
            while(($row = fgetcsv($file)) !== FALSE){
              
              //Get row data
              $product=$row[0];
              $price=$row[1];  
              
              //add row values in array
              $rows[]="('{$product}','{$price}')";
              
              //add row values in table 
              $tableRows.="<tr><td>{$product}</td><td>{$price}</td></tr>";
            }
            
            // Close opened CSV file
            fclose($file);
            
            //Display CSV Data In HTML Table
            echo "<table style='border-collapse:collapse;width:250px;' border='1px' cellpadding='5px;'>".$tableRows."</table>";
            
            //Execute Query
            $sql.=implode(",",$rows);
            if($con->query($sql)){
              echo "<h4>Upload Successfully</h4>";
            }else{
              echo "<h4>Upload Failed</h4>";
            }
            
          }else{
            echo "<h4>Upload Failed</h4>";
          }
        }else{
          echo "<h4>Invalid File</h4>";
        }
      }
      ?>
  </body>
</html>

Output :

Import CSV file data into MySQL using PHP