How can I use LOAD DATA LOCAL INFILE for specific columns?

Question

I have a directory of text files, as the filename is the id, and the content text for this table:

CREATE TABLE texts
(
ID int(11) unsigned,
Added date,
Text MEDIUMTEXT,
PRIMARY KEY(ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

I use PHP glob function to find files to INSERT file by file (row by row) as

INSERT INTO texts (ID,Added,Text) VALUES ($id,CURDATE(),'$text')";

$id is the filename, and $text the entire content of the file.

How can I use LOAD DATA LOCAL INFILE to directly INSERT into the database and to avoid reading the large text into PHP variable?

Answer 1

Mysql can do following with user defined variables

 LOAD DATA LOCAL INFILE '/your_path/yourfule.csv'  
 INTO TABLE texts 
 FIELDS TERMINATED BY ','   
 LINES TERMINATED BY '\n'  
 ( @ID
 , @Text
 )
 SET `ID`   = @ID
   , `Added` = CURDATE()
   , `Text`     = @Text
 ;

Please check, if MySQL allows from that path to insert data.

And also you may have to change the options for INFILE

Answer 2

The following method should allow you to load all of the data into your DB while keeping only one file's contents in the PHP memory at a time.

You can write a new CSV in PHP with something similar to:

$newCsvPath = '/path/to/where/you/want/to/save/csv';
$newCsvFileName = 'newcsv.csv';
$newCsvFullFileName = $newCsvPath.$newCsvFileName;
$newCSV = fopen($newCsvFullFileName,'w+');
$dir = '/directory/for/your/text/files';
$fileArray = array_diff(scandir($dir), array('.', '..'));
$fileCount = count($fileArray);

if ($fileCount > 0) {
    foreach ($fileArray as $file) {
        $filename = $dir . "/" . $file;
        $f = fopen($filename,'r');
        $added = new Date();
        $added = date_format($added,'Y-m-d');
        $text = file_get_contents($f);
        $row = $file . "," . $added . "," . $text;
        fputcsv($newCSV,$row,",","\"");
        //you might want to remove the last "enclosure" parameter above depending on your preference
    }
}

Then, you can load the newly created CSV into the DB using this SQL (via whatever PHP MySQL tool you use):

LOAD DATA LOCAL INFILE $newCsvFullFileName
    IGNORE INTO TABLE temp_table
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
    IGNORE 1 LINES; 

Obviously you would need to modify the above LOAD DATA statement for your particular needs (i.e. how the fields are delimited/enclosed, if there are field names in the first row, etc.)

You also might have to modify the above $newCsvFullFileName by adding escaping backslashes for the SQL statement to work.

Answer 3

1. CREATE a separate table for the load
2. LOAD into that table
3. UPDATE main JOIN temp ON ... SET MAIN... WHERE ... -- That is, transfer what you need to over into the real table from the temp table.