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

• Googlebot: thanks for the answer but (1) ID is the filename, not inside the content. (2) the entire file content should be INSERTed as Text, not each line.
• nbk: as you didn'6t post the text of your file, i only can guess and give a leg up to change it to your liking by yourself
• Jörg Asmussen: The above example did not work for us unless we removed the ` in the SET area.
• nbk: that is very sttange mysql has no problem with that statement, it also runs in Mariadb, which version are you using?

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.

• Googlebot: There is no reference between the two tables. How do you JOIN?