How to import large files from Excel to PHP?

Question

PHP

In PHPExcel, even a simple var_dump exceeds max_execution_time. Although the file is small, about 3000 lines.
spreadsheet-reader seems to be reading, even the order of the number of lines is the same, but at the same time print_r, as in the example on the github, displays some kind of nonsense. It seems that he reads the XLSX file without unpacking. Also, something about CSV flashes in the garbage output, as if it is trying to open the file as CSV.
The CSV option is inconvenient, so I would like to know about other options.

Answer 1

It turns out that spreadsheet-reader determines the type by extension. And I tried to feed him a temporary file without move_uploaded_file () and he really tried to recognize it as CSV. After specifying the extension, the file is successfully recognized in UTF-8.
https://github.com/nuovo/spreadsheet-reader

Answer 2

PHPExcel is extremely voracious.
Run large files only in CLI mode with no execution time limit.
And too big ones will devour all the RAM, so anyway the size will be limited to the maximum possible by the limits of the free frame.

Answer 3

If Excel 2007+ version, then first you can unzip this file (an excel file is a regular zip archive). Next, you will find a lot of xml files there, which contain both settings and your data. Here is this xml file and you can parse in parts. All this is done using PHP.

Answer 4

100 years ago there was a problem with xml, not that very large files or a resource-intensive task for modern computers. The iron was weak :)
The task was to import information into the catalog. We did the following:
In the database, a record was made in the table with the columns "path to the file", "position". initially position 0;
Then we read the file from the position indicated in this table and read it in chunks of N bytes. We check the presence of the opening tag "" and the closing tag through the functions of working with substrings. as soon as we find X closed tags (for example, 30 elements) and the same number of open ones, we cut off the excess and get markup for X products. We put it in the body of a valid XML document, make a DOMDocument, parse and prepare an array of data.
Then in the transaction we save the processed number of rows and put down the position from which we stopped. And here is the commit. This whole thing should be in a cycle. If there are problems with max_execution_time and there is no way to remove it, then such a procedure can be hung on Ajax! After all, it saves the current progress of the operation.
For XML, of course, there are functions that allow you to work with huge files, but we did not touch them.