So here's the task: Take the price for 30,000 lines and ideally compare it with the existing table for 1000 lines (update prices, in general).

In the extreme case, you just need to write the contents of xls into a separate table

I suspect that when parsing "in the forehead," the script will fall off on the first thousand lines.

Is there any way to solve the problem? (Maybe you can feed xls directly in MySQL, or alternatively, keep the xls file on the host and when requesting the product, search for the price directly in the xls file (via the article). But for this, there must be some way to search the xls file from PHP)

xlsx weighs 1.5 MB, when translated to xls - 4.5

In general, who faced with a similar task, I ask for help, where to start from. thank

  • If it is possible to convert the file to csv format, then do it, then read it in portions of 400-500 lines and use the function php.net/manual/ru/function.str-getcsv.php - naym
  • Unfortunately no. when trying to save via excel as csv. XML data cannot be saved because the book does not contain XML mappings. The task is to make an interface for dummies, so something more complicated than a file-save is hardly suitable for them - volkoff
  • Find the right library for reading xls. You need one that allows you to access a specific string. In extreme cases, you can read everything, 30k is not so much. It all depends on how you will later dispose of the data. Here are some libraries: github.com/PHPOffice/PHPExcel , github.com/nuovo/spreadsheet-reader . - Gino Pane

1 answer 1

First, you need to normalize the data, make a convenient reading, better in csv. Further search can be made in mysql, simply having written down lines. And can be different. Using bitfields and bitmaps create bitfields on each property.
Where a property with a value of x will have a bit field of 0101011, which means that for lines 1, 2, 4, 6, the property has the value x. Then when you need to filter, we do bitwise AND or OR, or combine both. Then simply calculate the position of the included bits, indicating the line numbers. One property of a bit field with 30K lines will be ~ 3.6 kilobytes.