Import a CSV file into a MySQL database using PHP PDO (fast af) 🚀

Let say you're like me and need to get ~2Gib of records into a SQL database. Most of us are probably running a shared hosted server and have limitations regarding file upload size in phpMyAdmin. Honestly, after discovering this new method, I dont know that I'll ever upload big data another way.

Here is a great PHP Script from Stavros Skamagkis that uses PDO. It is pretty self explanatory. I've included a modified version below.

So how long does the import take? Well, in my case I have ~1,800,000 records with 30 columns and it takes seconds, like less than 5 😎.

Tips:

• Define fieldseparator, fieldEscapedBy, fieldEnclosedBy, and lineSeperator as variables (shown in first few lines). I span my wheels a little defining them inline which did not work (I believe because of PDO).

• CSV file location is relative to this files location.

• Header? Use IGNORE 1 LINES to skip the first line.

• How to run? I created a Development Button and linked onClick() that connects via Ajax to the resource. I'm know there are easier methods, I just already had it setup. Nevertheless, this works.
  

<?php
//  Code Source: https://gist.github.com/stavrossk/0f513ccbfe7882870ab1

$databasehost = "localhost";
$databasename = "YOUR_DATABASE_NAME";

$databasetable = "YOUR_DATABASE_TABLE";

$databaseusername = "YOUR_DATABASE_USERNAME";
$databasepassword = 'YOUR_DATABASE_PASSWORD';

$fieldSeparator = ";";
$fieldEscapedBy = "";
$fieldEnclosedBy = '"';
$lineSeparator = "\n";

$csvfile = "../FILE_TO_IMPORT.csv";


if (!file_exists($csvfile)) {
    error_log('File does NOT exist!');
    die("File not found. Make sure you specified the correct path.");
}

try {

    $pdo = new PDO(
        "mysql:host=$databasehost;dbname=$databasename",
        $databaseusername,
        $databasepassword,
        array(
            PDO::MYSQL_ATTR_LOCAL_INFILE => true,
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
        )
    );
} catch (PDOException $e) {
    error_log('database connection failed!');
    die("database connection failed: " . $e->getMessage());
}

$affectedRows = $pdo->exec(
    "LOAD DATA LOCAL INFILE "
        . $pdo->quote($csvfile)
        . " INTO TABLE `$databasetable` FIELDS TERMINATED BY "
        . $pdo->quote($fieldSeparator)
        . " ESCAPED BY "
        . $pdo->quote($fieldEscapedBy)
        . " ENCLOSED BY "
        . $pdo->quote($fieldEnclosedBy)
        . " LINES TERMINATED BY "
        . $pdo->quote($lineSeparator)
        . " IGNORE 1 LINES "
);

echo "Loaded a total of $affectedRows records from this csv file.\n";
?>