Loading data with Spanish characters from #Microsoft Excel to #MySQL 5.6 with LOAD DATA INFILE

excel-to-mysqlCargar 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

tabla-excelLos 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.

catalogo_en_excel

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.

El pase de diapositivas requiere JavaScript.

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.

 

 

Deja una respuesta

Por favor, inicia sesión con uno de estos métodos para publicar tu comentario:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Salir /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Salir /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Salir /  Cambiar )

Conectando a %s

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.