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

Question

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

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

2

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?

Thanks!

Here is my code:

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

        $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;
    mysql_query($sql,$conexion);
    $cant = mysql_affected_rows();

mysql php excel

asked by martin.softpro 12.04.2016 в 00:18

source

3 answers

Error installing Pillow on ubuntu 15.10 32 bits How to make a progress bar 0 to 100?

score 1 · Answer 1

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]);
    $stmt->execute();
}

PDO

score 0 · Answer 2

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

score 0 · Answer 3

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.