Importing multiple text files into different tables in mysql database

Question

I need to import bunch of text files(3098) into a database. Each file has to be in different table. My idea is to use a loop that creates a new table for each file. Do i have to use foreach in php to go through all the files or just mysql loop and what name of file am i supposed to put afterLOAD DATA LOCAL INFILE .Also wondering how could i create theese tables (maybe use a variable for their names). Any other ideas to do my task will be helpful.

Answer 1

Simply use php's glob() to iterate through all .txt files and then run MySQL's LOAD DATA INFILE command on each file. But first, create the destination table with appropriate data type columns in MySQL. Then run import code in PHP. As commented above, this approach assumes all .txt files are of same structure to all be imported into one database table.

MYSQL (one-time run in console or code)

CREATE TABLE destinationTable (
    ID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
    col1 int(11),
    col2 varchar(255),
    col3 decimal(10,2),
    col4 date,
    ...
);

PHP (using iterative loop; below assumes comma-separated files, adjust to actual delimiter)

// OPEN DATABASE CONNECTION
$dbh = new PDO("mysql:host=$host;dbname=$database",$username,$password);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

foreach (glob("/path/to/folder/*.txt") as $file) {

    $sql = "LOAD DATA INFILE '".$file."' 
            INTO TABLE destinationTable 
            FIELDS TERMINATED BY ',' 
            OPTIONALLY ENCLOSED BY '\"' 
            LINES TERMINATED BY '\r\n';";

    $dbh->query($sql);

}

// CLOSE CONNECTION
$dbh = null;