How to import large CSV file in MySQL millions of records.

This tutorial we are going to import large CSV file in MySQL. We have millions of records in files so we use batch import to insert data in MySQL.

• Create MySQL Table and Connection file.
• Change php.ini configuration
• Upload data in MySQL

Create MySQL Table

CREATE TABLE `csv_import` (
  `id` int(11) NOT NULL,
  `client` varchar(255) NOT NULL,
  `alernative` varchar(255) NOT NULL,
  `redirect` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Create Configuration file

<?php
$servername = "localhost";
$username = "root";
$password = "";
$db = "test";

$conn = mysqli_connect($servername, $username, $password, $db);

// Check The Connection
if (mysqli_connect_errno() ){
	echo "Database Connection Failed: " . mysqli_connect_error();
	die;
}

mysqli_set_charset($conn,"utf8");
?>

We will use MySQL Local query to import data, for this we are require to enable

mysqli.allow_local_infile = On

Open php.ini and remove ; from mysqli.allow_local_infile to work local query.

Read also : How to import large CSV file in MySQL millions of records.

Create HTML form and Upload Data

<?php
if(isset($_POST['importSubmit'])){
    
    // Allowed mime types
    $csvMimes = 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');
    
    // Validate whether selected file is a CSV file
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){
$fileName = $_FILES["file"]["name"];
if($_POST['db_name']=='order'){
$result1=mysqli_query($connection,"select count(*) count from csv_import");
				$r1=mysqli_fetch_array($result1);
				$count1=(int)$r1['count'];

				if(!mysqli_query($connection, '
					LOAD DATA LOCAL INFILE "'.$fileName.'"
						INTO TABLE csv_import
						FIELDS TERMINATED by \';\' ENCLOSED BY \'"\'
						LINES TERMINATED BY \'\n\'
						IGNORE 1 LINES')){
				
						$result2=mysqli_query($connection,"select count(*) count from csv_import");
						$r2=mysqli_fetch_array($result2);
						$count2=(int)$r2['count'];

						$count=$count2-$count1;
						if($count>0){
							
						}else{
							$error=1;	
						}
				}
}
}
}
?>
<form method="post" enctype="multipart/form-data" action=""> 
<input type="file" name="file" />						
<input type="hidden" name="db_name" value="import">											
<p> <input type="submit" value="Upload file"  name="importSubmit"  class="btn btn-lg btn-primary" /> </p>
</form>