Based on PHP read TXT file to the database to import massive data method

  • 2020-05-30 19:46:35
  • OfStack

There is one TXT file, which contains 100,000 records. The format is as follows:

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

.......................................... And then there's 100,000...

The requirement is to import into the database, and the structure of the data table is

word_id automatic increment
word [adductive#1 adducting#1 adducent#1] this one TXT record is to be converted into three SQL records
value = column 3 - column 4; If =0, this record skips without inserting the data table


<?php
    $file = 'words.txt';//10W Records of TXT The source file 
    $lines = file_get_contents($file); 
    ini_set('memory_limit', '-1');// Don't limit 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)
        {
            if($i>=20000&&$i<25000)// Batch imports to avoid failure 
            {
             $mm=explode(" ",$arr[4]);                
                 foreach($mm as $m)   // 【 adductive#1 adducting#1 adducent#1 】 this 1 a TXT The record is to be converted to 3 a SQL record                  {
                     $nn=explode("#",$m);
                     $word=$nn[0];
                     $sql.="(\"$word\",1,$senti_value,2),";// This is the place to notice  word It is possible to include single quotes (e.g jack's ), so we'll use double quotation marks word (note the escape)                        
                 }
            }
   $i++;
        }         
    }
    //echo $i;
    $sql=substr($sql,0,-1);// Get rid of the last 1 A comma 
    //echo $sql;
    file_put_contents('20000-25000.txt', $sql);  // Batch import database, 5000 article 1 Time, probably 40 Seconds; 1 Too many secondary imports max_execution_time Will not be enough, leading to failure      
?>

1. When massive data is imported, please pay attention to the 1 limit of PHP. You can temporarily adjust 1, otherwise you will report an error

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

2, PHP operates on TXT files

file_get_contents()

file_put_contents()

3, when massive import, it is best to import in batches, the probability of failure is less than 1

4. Before massive import, script 1 must be tested for many times and then used, for example, 100 pieces of data

5. After import, if mem_limit of PHP is not enough, the program still cannot run

(it is recommended to improve mem_limit by modifying php.ini instead of using a temporary statement)


Related articles: