Asynchronus-PHP Upload Huge csv data into MYSQL

Question

I tried to upload around 20k rows CSV into SQL using async method referring to this post :

How to import a huge CSV file with 200,00 rows to MySQL (asynchronous and fast)?

The script already running and success when I upload 10000 data, but when I checked into the database, I found that only half rows (5000 rows) were Inserted. I've tried to change the $batchsize from 1000 to 100, only 9400rows inserted instead of supposedly 10000 rows

here's my current code :

index.php :

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <title>csv upload</title>
</head>
<body>
    <form action="upload.php" method="post" enctype="multipart/form-data">
        <input type="file" name="csv" value="" />
        <input type="submit" name="submit" value="Save" />
    </form>
</body>
</html>

upload.php :

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/1.11.1/jquery.js"></script>

<script>
//Declaration of function that will insert data into database
 function senddata(filename){
        var file = filename;
        $.ajax({
            type: "POST",
            url: "senddata.php",
            data: {file},
            async: true,
            success: function(html){
                $("#result").html(html);
            }
        })
        }
</script>

<?php

$batchsize = 100; //split huge CSV file by 1,000, you can modify this based on your needs

if($_FILES['csv']['error'] == 0){

    $name = $_FILES['csv']['name'];
    $ext = explode(".", $name);
    $ext = $ext[1];
    $tmpName = $_FILES['csv']['tmp_name'];

    if($ext === 'csv'){ //check if uploaded file is of CSV format

        if(($handle = fopen($tmpName, 'r')) !== FALSE) {

            set_time_limit(0);
            $row = 0;

            while(($data = fgetcsv($handle)) !== FALSE) {

                //splitting of CSV file :
                if ($row % $batchsize == 0):

                    $file = fopen("minpoints$row.csv","w");

                endif;

                $csv[$row]['col1'] = $data[0];
                $csv[$row]['col2'] = $data[1];
                $min = $data[0];
                $points = $data[1];
                $json = "'$min', '$points'";
                fwrite($file,$json.PHP_EOL);

                //sending the splitted CSV files, batch by batch...
                if ($row % $batchsize == 0):

                    echo "<script> senddata('minpoints$row.csv'); </script>";

                endif;
                $row++;

            }

            fclose($file);
            fclose($handle);

        }
    } else {

        echo "Only CSV files are allowed.";

    }
    //alert once done.
    echo "<script> alert('CSV imported!') </script>";
} ?>

senddata.php :

<?php
include('config.php');
$data = $_POST['file'];
$handle = fopen($data, "r");
$test = file_get_contents($data);
// print_r($test);die;
if ($handle) {
    $counter = 0;
    //instead of executing query one by one,
    //let us prepare 1 SQL query that will insert all values from the batch
    $sql ="INSERT INTO table_test(name,contact_number) VALUES ";
    while (($line = fgets($handle)) !== false) {
      $sql .= "($line),";
      $counter++;
    }
    $sql = substr($sql, 0, strlen($sql) - 1);
     if ($conn->query($sql) === TRUE) {
    } else {
    }
    fclose($handle);
} else {  
} 
//unlink CSV file once already imported to DB to clear directory
unlink($data);
?>

My Goals are :

Upload huge csv data asynchronusly with complete data
To understand this codes : --> fwrite($file,$json.PHP_EOL); and --> senddata('minpoints$row.csv'); ;

How big is your file, and what's the maximum file size given by upload_max_filesize in php.ini? — Adrian Wiik, Jan 14 '20 at 08:46
Maybe the table `table_test` has an **NOT NULL** column and the `csv` has **NULL** at some case and that's why is not inserted? — Roy Bogado, Jan 14 '20 at 10:33
@DannyThunder do you mean error while uploading ? I haven't got any error msg during upload process, — owf, Jan 14 '20 at 11:22
@Roy thanks man !!! its the problem, the csv containing NULL, after i cleaned it up it's successfully uploaded ! problem solved, but the csv might contain NULL according to the user, how to anticipate it ? do i have to change the table_test structure table ? — owf, Jan 15 '20 at 01:49
@owf Yes, make `name` and `contact_number` **NULL**, with `ALTER TABLE table_test MODIFY name your-type(your-number);` _Columns are nullable by default. As long as the column is not declared UNIQUE or NOT NULL, there shouldn't be any problems._ — Roy Bogado, Jan 15 '20 at 06:33
@Roy thank you, after i changed the structure table, it works perfectly. Anyway do you have an idea how to skip the first row (header) ? I've tried to use IF the $counter > 0 on senddata.php , but it skips every first counter of each batch — owf, Jan 15 '20 at 07:44

Roy Bogado · Answer 1 · 2020-01-15T12:27:13.600

The table table_test has an NOT NULL column and the csv has NULL values at some case and that's why is not inserted.

You can make name and contact_number NULL, with
ALTER TABLE table_test MODIFY name your-type(your-number);

Columns are nullable by default. As long as the column is not declared UNIQUE or NOT NULL, there shouldn't be any problems.

@owf: - thank you, after i changed the structure table, it works perfectly. Anyway do you have an idea how to skip the first row (header) ? I've tried to use IF the $counter > 0 on senddata.php , but it skips every first counter of each batch

R:
First for all, you need to $flag the first parsed csv to know that it comes with the first row to be skipped.
Add a variable to the ajax that check if is the first csv registered.
So, en ajax.php you know if the csv is the first one or not, based in this method, you can skip the first row of the first batched csv

yes, i've tried that way while (($line = fgets($handle)) !== false) { if($counter>0){ //should work here. $sql .= "($line),"; } $counter++; } but, it will skips every first row of each batch — owf, Jan 15 '20 at 08:58

score 0 · Accepted Answer · answered Nov 03 '21 at 07:54

using this you can skip the just first row of CSV file (header of CSV file) . try that

 while (($line = fgets($handle)) !== false) {
            if ($_POST['file'] == 'minpoints0.csv' && $counter > 0) {
                $sql .= "('$line'),";
            }
            $counter++;
        }

Asynchronus-PHP Upload Huge csv data into MYSQL

2 Answers2