How to parse and insert data in a database

Question

I have a big txt file (40mb) that contains some strings, the file is like this:

AN,Aixas|AN,Aixirivall|AN,Aixovall|AN,Andorra la Vella|AN,Ansalonga|AN,Anyos|AN,Arans|

I have a key (two letters) and a value (the city), so i have a problem, when i try to split it in PHP with this function, and when i try to insert data in my database.

The function that i use in php is:

$file = explode("|", file_get_contents('city.txt'));
$city = explode(",", $file);

foreach ($city as $key => $value)
mysqli_query($mysqli, "INSERT INTO city(name_city, id_city) VALUES ('$key', '$value')");

An error occured:

[29-Apr-2016 14:33:09 Europe/Berlin] PHP Fatal error:  Allowed memory size of 268435456 bytes exhausted (tried to allocate 32 bytes) in /home/mypath on line 6

I think that my file is more big to be processed, how i can parse and insert my data in my db ?

Use LOAD DATA INFILE http://dev.mysql.com/doc/refman/5.7/en/load-data.html — Funk Forty Niner, Apr 29 '16 at 12:52
Or you can create file with insert data https://dev.mysql.com/doc/refman/5.7/en/mysql-batch-commands.html — Ivan, Apr 29 '16 at 13:06

score 0 · Accepted Answer · answered Apr 29 '16 at 13:01

I would recommend you use mysql load data infile:

LOAD DATA LOCAL INFILE '".$file_path."' INTO TABLE your_table
                FIELDS TERMINATED BY '|' 
                LINES TERMINATED BY '\n'
                IGNORE n LINES";

Where $file_path is the path of your text file, your_table is the table name and n is the number of lines you wish to ignore from the beginning of text file.

How to parse and insert data in a database

1 Answers1