DEV Community

Peter Perkins
Peter Perkins

Posted on • Edited on

2 2

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";
?>
Enter fullscreen mode Exit fullscreen mode

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more