Cargar datos en mySQL con LOAD DATA INFILE
Friday, August 3rd, 2007Mucha gente me ha preguntado esto y bueno, decidí hacer un post para quienes buscan esta instrucción, para que aprendan a usarla.
LOAD DATA sirve para tomar cualquier archivo “comma-separated” (separado por comas, aunque no necesariamente son comas) y cargarlo como datos en alguna tabla de mySQL; la sintaxis básica es:
LOAD DATA LOCAL INFILE '/importfile.csv' INTO TABLE test_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1, filed2, field3);
Adicionalmente tiene algunas opciones; pero expliquemos con detalle el comando:
LOAD DATA indica que debe cargar un archivo csv (cualquier hoja de cálculo puede generar este tipo de archivos); si pasamos la opción LOCAL indicamos que el archivo esta en nuestra máquina y que debe ser leído por el cliente y enviado al servidor; sino, la ruta (absoluta o relativa) es en el servidor.
Si dentro de la tabla hay registros, la violación de primary keys podría causar la detención de la carga, entonces escribimos las opciones IGNORE (ignora las filas que violen el constraint y no las inserta) o REPLACE (agrega las filas reemplazando las existentes).
Fijense que quedaria algo como:
LOAD DATA LOCAL INFILE ‘ruta_archivo’
REPLACE INTO TABLE ‘nombre de la tabla’
Si acaso el archivo está en una máquina windows guardado en ISO-8859-1, entonces pueden pasar opcionalmente el charset en el que está el archivo en la sentencia:
CHARACTER SET latin1
Las opciones de como está estructurado el campo:
La dirección que está en mi hoja de calculo tiene comillas simples y comas en los datos, como separo entonces?.
Puedes inventarte cualquier separador entre campos: campo1 : campo2 : campo3 : … campoN : (separados con 2 puntos), simplemente debes indicarle a mySQL cual es el separador de campos con:
FIELDS TERMINATED BY ‘;’ <- yo por lo general uso punto y coma en vez de coma, a veces, uso el PIPE -> |
Tambien pueden indicar con QUE están encerradas las cadenas, si comillas simples, dobles, numerales (#), acentos viriguardilla (~) y cualquier cosa que se les ocurra: “campo1″; “campo2″; “campo3″; …
FIELDS ENCLOSED BY ‘”‘ <- fijense que dentro de las comillas simples, estan unas dobles.
Pueden agregar OPTIONALLY para indicar que algunos campos estan encerrados con comillas, pero no todos:
FIELDS OPTIONALLY ENCLOSED BY ‘#’
Inicio y fin de linea:
Como se inicia la linea?, cadena vacia?, vienen con una columna serial que queremos ignorar?, algun caracter?, simplemente escribimos:
Interpret occurrences of tab, newline, or ??\?? preceded by ??\?? as literal characters that are part of field values.
LINES STARTING BY ” <- indica que las lineas empiezan en una cadena vacia
LINES TERMINATED BY ‘\n’ indica que la lina termina con un salto de linea (\n).
Si en su archivo csv hay una o mas filas que repreentan los encabezados de los campos y desean “obviarlas” entonces:
IGNORE 1 LINES hará que se ignore una linea.
Procesamiento de los campos:
En algún sitio debo indicar que representan las columnas de mi archivo coma-separado; las columnas de dicho archivo son leidas secuencialmente, asi que la primera columna del archivo, por defecto, se guardará en la primera columna de mi tabla, si queremos sobre-escribir este comportamiento ejecutamos:
(campo1, campo2, campo3, campo4, campo5 …
para indicar su orden de guardado en la tabla; ejemplo, tenemos una tabla:
nombre, apellido, cedula, fecha_nacimiento
pero en el csv los campos vienen cedula, nombre, apellido, fecha_nacimiento, entonces colocamos entre parentesis los campos asi:
(cedula, nombre, apellido, fecha_nacimiento)
Primera columna de mi csv corresponde al campo cedula, segunda al nombre y asi suscesivamente.
Formateo y condicional de datos:
Si acaso la fecha de nacimiento la guardan el el común formato de las hojas de excel (23/10/78), podemos entonces “guardar” en una variable temporal el valor del campo fecha de nacimiento y procesarla despues, ejemplo:
(cedula, nombre, apellido, @fecha) <- crea una variable temporal de fecha
SET fecha_nacimiento = str_to_date(@fecha, ‘%d/%m/%y’) <- toma la variable @fecha (que tiene la forma dia/mes/año de 2 digitos) y la convierte a ANSI (año de cuatro digitos-mes-dia).
un caso común ocurre con que la gente en JOSS PRO (notese el acento en la X) que escribia todo como si estuvieran trabajando en 8 bits y los nombres vienen con JESUS LARA, PERICO DE LOS PALOTES, cuando deberían ser naturalmente capitalizados; eso se acomoda:
(cedula, @nombre, @apellido, @fecha)
SET nombre = CONCAT(UCASE(LEFT(@nombre, 1)), SUBSTRING(LCASE(@nombre), 2))
Asi que queda como chuleta si quieren convertir sus viejas aplicaciones FOJJ PRO a nuevas RBDMS como mySQL.
Como final, la sintaxis completa queda:
LOAD DATA LOCAL INFILE ‘archivo’
IGNORE
FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘
LINES STARTING BY ” TERMINATES BY ”
IGNORE 1 LINES
(field1, field2, field3, @field4)
SET field4 = (alguna cosa SQL que quieran hacerle a @field4)
