Import excel spreadsheet with many records with php

Question

Good morning!! I have the following situation, I need to do an Excel spreadsheet import (.xml).

Only the problem is that my spreadsheet has over 100,000 records and php can’t read everything. The code is working perfectly for spreadsheet with 2500 records. Is there any way I can get him to read the 100,000?

Obg.

Follows the code;

    $arquivo = new DOMDocument();
    $arquivo->load($_FILES['arquivo']['tmp_name']);

    $linhas = $arquivo->getElementsByTagName("Row");
  
    $listaArquivo = [];

    foreach ($linhas as $linha) {
       
        $listaTemp = [
            $linha->getElementsByTagName("Data")->item(0)->nodeValue,
            $linha->getElementsByTagName("Data")->item(1)->nodeValue,
            $linha->getElementsByTagName("Data")->item(2)->nodeValue,
            $linha->getElementsByTagName("Data")->item(3)->nodeValue,
            $linha->getElementsByTagName("Data")->item(4)->nodeValue,
            $linha->getElementsByTagName("Data")->item(5)->nodeValue,
            $linha->getElementsByTagName("Data")->item(6)->nodeValue,
            $linha->getElementsByTagName("Data")->item(7)->nodeValue,
            $linha->getElementsByTagName("Data")->item(8)->nodeValue
        ];

        array_push($listaArquivo, $listaTemp);

       // echo json_encode($listaTemp);
        
    }
   exit();

Answer 1

If the problem is the runtime, and this is a single operation, that is, once imported it will not be necessary to re-do this import, you can simply increase the max_execution_time ini_set('max_execution_time', 300); or set_time_limit(300); that would solve the problem.

If a constant import will be made, that is, this functionality is constantly used, you can think about SPLITTING the operation in smaller batches, for example 2000 records, and running it in a partial way in time sequences.

Or, finally, you understand the logic of this object and optimize it or implement a different logic to read and import the data, from the file directly, without having to make use of this class (Domdocument).

PS. In time, does it import any record of this spreadsheet with 100k records? As it may also be the file size, it also checks:

ini_set('upload_max_filesize', '100M');
ini_get('post_max_size', 100M);

as it may be the maximum size of the POST if the file is being sent by the browser directly or by an FTP direct to PHP. All this depends on how it is implemented as a whole.

Answer 2

Hi, I did the two implants you suggested and it still didn’t work out. To show html and Submit to that page to read the file

I tried to put on both the first page and the page that reads the file and it didn’t work ini_set('upload_max_filesize', '100M'); and ini_set('max_execution_time', 300);

                    <form id="myform" method="post" action="lerArquivo.php" enctype="multipart/form-data">
                        <input type="file" style="width:100%;margin-top:2%;margin-right:5px " name="arquivo" />
                        <!-- accept=".csv, .CSV"  -->
                    </form>

                    <button type="button" class="btn btn-primary " onmousedown="lerArquivo()"><i class="fas fa-file-upload"></i></button>
                </div>

lerArquivo = function() {   

                var arq = document.getElementsByName('arquivo');
                if(arq[0].value == ""){
                    Swal.fire(
                        'Selecione um arquivo',
                        '',
                        'error'
                    );
                } else{
                    $('#myform').submit();
                }

          

            }