Inserción masiva de datos desde excel vía php

[bucky]

Hola a todos,

Me gustaría saber cual es el método más óptimo para insertar registros en una tabla en postgresql cogiendo los datos de un archivo xls vía php.

La funcionalidad en sí ya está implementada, se muestra un formulario para indicar el archivo excel y acto seguido, con la clase Spreadsheet, leo el excel vía php y voy ejecutando una sentencia INSERT por fila.

Esto funciona bien, pero cuando el fichero contiene más de 3500 registros (más o menos), el proceso postgresql se queda zombie en el servidor.

Además, la clase Spreadsheet, mencionada anteriormente, tampoco puede con archivos superiores a 3,5 MB (más o menos). Esto se solucionaría exigiendo un archivo csv y tratarlo como un fichero de texto, pero seguiría encontrándome con la limitación de los 3500 registros.

Todo esto en un servidor web linux (ubuntu) propio, PHP (5.2.6-2) y PostgreSQL (8.3.9)

¿Alguna idea?

Gracias por adelantado.

Saludos.

[huesos52]

Muchas ideas bucky. jejejeje

Por eficiencia yo lo haría con el archivo csv y utilizar COPY para ingresar los datos. Pueden ser millones de registros y esto lo hace en tiempos de respuesta muy optimos. Lo que no tengo muy claro es como ejecutar copy desde php, creo que hay una función llamada pg_copy para manejar este comando.

Una segunda idea, y que sería mas flexible para el usuario, es que efectivamente pueda leer excel. Pero no hacer un viaje a la base de datos por cada registro para ejectarlo e insertarlo (esto es sumamente ineficiente) sino mas bien, recoger todos los registros (o inserts) en un arreglo y enviarselo a una función de plpgsql que lo reciba y mediante un for realice las respectivas inserciones. De esta forma, solo se requiere una conexión a la base de datos.

saludos y espero te sirva alguna de las ideas

[bucky]

Iniciado por huesos52

Muchas ideas bucky. jejejeje

[...] Pero no hacer un viaje a la base de datos por cada registro para ejectarlo e insertarlo (esto es sumamente ineficiente)[...] De esta forma, solo se requiere una conexión a la base de datos.[...]

Hola amigo huesos52,

Totalmente de acuerdo contigo, me has dado una buena idea. Tengo que cambiar algunas cosillas, pero creo que me servirá.

Voy a probar el comando pg_copy_from que creo que era al que te referías. Gracias.

Saludos.

[bucky]

Bueno, el asunto podría funcionar pero me he encontrado con otro problema.

En la tabla donde inserto los datos importados, el campo primary key es automático y, por lo que veo, la sentencia COPY de postgres es copiar tal cual, como su nombre indica, olvidándose de los campos autoincrementados (los de tipo serial).

Es fácil saber cual es el siguiente que toca e incluirlo en la sentencia, pero si hablamos de más de un registro, no podría asegurar que mientras importo, otro usuario no cree o importe otros registros, lo que haría que la inserción masiva no se realizara por duplicidad en el primary key.

Se podría solucionar bloqueando la tabla con un COMMIT, pero si son muchos registros, podría dejar la tabla sin responder durante muchos minutos, no?

¿Algún sabio consejo más?

Gracias.

Saludos.

[huesos52]

2 cosas.
El copy tiene la posibilidad de establecer que columnas serán ingresadas. Según la documentación del copy puedes escoger las columnas a ser leídas.

COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }

sería
COPY tabla(columna1,columna2) FROM ruta_archivo

De esa forma no hay problema con lo que dices.

Si no te funciona, te digo por experiencia, el comando copy es excesivamente rapido. He hecho importaciones de 3'500.000 rregistros y te cuento que no se me demora ni 30 segundos. Entonces te recomiendo pruebes un archivo grandecito y estimes cuanto se puede demorar. Si el tiempo es infimo, el bloqueo de la tabla es una buena opción, ya que si otro usuario llega en ese milisegundo de instante, llegará a una cola de peticiones que esperará a que termine el proceso para ejecutarse y no verás comprometida la consistencia de la base de datos.

Nos cuentas como te va

[bucky]

Muchas gracias por tu experiencia y consejos

Tenía pensado solucionar el problema en PHP, pero parecer ser que la opción de decirle al comando COPY que columnas copiar sólo existe en SQL y no en PHP (corrígeme por favor si me equivoco)

Probaré tiempos de respuesta con pg_copy_from, y si no me convecen, lo intentaré con

COPY tabla(columna1,columna2) FROM ruta_archivo

Si tengo que hacerlo de esta última manera, ruta_archivo, se refiere a un archivo de texto con los registros en sí para copiar, no? ¿Sería simplemente crearlo, subirlo, ejecutar sentencia diciéndolo donde está el archivo y listo? ¿Hay alguna restricción respecto a la ruta del fichero (dentro del dominio, carpeta específica del servidor, etc)?

Gracias por tus respuestas de nuevo.

Saludos.

[huesos52]

bucky con respecto a la función, si es esa, pero su uso no o conozco muy bien.

Si tengo que hacerlo de esta última manera, ruta_archivo, se refiere a un archivo de texto con los registros en sí para copiar, no?

exacto

¿Sería simplemente crearlo, subirlo, ejecutar sentencia diciéndolo donde está el archivo y listo?

si.

¿Hay alguna restricción respecto a la ruta del fichero (dentro del dominio, carpeta específica del servidor, etc)?

Debe estar en una carpeta en la cual tenga permisos el usuario postgres.

saludos

[bucky]

Gracias huesos52

Con toda esta información, ya me veo capaz de solucionar mi problema, pero no dudes que volveré a consultarte si algún día me surge otro inconveniente.

Gracias.

Saludos.

[__SDP__]

/*...pone esta wea arriba.... de tu pagina...el otro drama es q tu codigo quizas es demasiado feo...y muchos bucles en una gran cantidad de registros se marea el server....me gustaria ver un poquito de codigo y como utilizas la libreria reader.php

<?php
ini_set("memory_limit","220M");
ini_set("max_execution_time","6000");
?>

<html>
...
...
..


*/