Method to read TXT files and import massive data into database based on PHP

Method to read TXT files and import massive data into database based on PHP_PHP tutorial

WBOY

Release： 2016-07-21 15:11:49

Original

861 people have browsed it

There is a TXT file containing 100,000 records in the following format:

Column 1 Column 2 Column 3 Column 4 Column 5
a 00003131 0 0 adductive#1 adducting#1 adducent#1
a 00003356 0 0 nascent #1
a 00003553 0 0 emerging #2 emergent#2
a 00003700 0.25 0 dissilient#1

……………………There are 100,000 following………………

The requirement is to import it into the database. The structure of the data table is

word_id Automatic increment
word [adductive#1 adducting#1 adducent#1] This TXT record needs to be converted into 3 SQL records
value = third column - fourth column; if =0, this record will be skipped and not inserted into the data table

Copy code The code is as follows :

$file = 'words.txt';//TXT source file of 10W records
$lines = file_get_contents($file);
ini_set('memory_limit', '-1');//Do not limit the Mem size, otherwise an error will be reported
$line=explode("n",$lines);
$i=0;
$ sql="INSERT INTO words_sentiment (word,senti_type,senti_value,word_type) VALUES ";

foreach($line as $key =>$li)
{
$arr=explode(" ",$li);
$senti_value=$arr[2]-$arr[3];
if($senti_value!=0)
i<25000)//Import in batches to avoid failure
adductive #1 adducting#1 adducent#1】This TXT record needs to be converted into 3 SQL records 🎜> $sql.="("$word",1,$senti_value,2),";//It should be noted here that word may contain single quotes (such as jack's), so we need to use double quotes to include it word (note the escaping) > //echo $i;
$sql=substr($sql,0, -1);//Remove the last comma
//echo $sql;
file_put_contents('20000-25000.txt', $sql); //Batch import database, 5000 entries at a time, takes about 40 seconds It looks like; importing too much max_execution_time at one time will not be enough, leading to failure
?>

1. When importing massive data, you should pay attention to some limitations of PHP. You can adjust it temporarily, otherwise an error will be reported

Allowed memory size of 33554432 bytes exhausted (tried to allocate 16 bytes)

2. PHP operates TXT files

file_get_contents()

file_put_contents()

3. When importing large amounts, it is best to import in batches to reduce the chance of failure

4. Before mass import, the script must be tested multiple times before use, such as testing with 100 pieces of data

5. After importing, if PHP’s mem_limit is still not enough, the program still cannot run

(It is recommended to increase mem_limit by modifying php.ini instead of using temporary statements)