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, andlineSeperatoras 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 LINESto 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";
?>
Top comments (0)