How do I import a large CSV file to MySQL via PHP?

Question

I've followed multiple tutorials on how to get this done and I get the message "Imported Successfully" or similar message depending on the script I've used, but I have not had any luck doing so. My CSV consists of 26 columns and about 18,000 rows. I've set up the PHPMYADMIN database and table and I have tried the same script with a small load of data consisting of only 4 columns, and that one worked, I've attempted to figure out how to get the others to work, but have had 0 luck.

<?php
$conn = mysqli_connect("localhost", "DB_USERNAME", "DB_PASSWORD", "DB_NAME");

if (isset($_POST["import"])) {

    $fileName = $_FILES["file"]["tmp_name"];

    if ($_FILES["file"]["size"] > 0) {

        $file = fopen($fileName, "r");

        while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
            $sqlInsert = "INSERT into TABLE_NAME (COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10,COL11,COL12,COL13,COL14,COL15,COL16,COL17,COL18,COL19,COL20,COL21,COL22,COL23,COL24,COL25,COL26)
                   values ('" . $column[0] . "','" . $column[1] . "','" . $column[2] . "','" . $column[3] . "','" . $column[4] . "','" . $column[5] . "','" . $column[6] . "','" . $column[7] . "','" . $column[8] . "','" . $column[9] . "','" . $column[10] . "','" . $column[11] . "','" . $column[12] . "','" . $column[13] . "','" . $column[14] . "','" . $column[15] . "','" . $column[16] . "','" . $column[17] . "','" . $column[18] . "','" . $column[19] . "','" . $column[20] . "','" . $column[21] . "','" . $column[22] . "','" . $column[23] . "','" . $column[24] . "','" . $column[25] . "')";
            $result = mysqli_query($conn, $sqlInsert);

            if (! empty($result)) {
                $type = "success";
                $message = "CSV Data Imported into the Database";
            } else {
                $type = "error";
                $message = "Problem in Importing CSV Data";
            }
        }
    }
}
?>
<!DOCTYPE html>
<html>

<head>
<script src="jquery-3.2.1.min.js"></script>

<style>
body {
    font-family: Arial;
    width: 550px;
}

.outer-scontainer {
    background: #F0F0F0;
    border: #e0dfdf 1px solid;
    padding: 20px;
    border-radius: 2px;
}

.input-row {
    margin-top: 0px;
    margin-bottom: 20px;
}

.btn-submit {
    background: #333;
    border: #1d1d1d 1px solid;
    color: #f0f0f0;
    font-size: 0.9em;
    width: 100px;
    border-radius: 2px;
    cursor: pointer;
}

.outer-scontainer table {
    border-collapse: collapse;
    width: 100%;
}

.outer-scontainer th {
    border: 1px solid #dddddd;
    padding: 8px;
    text-align: left;
}

.outer-scontainer td {
    border: 1px solid #dddddd;
    padding: 8px;
    text-align: left;
}

#response {
    padding: 10px;
    margin-bottom: 10px;
    border-radius: 2px;
    display:none;
}

.success {
    background: #c7efd9;
    border: #bbe2cd 1px solid;
}

.error {
    background: #fbcfcf;
    border: #f3c6c7 1px solid;
}

div#response.display-block {
    display: block;
}
</style>
<script type="text/javascript">
$(document).ready(function() {
    $("#frmCSVImport").on("submit", function () {

        $("#response").attr("class", "");
        $("#response").html("");
        var fileType = ".csv";
        var regex = new RegExp("([a-zA-Z0-9\s_\\.\-:])+(" + fileType + ")$");
        if (!regex.test($("#file").val().toLowerCase())) {
                $("#response").addClass("error");
                $("#response").addClass("display-block");
            $("#response").html("Invalid File. Upload : <b>" + fileType + "</b> Files.");
            return false;
        }
        return true;
    });
});
</script>
</head>

<body>
    <h2>Import CSV file into Mysql using PHP</h2>

    <div id="response" class="<?php if(!empty($type)) { echo $type . " display-block"; } ?>"><?php if(!empty($message)) { echo $message; } ?></div>
    <div class="outer-scontainer">
        <div class="row">

            <form class="form-horizontal" action="" method="post"
                name="frmCSVImport" id="frmCSVImport" enctype="multipart/form-data">
                <div class="input-row">
                    <label class="col-md-4 control-label">Choose CSV
                        File</label> <input type="file" name="file"
                        id="file" accept=".csv">
                    <button type="submit" id="submit" name="import"
                        class="btn-submit">Import</button>
                    <br />

                </div>

            </form>

        </div>
               <?php
            $sqlSelect = "SELECT * FROM TABLE_NAME";
            $result = mysqli_query($conn, $sqlSelect);

            if (mysqli_num_rows($result) > 0) {
                ?>
            <table id='userTable'>
<?php

                while ($row = mysqli_fetch_array($result)) {
                    ?>

                <tbody>
                <tr>
                    <td><?php  echo $row['id']; ?></td>
                    <td><?php  echo $row['firstname']; ?></td>
                    <td><?php  echo $row['lastname']; ?></td>
                    <td><?php  echo $row['number']; ?></td>
                </tr>
                    <?php
                }
                ?>
                </tbody>
        </table>
        <?php } ?>
    </div>

</body>

</html>

So what's the problem? (Aside from your failure to use a prepared statement.) — miken32, Oct 24 '18 at 17:57
I am assuming that it times out, or it looks like it goes through, but it actually doesnt because on the MySQL side the table is still empty. — hiter202, Oct 24 '18 at 17:59
Not sure what you mean, but there is no error_log generated in the directory root. — hiter202, Oct 24 '18 at 18:50

score 1 · Answer 1 · answered Oct 24 '18 at 18:15

1

What you've got is hugely inefficient and very insecure. As soon as you have an apostrophe in your data the whole thing is going to explode, for example.

Prepared statements protect against data injection, and also greatly reduce the overhead needed to perform repeated queries. This is possible with mysqli, but PDO is much less verbose and more modern – its use should be preferred in new code.

Something like this should be a good start, but you'll want to do some proper error checking using try/catch. It assumes that the number of columns in the CSV is equal to the number of columns you want to insert into the database.

$conn = new \PDO("mysql:host=localhost;dbname=DB_NAME", "DB_USERNAME", "DB_PASSWORD");

if (isset($_POST["import"])) {

    $fileName = $_FILES["file"]["tmp_name"];
    if ($_FILES["file"]["size"] > 0) {
        $file = fopen($fileName, "r");

        $sql = "INSERT INTO TABLE_NAME (COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9, COL10, COL11, COL12, COL13, COL14, COL15, COL16, COL17, COL18,COL19, COL20, COL21, COL22, COL23, COL24, COL25, COL26)
                VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"
        $stmt = $conn->prepare($sql);
        while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
            $result = $stmt->execute($column);
        }
    }
}

answered Oct 24 '18 at 18:15

miken32

42,008
16
111
154

So what would I replace the ?'s with? – hiter202 Oct 24 '18 at 18:25
Read the links in my answer. – miken32 Oct 24 '18 at 18:29
The first link is informative for sure but still not getting it. I am not that techsavvy in this realm. The second link with the comic totally throw me off, dont get it at all. Pardon my ignorance – hiter202 Oct 24 '18 at 18:33
They're placeholders, you don't replace them with anything. There are numerous examples showing the use of these placeholders in the PHP documentation I linked to. Don't use this code unless you understand what it's doing. – miken32 Oct 24 '18 at 18:36
miken32, I've tried to replace my script with yours. Got an error, added a ; at the end of the VALUES section and that removed the error. Unfortunately same exact thing happens when using your script vs using mine, it comes back to the page as if it completed it successfully, but when I check in the table, there are no rows in there. – hiter202 Oct 24 '18 at 18:57
That's where the error checking I mentioned comes in. Outputting a number for each insert might help track down if it's a particular record that's a problem. There's not a whole lot else I can suggest – miken32 Oct 24 '18 at 18:59
I've already thought of that before posting on here so I attempted to upload a CSV with 2 rows and 26 columns, and each has "abc" in it, so 52 abc's total (2 rows x 26). Same behavior and did not upload to the the table in the DB – hiter202 Oct 24 '18 at 19:12

How do I import a large CSV file to MySQL via PHP?

1 Answers1