Import CSV to MySQL using a PHP Script?

Question

I have a CSV file that I'm wanting to import into a database, and then export the same data with omitted columns. How would I go about doing this? Any suggestions?

You should probably read the documentation on [`LOAD DATA INFILE`](http://dev.mysql.com/doc/refman/5.6/en/load-data.html). — tadman, Feb 24 '14 at 15:43
if the database is just a temporary dumping ground, it's not necessary. `fgetcsv()`, `array_splice()` and `fputcsv()` should be all you need. — Marc B, Feb 24 '14 at 15:45
I suggest to use http://www.ozerov.de/bigdump/ it's a lot faster. — Karim Lahlou, Feb 24 '14 at 15:48
I have suggestion write a code, try it, and post a real question once you hit a problem. For design questions go to: http://programmers.stackexchange.com/ — FazoM, Feb 24 '14 at 15:58

score 0 · Answer 1 · answered Feb 24 '14 at 15:48

<?php
    $databasehost = "localhost"; 
    $databasename = "test"; 
    $databasetable = "sample"; 
    $databaseusername="test"; 
    $databasepassword = ""; 
    $fieldseparator = ","; 
    $lineseparator = "\n";
    $csvfile = "filename.csv";

        if(!file_exists($csvfile)) {
            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) {
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)."
      LINES TERMINATED BY ".$pdo->quote($lineseparator));

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

?>

Import CSV to MySQL using a PHP Script?

1 Answers1