Ask Question Forum:

Model Library:2025-02-08 Updated:A.I. model including DeepSeek is online for auto reply question page

Show

ASK RECENT

get specific line in large csv file using php

Asked by Evan Cutler

at 2024-11-23 14:28:09

Point:500 Replies:18 POST_ID:828821USER_ID:11728

Topic:

PHP Scripting Language;;Linux

Greetings,
I have a file.
It is standard with <email>,<tag>
There are over a million lines in the file like that.

<email>,<tag>
<email>,<tag>
..continued.

no headers.
If I have a random value, say 124535,

How can I get the 124535th line in the file without reading the whole thing into memory?
I can call local commands, but the execution order must be in PHP.
Thanks

Expert: Ray Paseur replied at 2024-11-27 12:50:38

Dave: It was still a useful contribution to the conversation. Happy Thanksgiving, ~Ray

Expert: Dave Baldwin replied at 2024-11-27 12:47:43

If this gets re-opened for any reason, I shouldn't get any points for that post because, as Ray pointed out, I was wrong.

Expert: Ray Paseur replied at 2024-11-27 12:37:06

Thanks for the points and thanks for using EE, ~Ray

Author: Evan Cutler replied at 2024-11-27 12:13:24

Hey, thanks guys. This got me where I was going.
Appreciate it so much.

Expert: Dave Baldwin replied at 2024-11-24 11:24:43

Oh... Ok.

Author: Evan Cutler replied at 2024-11-24 11:04:01

1.7 seconds is Ok. It's having to read for 2 minutes that's the problem. I am trying it now.
Thanks

Expert: Ray Paseur replied at 2024-11-24 11:03:16

Dave: I read the entire file ...

$arr = file('RAY_temp_arcee123.txt', FILE_IGNORE_NEW_LINES);

... and used an iterator to get to the selected line. Using or omitting the iterator was undetectable in my tests -- iterating over hundreds of thousands of lines with foreach() did not even show up in the timing results. The time to read the 45 million bytes was where the 344 milliseconds came from.

It appears "much faster" to use fseek() if you know what you want to locate. And (serve this with a helping of mouse milk) we are talking about taking down something that needs 1/3 second to run ;-)

Assisted Solution

Expert: Dave Baldwin replied at 2024-11-24 10:46:10

166 points EXCELLENT

Ray, since you are counting down to create your lines in the text file, finding '08000000' only reads 20% of the file (200,000 lines), not 80% (800,000 lines). So it would really take 4 times as long to get to the 800,000th line. About 1.376 seconds.

Expert: Ray Paseur replied at 2024-11-24 10:39:24

Using @gr8gonzo's method the results are substantially faster.

WRITE LINES STOPPED 3,467.103 ms
READ LINES STOPPED 0.057 ms

This also appeared to be independent of the actual record chosen - line #200,000 was as fast as line #800,000.

Assisted Solution

Expert: gr8gonzo replied at 2024-11-24 08:14:33

167 points EXCELLENT

My $0.02 - I'd agree with most everything that's been said. The only thing I would add is that I often do a lot of large data imports at work, and some of them were so large that I needed to break them up into multiple runs (e.g. process 10,000 lines at a time). As I got towards the end of the file, each run took longer and longer because I kept having to re-read all the previous lines before reaching the desired line to resume the import.

I was able to get around this by using ftell() and fseek(). The ftell() function will return your current position in the file. So if you've read 1,000,000 lines that are around 50 - 150 bytes each, and you run ftell(), you might get a number like something like 75024048.

Then, if you want to restart the script and zoom back to that position instantly, you simply use fseek() to set the file pointer back to that position without having to re-read all the previous lines.

You can use this method to build "indexes" of the major intervals (e.g. a separate file that holds the offsets for every 10,000 lines), and then you can quickly figure out the nearest 10,000th interval, fseek to it, and read far fewer lines to get to the desired line.

As Ray said, databases are far better when you're doing this repeatedly. You'll avoid locking issues and get far better performance overall.

Accepted Solution

Expert: Ray Paseur replied at 2024-11-24 05:18:00

167 points EXCELLENT

Yes, it does cost. But the cost may be so small that optimizing this would be like milking a mouse -- no matter how much effort you put into it, you won't get very much out.

Please install this script and run it to see the effect. On my server, the load process took about 3 seconds and the read/lookup took less than half a second. You might extend this code to add a comparison of an indexed data base lookup, so you can see what is to be gained from optimizing a half-second process. If the process is to be repeated a million times, it's clearly worth optimizing.

0800000Simulated email address,simulated tag
WRITE LINES STOPPED 3,184.101 ms
READ LINES STOPPED 343.783 ms

<?php // RAY_temp_arcee123.phperror_reporting(E_ALL);echo '<pre>';// INSTANTIATE THE TIMER$sw  = new Stopwatch;// SIMULATED DATA$line = 'Simulated email address' . ',' . 'simulated tag';// LOAD DATA INTO THE FILE$sw->start('WRITE LINES');$cnt = 1000000;$fph = fopen('RAY_temp_arcee123.txt', 'w');while ($cnt){    $num = str_pad($cnt,7,'0', STR_PAD_LEFT);    fwrite($fph, $num . $line . PHP_EOL);    $cnt--;}fclose($fph);$sw->stop('WRITE LINES');// READ THE DATA TO FIND LINE #800,000$sw->start('READ LINES');$arr = file('RAY_temp_arcee123.txt', FILE_IGNORE_NEW_LINES);foreach ($arr as $str){    if (strpos($str, '0800000') === 0) break;}echo $str . PHP_EOL;$sw->stop('READ LINES');// A SCRIPT TIMER FOR ALL OR PART OF A SCRIPT PHP 5+// MAN PAGE http://php.net/manual/en/function.microtime.phpclass StopWatch{    protected $a; // START TIME    protected $s; // STATUS - IF RUNNING    protected $z; // STOP TIME    public function __construct()    {        $this->a = array();        $this->s = array();        $this->z = array();    }    // A METHOD TO PROVIDE A FINAL READOUT, IF NEEDED    public function __destruct()    {        $ret = $this->readout();        if (!$ret) return FALSE;        echo "<b>$ret</b>";        echo PHP_EOL;    }    // A METHOD TO REMOVE A TIMER    public function reset($name='TIMER')    {        // RESET ALL TIMERS        if ($name == 'TIMER')        {            $this->__construct();        }        else        {            unset($this->a[$name]);            unset($this->s[$name]);            unset($this->z[$name]);        }    }    // A METHOD TO CAPTURE THE START TIME    public function start($name='TIMER')    {        $this->a[$name] = microtime(TRUE);        $this->z[$name] = $this->a[$name];        $this->s[$name] = 'RUNNING';    }    // A METHOD TO CAPTURE THE END TIME    public function stop($name='TIMER')    {        $ret = NULL;        // STOP ALL THE TIMERS        if ($name == 'TIMER')        {            foreach ($this->a as $name => $start_time)            {                // IF THIS TIMER IS STILL RUNNING, STOP IT                if ($this->s[$name])                {                    $this->s[$name] = FALSE;                    $this->z[$name] = microtime(TRUE);                }            }        }        // STOP ONLY ONE OF THE TIMERS        else        {            if ($this->s[$name])            {                $this->s[$name] = FALSE;                $this->z[$name] = microtime(TRUE);            }            else            {                $ret .= "ERROR: CALL TO STOP() METHOD: '$name' IS NOT RUNNING";            }        }        // RETURN AN ERROR MESSAGE, IF ANY        return $ret;    }    // A METHOD TO READ OUT THE TIMER(S)    public function readout($name='TIMER', $dec=3, $m=1000, $t = 'ms', $eol=PHP_EOL)    {        $str = NULL;        // GET READOUTS FOR ALL THE TIMERS        if ($name == 'TIMER')        {            foreach ($this->a as $name => $start_time)            {                $str .= $name;                // IF THIS TIMER IS STILL RUNNING UPDATE THE END TIME                if ($this->s[$name])                {                    $this->z[$name] = microtime(TRUE);                    $str .= " RUNNING ";                }                else                {                    $str .= " STOPPED ";                }                // RETURN A DISPLAY STRING                $lapse_time = $this->z[$name] - $start_time;                $lapse_msec = $lapse_time * $m;                $lapse_echo = number_format($lapse_msec, $dec);                $str .= " $lapse_echo $t";                $str .= $eol;            }            return $str;        }        // GET A READOUT FOR ONLY ONE TIMER        else        {            $str .= $name;            // IF THIS TIME IS STILL RUNNING, UPDATE THE END TIME            if ($this->s[$name])            {                $this->z[$name] = microtime(TRUE);                $str .= " RUNNING ";            }            else            {                $str .= " STOPPED ";            }            // RETURN A DISPLAY STRING            $lapse_time = $this->z[$name] - $this->a[$name];            $lapse_msec = $lapse_time * $m;            $lapse_echo = number_format($lapse_msec, $dec);            $str .= " $lapse_echo $t";            $str .= $eol;            return $str;        }    }}

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31:32:33:34:35:36:37:38:39:40:41:42:43:44:45:46:47:48:49:50:51:52:53:54:55:56:57:58:59:60:61:62:63:64:65:66:67:68:69:70:71:72:73:74:75:76:77:78:79:80:81:82:83:84:85:86:87:88:89:90:91:92:93:94:95:96:97:98:99:100:101:102:103:104:105:106:107:108:109:110:111:112:113:114:115:116:117:118:119:120:121:122:123:124:125:126:127:128:129:130:131:132:133:134:135:136:137:138:139:140:141:142:143:144:145:146:147:148:149:150:151:152:153:154:155:156:157:158:159:160:161:162:163:164:165:166:167:168:169:170:171:172:173:174:175:176:177:178:

Select All Code

Best to all, over and out, ~Ray

Expert: Dave Baldwin replied at 2024-11-24 01:04:54

Yes, it does cost. But as long as it is a text file without any indexes that you can use to skip the unwanted lines, that is what you have to do. Which is why Ray suggested that you load the CSV file into a database because databases do create indexes and provide the ability to search much more quickly than reading a text file line by line.

Years ago when I had to write a program that used a CSV file for a 'database', the first thing I did when the program started was to read it all into memory and create indexes to find the data I needed. That worked then because the data was read many times while the program was running. If you are doing this on a web site and only reading one line each time, you would be Much better off putting it a database because it would be Much quicker.

Author: Evan Cutler replied at 2024-11-23 20:37:27

Ok guys.Thanks much.
What happens if I need to read somewhere beyond 800000? Doesn't it cost alt to get to the 800000 if I have to readline to that point?

Expert: duncanb7 replied at 2024-11-23 17:56:59

Dear All,

Thanks Ray_Paseur's reminder, I used slower function that is not related to 4096
and it also run much slower since using fgetc from last code.
After Tintin's suggestion code that is really good code and really fast & easy, and I try to
improve my code as follows code ,as same speed as "sed"

arcee123, hope you try this all code including Tintin's code for speed at your side.
I think I have no more room to improve the speed faster by fgets.

Duncan

<?php  $f = fopen ("../../data/yourfile.csv", "r");  $i= 0;// where 5000 is line number  while ($line= fgets ($f)) { if ($i==5000) {print ($line);break;}; ++$i; }   fclose ($f);?>

1:2:3:4:

Select All Code

Expert: Tintin replied at 2024-11-23 16:57:44

Depending on whether you have safe_mode enabled, you can always "cheat" and do:

<?php$line = `sed -n 124535p filename`;echo $line?>

1:2:3:4:

Select All Code

Expert: Ray Paseur replied at 2024-11-23 15:46:48

Please examine the descriptions of these functions:

fgetc()
fgets()
fseek()

It always pays to read the manual :-)

Expert: duncanb7 replied at 2024-11-23 15:31:46

Following code might be what you need for complete PHP code to read content at certain line-number,(Note: where 4096 in code area, you can change it with adding some margin if you know the max characters in one line of your file to speed up the code running time)

Hope I understand your questionc completely, if no, please point it out

Duncan

php code , it is tested at my linux server
========================================

<?php  $handle = @fopen("../../yourfile.csv", "r");if ($handle) {   // while (($buffer = fgets($handle, 4096)) !== false) { echo $buffer; }   // if (!feof($handle)) {   echo "Error: unexpected fgets() fail";}   $num=123;	$line=readLine($num,$handle); ///123 is just exmple line-number;	echo "Line Num:".$num."-----------------";	echo $line."";    fclose($handle);}  function readLine ($linenum,$fh) {        $line = fgets ($fh, 4096);        $pos = -1;        $i = 0;        while (!feof($fh) && $i<($linenum-1)) {            $char = fgetc($fh);            if ($char != "" && $char != "
") {                fseek($fh, $pos, SEEK_SET);                $pos ++;            }            else $i ++;        }        $line = fgets($fh);        return $line;    }?>

1:2:3:4:5:6:7:8:9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:

Select All Code

Expert: Ray Paseur replied at 2024-11-23 15:16:02

You can do this easily. Just read up to the 124,535th line. That's only about 1/8 of the lines. If you load the file into a database table, you can use the LIMIT clause to get the correct line. Use this function: http://php.net/manual/en/function.fgets.php

&nbps;

About Us|Service|Pricing

即時新聞:↓

即時新聞:↑

On Balance Volume (OBV):

Ask Question Forum:

get specific line in large csv file using php

Video♬:↓

Your Information

Members/Profolio