How to Import CSV File into MySQL Database using PHP

In PHP  |  Last Updated on Fri, 01 Jul 2016

This post is about How to Import CSV File into MySQL Database using PHP. This is very important tutorial because every developers have to face this, which is import CSV file data into MySQL Database.

This is simple tutorial. You just need to understand below PHP script. And we will also discuss How to export MySQL Data to CSV file using PHP in another tutorial.

In many case we need to import CSV file data into MySQL database. But manually import data, which very difficult or hard working. So solution is programming . And from this tutorial you are going to learn how to import CSV file data into MySQL database directly using PHP. So lets Start..

Read More How to export MySQL Data to CSV file using PHP



 

index.php

This is home page of your project. Here you need to upload your CSV file. And submit. 
 

<form name="import_export_form" method="post" action="subImport.php" enctype="multipart/form-data"> 
    <p>Import file : <input type="file" name="excelfile[]" required/></p>
    <input name="sub" type="submit" value="Submit">
 </form>

 

subImport.php

This is very important part. First we have seen excel_reader.php which is read any CSV file. So you have to include these file. Next line you have to check any CSV file is set or not. If any file is set then using foreach loop you can get all the details of the CSV file.

Next you have to create a object, Using IOFactory.php, create your object and count how many rows inside the CSV file. We need to remove first row of the CSV file which is title. And others row data we have to insert into MySQL database. So using for loop we are going to insert all data into database accept first row. So, we set $i=2.
 

<?php

include('conn.php'); 
require_once 'Classes/excel_reader.php';
include 'Classes/PHPExcel/IOFactory.php';

if(isset($_FILES['excelfile'])) {
    $errors= array();
    foreach($_FILES['excelfile']['tmp_name'] as $key => $tmp_name ){
    
        $name = $_FILES['excelfile']['name'][$key];
        $file_size =$_FILES['excelfile']['size'][$key];
        $file1 =$_FILES['excelfile']['tmp_name'][$key];
        $str = explode(".",$name);
        $fname = $str[0];
        $exe = $str[1];
        
        // This is the file path to be uploaded.
    
        try {
            $objPHPExcel = PHPExcel_IOFactory::load($file1);
        } catch(Exception $e) {
            die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
        }
        
        
        $allDataInSheet = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
        $arrayCount = count($allDataInSheet);  // Here get total count of row in that Excel sheet
          
           for($i=2;$i<=$arrayCount;$i++)
             {
                $name = trim($allDataInSheet[$i]["A"]);
                $phone = trim($allDataInSheet[$i]["B"]);
                $email = trim($allDataInSheet[$i]["C"]);
               
                mysql_query("INSERT INTO `import_data` (`name`, `phone`, `email`) VALUES ('$name', '$phone', '$email')");
                          
           }  //END FOR  
                         
    }  // END FOREACH    

}
header('Location:index.php');

?>

 

Database

Create MySQL table lets say import.
 

CREATE TABLE IF NOT EXISTS `import`(
 `ID` int(255) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) NOT NULL,
 `Phone` varchar(50) NOT NULL,
 `email` varchar(50) NOT NULL,
 PRIMARY KEY (`ID`) )

conn.php

Database connectivity.  
 

<?php 
    error_reporting(E_ALL && ~E_NOTICE);

    $conn = mysql_connect("localhost", "root", "");
    if(!$conn) die("Failed to connect to database!");
    $status = mysql_select_db('import', $conn);
    if(!$status) die("Failed to select database!");
?>