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)