Cargar los datos de un archivo Excel a una base de datos MySQL resulta una de esas cosas que no resultan tan sencillas como en principio aparentan. Se que muchos seguramente pensaran, que de complicado puede tener guardar el archivo como un .csv y luego importarlo al MySQL.
Lo mismo pensaba yo hasta que me decidí a realizar un ejercicio práctico.
El Separador
Una de las primeras cosas a las que nos enfrentamos al intentar cargar los datos de un archivo Excel a una base de datos MySQL, es justamente la preparación del archivo .csv
Los ejemplos clásicos siempre resultan sencillos de ejecutar porque intencionadamente se colocan valores simples como por ejemplo una tabla donde listemos Nombre, Sexo, Estado Civil y Nacionalidad.
Pero, la realidad es distinta cuando nos enfrentamos a datos reales como por ejemplo el Sistema de Clasificación Industrial de América del Norte.
Donde resulta que, los valores contienen los signos de puntuación tradicionalmente utilizados como separadores de lista «,» y «;».
Microsoft Excel toma ese valor directamente de la configuración de Región definida en el sistema, de manera que optar por hace uso de algún otro separador, como por ejemplo «|» nos llevará por un camino que puede resultar más o menos tortuoso dependiendo de la versión de Windows que tengas en tu equipo.
Por fortuna, al menos para mí, LibreOffice Calc llega al rescate ya que a diferencia del Microsoft Excel, nos permite definir en un solo paso el delimitador que deseemos utilizar sin necesidad de realizar el paseo por la configuración de Windows. Y aún mejor, nos permite definir entrecomillar nuestro texto de manera que no tengamos ningún problema al exportar al .csv.
LOAD DATA INFILE
Una vez resuelta la creación de nuestro archivo .csv, resulta que el uso de LOAD DATA INFILE también trae lo suyo.
A pesar de que en los ejemplos básicos documentan de manera algo simplista que basta con utilizar la sintáxis…
LOAD DATA INFILE ‘ubicación/del/archivo’ INTO TABLE ‘nombre_de_la_tabla’
Resulta que tampoco es tan elemental.
Primeramente hay que cuidar las comillas que se utilizan y que resultan ser distintas para definir la ubicación del archivo y para nombrar la tabla.
LOAD DATA INFILE 'ubicación/del/archivo' INTO TABLE ´nombre_de_la_tabla´
Luego, resulta que la ‘ubicación/del/archivo’ no puede ser así nada más la que se nos ocurra ya que obtendríamos un…
Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Para conocer que ruta podemos utilizar para colocar el archivo, debemos ejecutar la sentencia
SHOW VARIABLES LIKE "secure_file_priv";
Por defecto la ruta definida para el MySQL 5.6 es…
File ‘C:\ProgramData\MySQL\MySQL Server 5.6\Uploads\
NOTA: Es fácil confundirnos con ProgramFile, pero la ruta correcta es ProgramData, si nos equivocamos obtendremos un…
Error Code: 29. File 'C:\ProgramData\MySQL\MySQL Server 5.6\Uploads\scian_2.csv' not found (Errcode: 2 - No such file or directory)
El Charset
Otra de las situaciones a las que nos enfrentaremos al intentar cargar los datos de un archivo Excel a una base de datos MySQL, y quizás la que más trabajo me costo resolver, es lo referente al charset.
Habiendo creado nuestra tabla con un script como este
CREATE TABLE molybdenum.scian (
`ID_SCIAN` int(11) NOT NULL AUTO_INCREMENT,
`COD_SCIAN` varchar(11) NOT NULL,
`SCIAN_ES` varchar(250) NOT NULL,
`SCIAN_EN` varchar(250) NOT NULL,
PRIMARY KEY (`ID_SCIAN`),
UNIQUE KEY `COD_SCIAN_UNIQUE` (`COD_SCIAN`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ID_SCIAN: Identificador autoincremental, COD_SCIAN: Codigo SCIAN, SCIAN_ES: Definicion SCIAN Español, SCIAN_EN: Definicion SCIAN Ingles';
Al intentar ejecutar el comando…
LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.6\\Uploads\\scian.csv'
INTO TABLE scian
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
Resulta que obtenemos un emocionante…
Error Code: 1366. Incorrect string value: '\xEDa y e...' for column 'SCIAN_EN' at row
Y digo emocionante porque, además de que podemos encontrar múltiples referencias respecto de este y otros errores similares, y que algunos señalan en distintos sitios haber resuelto con tan solo ajustar la configuración del charset…
…de la tabla, …de la columna, …de la base de datos, …de la conexión, …del cliente, …
Puedo decir, que ninguna de ellas funcionó, al menos para mi caso concreto en donde, la única dificultad parecía ser la presencia de caracteres españoles, es decir letras eñes y vocales acentuadas.
O al menos era lo que yo percibía hasta encontrar la información en este enlace
https://bugs.mysql.com/bug.php?id=10573
Y que me llevó por otro camino.
Hay que revisar que hayamos guardado el archivo en codificación UTF sin BOM, en caso de que no haya sido así habrá que realizar la conversion.
Ya con ello, solo tendremos que asegurarnos de ejecutar
SET character_set_database=utf8;
Justo antes de ejecutar el LOAD DATA INFILE.
Fin de campo / Fin de línea
Ya que hemos resuelto el tema de la codificación de nuestro archivo .csv, ya no deberíamos tener inconveniente con el charset; sin embargo, dada la característica de nuestro archivo, tendremos que resolver un último issue.
Error Code: 1262. Row 1 was truncated; it contained more data than there were input columns
Para ello, solo tenemos que ajustar nuestra sentencia de ejecución de LOAD DATA INFILE para ajustar la evaluación del fin de campo y fin de línea.
LOAD DATA INFILE 'C:\\ProgramData\\MySQL\\MySQL Server 5.6\\Uploads\\scian_3.csv'
INTO TABLE molybdenum.scian
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
Y listo, ya con eso deberíamos poder realizar la carga de los datos a la base de datos MySQL.
Espero te haya parecido útil este post.
………….
Quiero man.tener.me informado: Seguir en Twitter @eldavid_oficial https://twitter.com/eldavid_oficial
Regálame un ME GUSTA. Y si eres solidario, COMPARTE para que otros puedan aprovecharlo.
El Blog de David Rengifo 