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
– miken32, Commented Oct 24, 2018 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
– hiter202, Commented Oct 24, 2018 at 17:59
Not sure what you mean, but there is no error_log generated in the directory root. — hiter202
– hiter202, Commented Oct 24, 2018 at 18:50

miken32 · Accepted Answer · 2018-10-24 18:15:27Z

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, 2018 at 18:15

miken32

42.5k16 gold badges127 silver badges177 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

hiter202 Over a year ago

So what would I replace the ?'s with?

miken32 Over a year ago

Read the links in my answer.

hiter202 Over a year ago

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

miken32 Over a year ago

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.

hiter202 Over a year ago

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.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related