CODEWITHSUNDEEP
phpmysql
good_evening
good_evening

Reputation: 21759

How to transfer big .txt file to MySQL?

I have a very big .txt file of millions of strings and it has separation sign.

So, what is the easiest way to transfer all those strings to database? Is it PHP fopen and taking every string with explode and inserting it into database?

FILE IS 2 GB.

Upvotes: 4

Views: 1608

Answers (3)

Petr Peller
Petr Peller

Reputation: 8846

It depends how much memory you have available on the machine. If you are not limited by RAM size, you could try to read whole file, explode it into array and generate insert query in one go.

You will probably need to generate multiple insert queries because of DB limitations (e.g. max_allowed_packet in mySQL)

If you don't have enough memory available you will need to read the file in more steps (by chunks of bytes). You can after generate some multiple insert queries.

fopen and fread functions might be helpful.

Upvotes: 0

Emanuele Minotto
Emanuele Minotto

Reputation: 415

$mysql = mysql_connect("localhost", "user", "password");
mysql_select_db("database", $mysql);
$filename = "file.txt";
$handle = fopen($filename, "rb");
$contents = fread($handle, filesize($filename));
fclose($handle);
$array = explode('[separator]', $contents);

foreach ($array as $line)
{
    mysql_query("INSERT INTO table VALUES (".mysql_real_escape_string($line).")", $mysql);
}
mysql_close($mysql);

Another solution, but boj' solution is better than this.

Upvotes: 1

boj
boj

Reputation: 11395

Use phpmyadmin to generate the command for you, ie.:

LOAD DATA LOCAL INFILE 'input_file' INTO TABLE `tablename` FIELDS TERMINATED BY '|' LINES TERMINATED BY '\n'

Quoted from http://vegdave.wordpress.com/2007/05/19/import-a-csv-file-to-mysql-via-phpmyadmin/

Upvotes: 8

Related Questions