Adrian Higgins Adrian Higgins -4 years ago 95
PHP Question

Automaitcally format 1000 identical csv files to import into MySQL?

I have about 1000 csv files that hold client information, they're all formatted the same way. I'm trying to move all of these files into a MySQL Database table, either all on one table, or one table per csv, whichever is more plausable.

Anyways, all I can find is information on doing it with a couple csv files, manually going into them and formatting them correctly, then importing into MySQL. There's no way I can do that with over 1000 files.

Any suggestions would be helpful.




Files came from google spreadsheets, the original hoster for the companys clients (each having their own spreadsheet for their data).

Moving over to an application I've built for the company.

Answer Source

This is a general version of my CSV importer. Create a table and replace 'your_table_name_here' with your table. Create all the columns and run this script in the folder that has all of your CSV files. It will do its best to match all columns with the data. I can add additional code that handles CSV files with different columns (automagically creates new columns if they do not exist in database), which I can add later tonight if you need it.

//Create database connection


$host = '127.0.0.1';
$db   = 'your_database';
$user = 'username';
$pass = 'password';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
    ];
$pdo = new PDO($dsn, $user, $pass, $opt);



//Run this script in the folder that has all the CSV files that you want to import


//Grab list of all CSV files
$files=glob('*.csv');


foreach($files as $file)
{

    $handle = fopen($file, "r");
    $cnt = 0;
    $is_first = true;
    echo "OPENING $file\n";

    if($handle)
        //Change this value. Usually delimiter is ',' but if your CSV file has something else, you will need to modify this value
        $delimiter='|';

    while (($csv = fgetcsv($handle, 5000, $delimiter)) !== false) {

        $d = array();

        if ($is_first) {
            $is_first = false;
            $header=$csv;

            $inserter=$pdo->prepare("replace into your_table_name_here(`".implode('`,`',$header)."`)
                values (:".implode(',:',$header).");");
        }
        else {
            $to_insert=array();
            foreach($csv as $key=>$val)
            {
                $to_insert[':'.$header[$key]]=$val;
            }
            $inserter->execute($to_insert);
        }
    }
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download