A veces puede ser interesante ofrecer al usuario el poder actualizar datos de una base de datos MySQL utilizando como fuente un fichero *.csv generado por Excel

MySQL es capaz de importar datos no sólo a través de archivos con sentencias SQL, sino tambien desde ficheros CSV exportados desde una hoja de calculo como MS Excel a traves de la funcion LOAD DATA INFILE.

En este tutorial no veremos todas las posibilidades que la función ofrece. Nos centraremos en aquellas de uso más común, pero que se adaptan al 95% de las situaciones en que vayamos a encontrarnos. Para saber más sobre la misma podéis consultar el manual de MySQL

En la mayoría de los casos, la función tendrá un aspecto como éste:

LOAD DATA LOCAL INFILE “nombreArchivo” REPLACE INTO TABLE miTabla
FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n\r’ IGNORE 1 LINES (columna1,columna2)

?Qué significa todo esto? Vamos a verlo paso a paso:

LOAD DATA LOCAL INFILE “nombreArchivo”

MySQL tiene 2 caminos para leer el archivo .csv:

  1. Leerlo directamente el servidor
  2. Conectarse con el cliente y leerlo a través de él

La primera posibiliidad es muy rapida en su ejecución (Es el propio servidor el que ejecuta la operación), pero necesita de más permisos (FILE, por ejemplo, que no siempre está activado para usuarios normales).

En el segundo caso (gracias a la opcion LOCAL) MySQL crea una conexión con el cliente y, a través de ella, comienza a leer el archivo. El sistema es un poco más lento, pero a cambio no se necesitan permisos especiales tipo FILE. En este caso, sin embargo, es necesario que mysqld haya sido inciado con la opción --local-infile=1

Por lo tanto: LOAD DATA LOCAL INFILE indica que utilizaremos el segundo sistema

LOAD DATA LOCAL INFILE “nombreArchivo” REPLACE INTO TABLE tableName

La opción REPLACE (y su opuesta IGNORE) controlan la entrada de registros que tengan una clave ya existente en la tabla.

REPLACE provoca que el registro reemplace el ya existente.

IGNORE, en cambio, ignora el nuevo registro sin modificar el existente. Al utilizar en nuestro caso la opcion LOCAL éste es el valor definido por defecto, por lo que:

LOAD DATA LOCAL INFILE nombreArchivo IGNORE INTO TABLE miTabla

y:

LOAD DATA LOCAL INFILE nombreArchivo INTO TABLE miTabla

Son 2 sentencias identicas.

Opciones de separacion de datos:

1.- FIELDS/LINES TERMINATED BY:

Estas opciones nos permiten indicar qué simbolos son los que separan cada campo y linea, respectivamente, del archivo *.csv. Algunos ejemplos pueden ser:

  • FIELDS TERMINATED BY ‘,’ (separados por comas)
  • FIELDS TERMINATED BY ‘;’ (separados por punto y coma)
  • FIELDS TERMINATED BY ‘\t’ (separados por tabulaciones)

Y para indicar el separador de cada linea:

LINES TERMINATED BY ‘\n\r’

2.- IGNORE XX LINES

Esta opcion permiteindicar si la(s) primera(s) linea(s) del fichero *.csv contiene algun otro tipo de informacion que no debe ser insertada en la tabla. El ejemplo mas habitual es cuando esta primera linea se utiliza en excel para mostrar los titulos de cada fila, en cuyo caso deberiamos escribir:
IGNORE 1 LINES

SUPUESTO PRACTICO

Como supuesto práctico hemos decidido que nuestro futuro usuario ya conoce que, al exportar desde Excel, debe utilizar punto y coma para separar cada campo y cada linea del fichero se corresponde con un registro en la tabla. Al mismo tiempo, hemos asumido que la primera fila incluye el titulo de cada columna y que la hoja de calculo contenia un campo de indice. Nuestro ejemplo tambien asumira que la hoja de calculo debe actualizar los registros actuales de la tabla (por lo que utilizaremos REPLACE)

Empezaremos creando el formulario que permitirá al usuario seleccionar el fichero csv:



Como veis, simplemente hemos creado el formulario y un input que permita al usuario seleccionar el fichero csv.

Vayamos ahora al lado servidor. En el mismo documento, insertaremos algo de PHP que procese el fichero seleccionado y lo inserte en la Base de datos La consulta base será algo como esto (de momento sólo debes cambiar el valor para “miTabla”):

LOAD DATA LOCAL INFILE nombreArchivo REPLACE INTO TABLE miTabla FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES

Simplemente observad a continuación que el parametro nombreArchivo tiene como valor el equivalente al campo del formulario ‘csv’, que se corresponde con el nombre del input de nuestro formulario (Se ha intentado simplificar el código al máximo para centrarnos en la consulta)


if (isset($_POST['csv']){
uploadQuery="LOAD DATA LOCAL INFILE ".$csv." REPLACE INTO TABLE miTabla FIELDS TERMINATED BY';' LINES TERMINATED BY '\r\n' IGNORE 1 LINES";
mysql_query($uploadQuery) or die("error:".mysql_error());
}

Para poder verlo con más claridad (gracias por el comentario, Francis), podeis ulsar aqui y bajaros unos archivo de ejemplo