Sunday, 29 December 2019

Importing Large CSV File into Multiple MySql Table using PHP



Currently, I have working on one my project, In which I want to import Large CSV file data into Two Mysql table. So, I have follow one by one insert data into multiple table. But when I have upload on server and try to import large data from CSV file but my script has been taken long time for import data from large csv file and get the error of timeout error on web page. So, I have come in trouble and try different way to import large data from CSV file by using PHP script but I have not get success.

So, I have start to search on internet and find solution of How to Import large CSV file into Multiple Mysql table. And on internet I have found "LOAD INFILE" Mysql command which will read data from CSV file and in one query execution it will insert data into Mysql table by using PHP script. But by using this command we can only import data from ine one table only and there is on internet I have not found any tutorial on How to import huge CSV file with 200000 rows of data into Multiple mysql table by using PHP script. So, I have try to write my own logic for solve the problem of importing large csv file data into multiple table by using PHP script.

In this tutorial, we have you can find the solution of importing of large csv file data into multiple mysql table by using PHP script. In this tutorial, we have use MYSQL LOAD DATA command, which will makes your task of importing large csv file into mysql table will do very easy and completed this task very fast. If you have working on any enterprise level application, and in that application you have to work with large data, then at the time CSV file has been used for take data from one application to another application. Because .CSV file is lighter than any other type of file extension, Mysql database has provide command for read csv file data and import into database in single query execution. So, Below you can find solution of importing large csv file data into multiple mysql table with PHP script.






Mysql Database


Run following SQL script, it will make customer table and order table in your local mysql database. In both table one table column like customer id is common.


--
-- Database: `testing`
--

-- --------------------------------------------------------

--
-- Table structure for table `customer_table`
--

CREATE TABLE `customer_table` (
  `customer_id` int(11) NOT NULL,
  `customer_first_name` varchar(200) NOT NULL,
  `customer_last_name` varchar(200) NOT NULL,
  `customer_email` varchar(300) NOT NULL,
  `customer_gender` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `order_table`
--

CREATE TABLE `order_table` (
  `order_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `product_name` varchar(200) NOT NULL,
  `product_price` double(10,2) NOT NULL,
  `order_date` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `customer_table`
--
ALTER TABLE `customer_table`
  ADD PRIMARY KEY (`customer_id`);

--
-- Indexes for table `order_table`
--
ALTER TABLE `order_table`
  ADD PRIMARY KEY (`order_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customer_table`
--
ALTER TABLE `customer_table`
  MODIFY `customer_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

--
-- AUTO_INCREMENT for table `order_table`
--
ALTER TABLE `order_table`
  MODIFY `order_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;




index.php


In this file, you can find front-end or client side code of this tutorial. Here we have make one HTML form for select CSV file from local computer, and by using Ajax script we have send selected CSV file to import.php server script by using FormData() object. For select only CSV file, here we have use accept=".csv" attribute.


<!DOCTYPE html>
<html>
 <head>
  <title>How to Import Large CSV File in Multiple Mysql table</title>  
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  
  <br />
  <br />
  <div class="container">
   <h1 align="center">How to Import Large CSV File in Multiple Mysql table</h1>
   <br />
   <div class="panel panel-default">
    <div class="panel-heading">
     <h3 class="panel-title">Import Large CSV File Data into Multiple Table</h3>
    </div>
      <div class="panel-body">
       <span id="message"></span>
       <form id="sample_form" method="POST" enctype="multipart/form-data" class="form-horizontal">
        <div class="form-group">
         <label class="col-md-4 control-label">Select CSV File</label>
         <input type="file" name="file" id="file" accept=".csv" />
        </div>
        <div class="form-group" align="center">
         <input type="hidden" name="hidden_field" value="1" />
         <input type="submit" name="import" id="import" class="btn btn-info" value="Import" />
        </div>
       </form>
      </div>
     </div>
  </div>
 </body>
</html>

<script>
 
 $(document).ready(function(){

  $('#sample_form').on('submit', function(event){
   $('#message').html('');
   event.preventDefault();
   $.ajax({
    url:"import.php",
    method:"POST",
    data: new FormData(this),
    dataType:"json",
    contentType:false,
    cache:false,
    processData:false,
    success:function(data)
    {
     $('#message').html('<div class="alert alert-success">'+data.success+'</div>');
     $('#sample_form')[0].reset();
    }
   })
  });

 });
</script>


import.php


This is PHP script file, which will received Ajax request with CSV file, and this script will read CSV file data and import that CSV file data into multiple mysql table by using Mysql LOAD FILE command.

Below you can find PHP script which will first make database connection, and at the time making database connection we have to set PDO::MYSQL_ATTR_LOCAL_INFILE this method with true value. Without using this method we cannon read CSV file from Mysql command. So, this is required at the time of making database connection in PHP PDO, if we want to import CSV large file by using MYSQL LOAD FILE command.

After this you can find query with LOAD DATA LOCAL INFILE command, it will read CSV file data from temporary location of selected CSV file. First it will import data into customer table, and after this it will import data into order table from same selected CSV file.


<?php

if(!empty($_FILES['file']['name']))
{
 $connect = new PDO("mysql:host=localhost;dbname=testing;", "root", "", array(
        PDO::MYSQL_ATTR_LOCAL_INFILE => true,
    ));

 $total_row = count(file($_FILES['file']['tmp_name']));

 $file_location = str_replace("\\", "/", $_FILES['file']['tmp_name']);

 $query_1 = '
 LOAD DATA LOCAL INFILE "'.$file_location.'" IGNORE 
 INTO TABLE customer_table 
 FIELDS TERMINATED BY "," 
 LINES TERMINATED BY "\r\n" 
 IGNORE 1 LINES 
 (@column1,@column2,@column3,@column4) 
 SET customer_first_name = @column1, customer_last_name = @column2,  customer_email = @column3, customer_gender = @column4
 ';

 $statement = $connect->prepare($query_1);

 $statement->execute();

 $query_2 = "
 SELECT MAX(customer_id) as customer_id FROM customer_table
 ";

 $statement = $connect->prepare($query_2);

 $statement->execute();

 $result = $statement->fetchAll();

 $customer_id = 0;

 foreach($result as $row)
 {
  $customer_id = $row['customer_id'];
 }

 $first_customer_id = $customer_id - $total_row;

 $first_customer_id = $first_customer_id + 1;

 $query_3 = 'SET @customer_id:='.$first_customer_id.'';

 $statement = $connect->prepare($query_3);

 $statement->execute();

 $query_4 = '
 LOAD DATA LOCAL INFILE "'.$file_location.'" IGNORE 
 INTO TABLE order_table 
 FIELDS TERMINATED BY "," 
 LINES TERMINATED BY "\r\n" 
 IGNORE 1 LINES 
 (@column1,@column2,@column3,@column4,@column5,@column6,@column7) 
 SET customer_id = @customer_id:=@customer_id+1, product_name = @column5,  product_price = @column6, order_date = @column7
 ';

 $statement = $connect->prepare($query_4);

 $statement->execute();

 $output = array(
  'success' => 'Total <b>'.$total_row.'</b> Data imported'
 );

 echo json_encode($output);
}

?>


So, from this tutorial you get the solution of How can we import large data from CSV file into Multiple mysql table. Above you can get the complete source code this tutorial, So, I hope you have understand this topic and learn the problem of importing large CSV file data into multiple mysql table.

6 comments:

  1. please can you give me csv file format or sample csv ?

    ReplyDelete
  2. how to insert data from odk collect into database

    ReplyDelete
  3. Thanks! Can You provide MOCK_DATA.csv file?

    ReplyDelete
  4. how can we upload csv file as well?

    ReplyDelete
  5. please help i cant upload data

    ReplyDelete
  6. D:/ashishxamp/tmp/php82C.tmp
    LOAD DATA LOCAL INFILE "D:/ashishxamp/tmp/php82C.tmp" IGNORE
    INTO TABLE temporary_completedata
    FIELDS TERMINATED BY ","
    LINES TERMINATED BY "\n"
    IGNORE 1 LINES
    (@column1,@column2,@column3,@column4,@column5,@column6,@column7,@column8,@column9,@column10,@column11,@column12,@column13,@column14,@column15,@column16,@column17,@column18,@column19,@column20,@column21,@column22,@column23)
    SET Date = @column1, AcademicYear = @column2, Session = @column3, AllotedCategory = @column4, AllotedCategory = @column4, AllotedCategory = @column4, AllotedCategory = @column4, AllotedCategory = @column4, VoucherType = @column5, VoucherNumber = @column5, RollNumber = @column7, Admno = @column8, Status = @column9, FeeCategory = @column10, Faculty = @column11, Program = @column12, Department = @column13, Batch = @column14, ReceiptNumber = @column15, FeeHead = @column16, DueAmount = @column17, PaidAmount = @column18, ConcessionAmount = @column19, ScholarshipAmount = @column20, ReverseConcessionAmount = @column21, WriteoffAmount = @column22,AdjustedAmount = @column23

    ReplyDelete