自动格式化1000个相同的csv文件导入MySQL？

Question

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.

---

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 1

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.

//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
";

    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);
        }
    }
}