Process very big csv file without timeout and memory error

Question

At the moment I'm writing an import script for a very big CSV file. The Problem is most times it stops after a while because of an timeout or it throws an memory error.

My Idea was now to parse the CSV file in "100 lines" steps and after 100 lines recall the script automatically. I tried to achieve this with header (location ...) and pass the current line with get but it didn't work out as I want to.

Is there a better way to this or does someone have an idea how to get rid of the memory error and the timeout?

How big is your CSV file? You need to import this in a database? — Book Of Zeus, Sep 06 '11 at 11:04
check out my answer at http://stackoverflow.com/a/22744300/2037323 which includes some comparisons also. — Raza Ahmed, Mar 30 '14 at 13:05

feeela · Accepted Answer · 2012-11-22T09:25:10.750

I've used fgetcsv to read a 120MB csv in a stream-wise-manner (is that correct english?). That reads in line by line and then I've inserted every line into a database. That way only one line is hold in memory on each iteration. The script still needed 20 min. to run. Maybe I try Python next time… Don't try to load a huge csv-file into an array, that really would consume a lot of memory.

// WDI_GDF_Data.csv (120.4MB) are the World Bank collection of development indicators:
// http://data.worldbank.org/data-catalog/world-development-indicators
if(($handle = fopen('WDI_GDF_Data.csv', 'r')) !== false)
{
    // get the first row, which contains the column-titles (if necessary)
    $header = fgetcsv($handle);

    // loop through the file line-by-line
    while(($data = fgetcsv($handle)) !== false)
    {
        // resort/rewrite data and insert into DB here
        // try to use conditions sparingly here, as those will cause slow-performance

        // I don't know if this is really necessary, but it couldn't harm;
        // see also: http://php.net/manual/en/features.gc.php
        unset($data);
    }
    fclose($handle);
}

I have a 200MB of CSV with 1344953 rows and I am reading, filtering and writing into different files in 25 seconds and still in my class someone has did it in 12 seconds! Cool, isn't it! — Nobody, Apr 02 '22 at 14:59

score 18 · Answer 2 · edited Sep 28 '12 at 08:27

18

I find uploading the file and inserting using mysql's LOAD DATA LOCAL query a fast solution eg:

    $sql = "LOAD DATA LOCAL INFILE '/path/to/file.csv' 
        REPLACE INTO TABLE table_name FIELDS TERMINATED BY ',' 
        ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES";
    $result = $mysqli->query($sql);

edited Sep 28 '12 at 08:27

Fluffeh

33,228
16
67
80

answered Aug 14 '12 at 15:57

Craigo

183
1
5

Wow I went from 5 minutes+ to import a 64000 records csv to less then 5 seconds. This is fantastic! – Iznogood Feb 26 '15 at 19:10

2ndkauboy · Answer 3 · 2011-09-06T11:13:23.107

13

If you don't care about how long it takes and how much memory it needs, you can simply increase the values for this script. Just add the following lines to the top of your script:

ini_set('memory_limit', '512M');
ini_set('max_execution_time', '180');

With the function memory_get_usage() you can find out how much memory your script needs to find a good value for the memory_limit.

You might also want to have a look at fgets() which allows you to read a file line by line. I am not sure if that takes less memory, but I really think this will work. But even in this case you have to increase the max_execution_time to a higher value.

edited Sep 06 '11 at 11:13

answered Sep 06 '11 at 11:05

2ndkauboy

9,302
3
31
65

1

This is of course only a good approach if you know that the file is always of the same size. – Bas Slagter Sep 06 '11 at 11:09
3

If you know it's not larger than a speficic size, it also works. – 2ndkauboy Sep 06 '11 at 11:11

rawdesk.be · Answer 4 · 2018-05-24T07:56:19.757

There seems to be an enormous difference between fgetcsv() and fgets() when it comes to memory consumption. A simple CSV with only one column passed my 512M memory limit for just 50000 records with fgetcsv() and took 8 minutes to report that.

With fgets() it took only 3 minutes to successfully process 649175 records, and my local server wasn't even gasping for additional air..

So my advice is to use fgets() if the number of columns in your csv is limited. In my case fgets() returned directly the string inside column 1. For more then one column, you might use explode() in a disposable array which you unset() after each record operation. Thumbed up answer 3 @ndkauboy

score -3 · Answer 5 · answered Sep 06 '11 at 11:19

-3

Oh. Just make this script called as CLI, not via silly web interface. So, no execution time limit will affect it.
And do not keep parsed results forever but write them down immediately - so, you won't be affected by memory limit either.

answered Sep 06 '11 at 11:19

Your Common Sense

156,878
40
214
345

Process very big csv file without timeout and memory error

5 Answers5

Linked

Related