Memory error when importing data in MySQL from an Excel excel file


This is receiving the following error:

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 2 bytes) in C:\xampp\htdocs\servisa\Excel\reader.php on line 482

I have to read a very large Excel file (13 mb, 9000 rows) and insert all this data from a table in my MySQL database.

What I do is load all the excel data into an array, and then I generate each insert statement by traversing the array. Then I concatenate each insert into a single giant query.

I thought that this was more appropriate than performing 9000 inserts to the database. Does anyone suggest a better way to do it, with less memory consumption?


Here is my code:

require_once 'Excel/reader.php';
    $data = new Spreadsheet_Excel_Reader();
    $arreglo = array(array());
    for ($i = 1; $i <= $data->sheets[0]['numRows']; $i++) 
        if ($i == 1)

        $nomCompleto = mysql_real_escape_string($data->sheets[0]['cells'][$i][3]);//str_replace("'", "", str_replace("'", "", $data->sheets[0]['cells'][$i][3]));
        $parts = explode(" ", $nomCompleto);
        $apellido = array_shift($parts);
        $nombre = implode(" ", $parts);
        $nroBenef = $data->sheets[0]['cells'][$i][1];
        $dni =  $data->sheets[0]['cells'][$i][5];

        $tipoDni = $data->sheets[0]['cells'][$i][4];
        $sexo = $data->sheets[0]['cells'][$i][8];
        $domicilio = $data->sheets[0]['cells'][$i][9];//mysql_real_escape_string($data->sheets[0]['cells'][$i][9].' '.$data->sheets[0]['cells'][$i][10]);

        $date = str_replace('/', '-', $data->sheets[0]['cells'][$i][6]);
        $fnac = date('Y-m-d', strtotime($date));

        $arreglo[$i][0] = $apellido;
        $arreglo[$i][1] = $nombre;
        $arreglo[$i][2] = $dni;
        $arreglo[$i][3] = $nroBenef;
        $arreglo[$i][4] = $tipoDni;
        $arreglo[$i][5] = $sexo;
        $arreglo[$i][6] = $domicilio;
        $arreglo[$i][7] = $fnac;

    $sql ="insert into clientespami (apellido,nombre,dni,nroBeneficiario,tipoDni,sexo,domicilio,fechaNac) values ";
    $valuesArr = array();
    foreach($arreglo as $fila)

        $ape = $fila[0];
        $nom = $fila[1];
        $dni = $fila[2];
        $ben = $fila[3];

        $tipo = $fila[4];
        $sex = $fila[5];
        $domi = $fila[6];
        $nac = $fila[7];

        $valuesArr[] = "('$ape', '$nom', '$dni','$ben','$tipo','$sex','$domi','$nac')";

    $sql .= implode(',', $valuesArr);
    //echo $sql;
    $cant = mysql_affected_rows();
asked by martin.softpro 12.04.2016 в 00:18

3 answers


One of the reasons is that you are creating an oversized array ($ valuesArr) and then converting it to a string, on the other hand it is not advisable to use mysql_query since it is obsolete.

I suggest using PDO, an example could be something like this

$stmt = $pdo->prepare('INSERT INTO foo VALUES(:a, :b, :c)');
foreach($data as $item) {
    $stmt->bindValue(':a', $item[0]);
    $stmt->bindValue(':b', $item[1]);
    $stmt->bindValue(':c', $item[2]);


answered by 22.04.2016 в 00:27

I think you should run the PHP script from the command line. Do not do it from the browser. In addition to this form you can tell PHP that for this execution assign an unlimited memory limit only with the following command:

$ php -d memory_limit=-1 script.php
answered by 22.05.2016 в 01:01

If it is a large file, it is best to do it through the command line. There are also PHP applications in google that make a "strip" of the file and do it in parts. Phpmyadmin also has a hidden function for large lists of data.

answered by 22.05.2016 в 03:34