Working with a large amount of really large files in PHP

Question

I have a bunch of text files that look something like this:

987654 Example 1
321987 Test 2
654321 Whatever 1

Each column represents a specific value (e.g., ID, timestamp, name, etc.). I'm trying to funnel all of this into a MySQL table. I need to read each line of these files individually and parse what part of each line should go into what column in the row.

Each file contains about 5,000,000 lines. I tried doing a test with just this:

$test = array();
for($i=1;$i<5000000;$i++){
  $test[] = '';
}

Even a blank array with that many elements maxes out my memory limit (64mb, it needs to stay at that too because my host doesn't allow anything larger), so turning the file into an array is impossible, and probably a little silly to consider in retrospect. I'm out of my element here because I've never had to do something like this before.

How can I do something like foreach line in file without using an array?

You do not need php. Try `LOAD DATE INFILE`. – sectus Nov 11 '13 at 04:57 — sectus, Nov 11 '13 at 04:57

Paulo Freitas · Accepted Answer · 2013-11-11T05:09:16.157

0

Check out if MySQL built-in LOAD DATA INFILE statement doesn't fit for you.

If not, you can use PHP SplFileObject class to iterate over your files lines without loading all them into memory. It have specific methods to parse lines like that, like SplFileObject::fgetcsv() and SplFileObject::fscanf(). In this case you might want to be using PDO to have a MySQL transaction to commit all insert statements at once to speed up the import proccess or rollback all them if something goes wrong.

edited Nov 11 '13 at 05:09

answered Nov 11 '13 at 04:58

Paulo Freitas

13,194
14
74
96

*It have specific methods to parse lines like that* -- for example? – sectus Nov 11 '13 at 05:05
[`SplFileObject::fgetcsv()`](http://www.php.net/splfileobject.fgetcsv.php) and [`SplFileObject::fscanf()`](http://www.php.net/splfileobject.fscanf.php)... – Paulo Freitas Nov 11 '13 at 05:07
@user1928928 You're welcome! I'm glad that I was able to help you! :) – Paulo Freitas Nov 11 '13 at 05:30

score 0 · Answer 2 · edited May 23 '17 at 12:29

I agree with sectus, do the LOAD DATA INFILE, and let MySQL do the dirty work.

Another way around if you absolutely need to use php would be to use some kind of 'parallel processing' this SO Question has more info on that.

If you decide to use the php approach you should read line by line the using fgets and then throwing each line chunk to a different thread to be processed. That way you don't eat your allowed memory and should get the work done in less time.

score 0 · Answer 3 · answered Nov 11 '13 at 05:10

0

For such large files, you need bigdump script if your files are properly delimited. It is easy to use and very effective and fast. I use it to import such big files to mysql. bigDump

answered Nov 11 '13 at 05:10

Rob

119
3

Working with a large amount of really large files in PHP

3 Answers3