PHP Read CSV file and insert 10 million record into MySQL table

 1. So first we are creating the CSV file with 10 million records in that.
Below code will help us to crate a CSV file with 10 million records. Later we will read this same file and insert in table.
Write10million.php

<?php
header("Content-Type: text/plain");
header('Content-Disposition: attachement; filename="jinujawad.csv"');
$pp = new NumberFormatter("en", NumberFormatter::SPELLOUT);
for ($x = 1; $x <= 10000000; $x++) {
echo $x.','."The number is: $x ".','.$pp->format($x)."\n";
//withoutnumber formatter
//echo $x.','."The number is: $x ".','.$x."\n";
}
?>
Copy

2. Now once the file is created. We will create a table inside MySQL with three columns inside to add the values from the CSV to the table.

CREATE TABLE samplelist (
sl_no INT(255) NOT NULL AUTO_INCREMENT,
sequence1 INT(255) NOT NULL DEFAULT '0',
word1 VARCHAR(10000) NOT NULL DEFAULT '0',
number1 VARCHAR(1000) NOT NULL DEFAULT '0',
time1 VARCHAR(1000) NOT NULL DEFAULT '0',
PRIMARY KEY (sl_no))
Copy

You can create a table with above line of code.

3. Now we have to connect with the MySQL table using PHP. You can use the below code to connect to MySQL database.
In the video I have shown how you can test the connectivity with DB.

<?php
echo 'Hello<br>';
$hostname='localhost';
$username='root';
$password='root';
$database='jinu';
$conn=new mysqli($hostname,$username,$password,$database);
if(!$conn){
die('Error In connection'.mysqli_connect_error());
}else{
echo 'Connection Success<br>';
}
mysqli_close($conn);
?>
Copy

4. First we will read the file created jinujawad.csv partially and show that in the php page.
We will connect the DB and execute insert command on the next step.
At the end of the page you can see the insert query also.

readandview.php

<?php
echo '<table border="1">';
$start_row = 1;
$insertquery =
    "INSERT INTO `samplelist` (`sequence1`, `word1`, `number1`, `time1`) VALUES ";
$subquery = "";
if (($csv_file = fopen("E:\\jinujawad10.csv", "r")) !== false) {
    while (($read_data = fgetcsv($csv_file, 1000, ",")) !== false) {
        $column_count = count($read_data);
        $subquery = $subquery . " (";
        echo "<tr>";
        $start_row++;
        for ($c = 0; $c < $column_count; $c++) {
            echo "<td>" . $read_data[$c] . "</td>";
            $subquery = $subquery . '\'' . $read_data[$c] . '\',';
        }
        echo "</tr>";
        $date = date("m/d/Y h:i:s a", time());
        $subquery = $subquery . '\'' . $date . '\'';
        $subquery = substr($subquery, 0, strlen($subquery) - 2);
        $subquery = $subquery . '\')' . " , ";
    }
    $insertquery = $insertquery . $subquery;
    $insertquery = substr($insertquery, 0, strlen($insertquery) - 2);
    echo $insertquery;
    fclose($csv_file);
}
echo "</table>";
?>
Copy

5. Now we will insert values to MySQL db with below code.
The below code will preview the data on the page and then insert the values into MySQL table.
As preview page is there, We cannot use this for records with greater number of entries like 10 Million.
If you do so, Please comment all the echo lines.
Only CSV with lesser number of entries can be shown in one PHP page.

readandinsert.php

<?php
include "db.php";
echo '<table border="1">';
$start_row = 1;
$insertquery =
    "INSERT INTO `samplelist` (`sequence1`, `word1`, `number1`, `time1`) VALUES ";
$subquery = "";
date_default_timezone_set("Asia/Qatar");
if (($csv_file = fopen("E:\\jinujawad10.csv", "r")) !== false) {
    $temp_count = 0;
    while (($read_data = fgetcsv($csv_file, 1000, ",")) !== false) {
        $column_count = count($read_data);
        $subquery = $subquery . " (";
        $temp_count++;
        echo "<tr>";
        $start_row++;
        for ($c = 0; $c < $column_count; $c++) {
           // echo "<td>" . $read_data[$c] . "</td>";
            $subquery = $subquery . '\'' . $read_data[$c] . '\',';
        }
        echo "</tr>";
        $date = date("m/d/Y h:i:s a", time());
        $subquery = $subquery . '\'' . $date . '\'';
        $subquery = substr($subquery, 0, strlen($subquery) - 2);
        $subquery = $subquery . '\')' . " , ";
        if ($temp_count % 10 == 0) {
            $insertquery = $insertquery . $subquery;
            $insertquery = substr($insertquery, 0, strlen($insertquery) - 2);
            // echo $insertquery.'<br><br><br>';
            if (mysqli_query($conn, $insertquery)) {
                //  echo "<br>New record created successfully - ".$temp_count;
                $insertquery =
                    "INSERT INTO `samplelist` (`sequence1`, `word1`, `number1`, `time1`) VALUES ";
                $subquery = "";
            } else {
                echo "Error: " . mysqli_error($conn);
            }
        }
    }
    // echo $insertquery;
    fclose($csv_file);
    mysqli_close($conn);
}
echo "</table>";
?>

Copy

Please note that there is a limitation in this method, I have already explained in the video.
In the line if ($temp_count % 10 == 0), If you give 10 lines per insert. It would insert lines with multiples of 10 only. It means if the CSV file has 112 records, It will read till 110 only.