1

I want to read a huge excel file in patches so as to reduce reading time. I wrote the code below.

$xls = new Spreadsheet_Excel_Reader($path);
$dd=array(0);
for($row=2;$row<=10;$row++){
$val=$xls->val($row,$field);
}

This takes a lot of time each time the file is read because the file is huge. The file also gets reloaded each time.

How can I read only the required rows of the file to save time??

David Kariuki
  • 1,522
  • 1
  • 15
  • 30
warda Jouria
  • 63
  • 4

1 Answers1

0

The file will get reloaded each time the PHP script is executed, simply due to the fact that it does not keep the previous state. When you say a large file, how many records/bytes are we talking about?

To speed up the reading of such a file, you could put it on a RAM disk (if using Linux) which is far faster than SSD's. Or, read it and store a CSV equivalent with fixed record lengths. The fixed record lengths will allow you to jump to segments as you wish, and retrieve the number of records easily.

So if your record length became 90 bytes/characters and you wanted the records 100 to 109, you would open the file for reading, fseek to position 9000 (90 * 100) and grab the next 900 characters.

Jim Grant
  • 1,128
  • 2
  • 13
  • 31
  • Is there a way to format the file to read rows from 100 to 109 only, which is just storing these lines in memory and not storing the rest of the rows ?? – warda Jouria Apr 23 '19 at 09:19
  • It all depends on how automated you want the process. If the XLS file is generated once a month, then it would be worth saving it as a CSV and then using that. If it's generated every hour or day or so, by another process, then it becomes a different proposition. Also, what version of XLS file are you using? If they are XLSX, then memory will be consumed as the file will need to be unzipped before the contents can be extracted (which is done in memory). Does the file contain a single sheet, or is there multiple sheets involved? – Jim Grant Apr 23 '19 at 12:33