SQLite logotipo ( https://en.wikipedia.org/wiki/File:SQLite370.svg )

Importando datos en formato CSV a SQLite3

Download PDF

Publicado el martes 8 de octubre de 2019.

Actualizado el sábado 26 de octubre de 2019.

Algunas veces necesitamos trabajar con información o datos almacenados en formato CSV y casi siempre son listas «cortas» como de cinco mil hasta cincuenta mil elementos. Una manera de trabajarlos de manera muy rápida, con la ventaja de integrarlos luego a nuestros programas, es con SQLite y, para ser exactos, la versión 3.

Tabla de contenido:

Fichero CSV

Así como la famosa Guerra de los Cien Años en Europa durante la Edad Media no duró cien años, un archivo con valores separados por comas tampoco está siempre separado por comas, incluso algunos ni siquiera tendrán una coma alguna. Esto es así porque cada línea (delimitada con símbolos especiales como fin de líneaASCII 10 -y/o retorno de carro -ASCII 13-, dependiendo del sistema operativo usado) puede contener varios campos distintos. Es decir, cada línea es un registro para la base de datos, y si ese registro solo contiene un campo pues no necesitamos de coma alguna para separar campo (de hecho finalizar la línea con una coma agregaría un campo nulo a la base de datos).

También admite otros delimitadores, como por ejemplo el punto y coma (ASCII 59) para el caso de que manejemos números con decimales: como todos bien sabemos la coma es el símbolo separador de decimales y el punto como separador de miles. Otras veces usan el tabulador (ASCII 9); a lo largo de este artículo consideramos que la coma es es separador de campos

Otro punto a tener en cuenta, de manera independiente al separador que utilicemos, que si tenemos datos que dentro de ellos utilicen el separador pero con otro propósito, deberemos encerrar entre comillas dobles » (… datos …) « para distinguir cual es el verdadero separador, ejemplo:

"Snowden,Edward","Moscú,Rusia"

En este caso estamos separando el apellido y el nombre de una persona y la ciudad y país pero solamente el registro tiene dos campos nada más.

SQLite logotipo ( https://en.wikipedia.org/wiki/File:SQLite370.svg )
SQLite logotipo ( https://en.wikipedia.org/wiki/File:SQLite370.svg )

Instalación y uso básico de SQLite3

Pensando en la colaboración entre los distintos blogs sobre software libre en idioma castellano, este año nos ha llegado la hora de integrarnos de pleno a la comunidad: por ello hemos comenzado a enlazar los artículos relacionados para ahorrar trabajo y compartir conocimiento rápidamente. El señor David lleva su blog «Ocho Bits Un Byte» y allí ha publicado un artículo completo sobre esta sección «Instalación y uso básico de SQLite en Ubuntu 18.04»: recomendamos vayan, allá, lean y practiquen los ejercicios y luego vuelvan por acá.

Nota: en la medida de lo posible siempre guardamos en la «Wayback Machine» una copia de los artículos que enlazamos, así como el contenido de nuestro blog.

Importando un CSV con cabecera

No lo explicamos en la primera parte pero lo decimos justo ahora: muchas veces la primera línea es considerada como la cabecera para el resto de los registros. Para SQLite3 esto es una ventaja porque precisamente este primer registro los toma como nombres de campo. Consideren ustedes el siguiente fichero llamado capitales.csv:

ciudad,país
Québec,Canadá
Buenos Aires,Argentina
Caracas,Venezuela
Importando un archivo CSV con cabecera a una base de datos temporal en Squlite3
Importando un archivo CSV con cabecera a una base de datos temporal en Squlite3
  • El primer comando cat capitales.csv lista el contenido del fichero, observen el encabezado (primera línea «ciudad, país»).
  • Abrimos el programa con sqlite3.
  • Pasamos al modo CSV con .mode csv (los comandos son sensibles a mayúsculas y minúsculas).
  • Importamos los datos, importante notar que como estamos trabajando en un directorio llamado sqlite3-datos, lugar donde descargamos el archivo CSV, no es necesario indicar la vía completa, al final agregamos el nombre deseado para la tabla; así que tecleamos .import capitales.csv capitales.
  • ¡Sin errores! Tampoco confirmaciones, el estoicismo de GNU/Linux es legendario, debido a esto verificamos si fue creada la tabla con el comando .schema capitales y arroja que tiene dos campos, ambos de tipo texto, con los nombres del encabezado del archivo CSV.
  • Para ver los datos en sí mismo usamos el código select ciudad from capitales; y el detalle aquí es que los caracteres muy especiales de nuestro idioma castellano (ASCII extendido) resulta que se entregan de manera entrecomillada.
  • Pedimos la lista de países y, bueno, las combinaciones de consultas SQL (valga la redundancia) podrán imaginar que son muchísimas.
  • Muy importante: señalamos con la flecha en la imagen que hemos abierto una base de datos temporal en memoria RAM, al salir de SQLite se borran los datos importados; la manera correcta es abrir la base de datos invocándola desde el principio, nuestra base de datos se llamará «ciudades.db» pues introducimos sqlite3 ciudades.db. Prueben todo de nuevo y guarden su base de datos, salgan de SQLite y vuelvan a abrir y listar, etc.

Uso práctico

La teoría es muy bonita, debemos estudiarla pero teniendo cuidado de no caer y vivir solamente para la misma; acá siempre estamos pendiente de darle un uso cotidiano, útil al software libre. Para ello, con la base de datos creada y guardada de la sección anterior, la abriremos e introduciremos el siguiente código:

.mode html
.select * from capitales;
<TR><TD>Québec</TD> <TD>Canadá</TD> </TR> <TR><TD>Buenos Aires</TD> <TD>Argentina</TD> </TR> <TR><TD>Caracas</TD> <TD>Venezuela</TD> </TR> 

Sí, eso que ven es código HTML y hacemos notar que en el software que desarrollemos podremos tranquilamente compilar incluyendo las librerías de SQLite3, quedando integrada para realizar páginas web listas para ser publicadas vía FTP a nuestro servidor web ¿qué les parece?

Una consideración final: para poder exportar este código HTML debemos indicarle primero a SQLite3 que entre en modo de salida a un fichero concreto, pongamos uno llamado «ciudades.html»:

.output ciudades.html
.mode html
.select * from capitales;

Obviamente el fichero solo contendrá la lista de ciudades en formato HTML, para poder crear una página web de manera correcta aún faltan muchos elementos, para ello visiten nuestro tutorial al respecto.

Lista de ciudades exportada en formato HTML
Lista de ciudades exportada en formato HTML

Importando un CSV sin cabecera

De tener un CSV que no tenga la cabecera, pues nosotros mismos debemos crear primero la base de datos, crear la tabla, establecer un índice para acelerar la búsqueda -y depuración de datos, ya vamos a explicar eso-, ¡manos a la obra!

Por favor, lea también   "El arte de crear secuencias de comandos de solicitudes HTTP usando Curl" por Daniel Stenberg

Tenemos dos ficheros CSV con los siguientes contenidos:

capitales1.csv

Québec,Canadá
Buenos Aires,Argentina
Caracas,Venezuela

capitales2.csv

Madrid,España
Buenos Aires,Argentina
Pekín,China
Importando desde un CSV a SQLite3 de manera indexada y sin repetir elementos
Importando desde un CSV a SQLite3 de manera indexada y sin repetir elementos
  • Abrimos una base de datos nueva capitales.db de manera permanente (graba en disco).
  • Con el comando create table hacemos una tabla con dos campos de texto.
  • Realizamos un índice que permite buscar rápidamente nuestros datos y con una parámetro especial: unique establece que no aceptará datos repetidos en ese campo (las ciudades són únicas pero los países tienen muchas ciudades, el campo país no puede ser singularizado).
  • Repasamos lo aprendido anteriormente pero ahora importaremos dos CSV.
  • ¡Exacto! Al importar el segundo CSV uno de los registros no será agregado a la base de datos por estar registrado previamente.
Fíjense en la creación de la tabla la opción «NOT NULL», en este «tuit» explican muy bien el asunto de los valores nulos.

Otros tipos de exportación de datos

Aparte de html también podremos regresar los datos a CSV (claro está, con el comando .mode csv ) pero ahora sin registros repetidos, ordenados como necesitamos, con los campos que necesitemos, etc. Mostramos cada una de las ocho salidas posibles:

.mode ascii

Este formato no es posible representarlo correctamente en WP ¿sorprendidos, sorprendidas? El asunto es que presenta unos caracteres no imprimibles que están al comienzo de los códigos ASCII, pero lo más particular es que en tantos años por acá trabajando con ordenadores y puertos seriales nunca los habíamos empleado… hasta ahora, miren la imagen siguiente donde explicamos:

Separadores de información en ASCII
Separadores de información en ASCII

.mode column

Québec      Canadá    
Buenos Air  Argentina 
Caracas     Venezuela 
Madrid      España    
Pekín       China     

.mode csv

"Québec","Canadá"
 "Buenos Aires",Argentina
 Caracas,Venezuela
 Madrid,"España"
 "Pekín",China

.mode html

<TR><TD>Québec</TD>
<TD>Canadá</TD> </TR>
<TR>
<TD>Buenos Aires</TD>
<TD>Argentina</TD>
</TR>
<TR><TD>Caracas</TD>
<TD>Venezuela</TD>
</TR>
<TR><TD>Madrid</TD>
<TD>España</TD>
</TR>
<TR><TD>Pekín</TD>
<TD>China</TD>
</TR>

.mode

.mode insert

INSERT INTO "table" VALUES('Québec','Canadá');
 INSERT INTO "table" VALUES('Buenos Aires','Argentina');
 INSERT INTO "table" VALUES('Caracas','Venezuela');
 INSERT INTO "table" VALUES('Madrid','España');
 INSERT INTO "table" VALUES('Pekín','China');

.mode line

ciudades = Québec
    país = Canadá
 ciudades = Buenos Aires
    país = Argentina
 ciudades = Caracas
    país = Venezuela
 ciudades = Madrid
    país = España
 ciudades = Pekín
    país = China

.mode list

Québec|Canadá
Buenos Aires|Argentina
Caracas|Venezuela
Madrid|España
Pekín|China

.mode quote

'Québec','Canadá'
'Buenos Aires','Argentina'
'Caracas','Venezuela'
'Madrid','España'
'Pekín','China'

.mode tabs (tabuladores horizontales)

 Québec    Canadá
 Buenos Aires    Argentina
 Caracas    Venezuela
 Madrid    España
 Pekín    China

.mode tcl

En este caso la codificación de caracteres se rige por la norma ISO/IEC 8859-1, en forma particular con el número octal del caracter:

"Qu\303\251bec" "Canad\303\241"
 "Buenos Aires" "Argentina"
 "Caracas" "Venezuela"
 "Madrid" "Espa\303\261a"
 "Pek\303\255n" "China"

Visualizando una base de datos

Actualizado el sábado 26 de octubre de 2019.

Bien, no todo en esta vida es por línea de comandos, es mi deber el comportarme de «manera normal» para con vosotros estimados lectores y lectoras. Por ello he visto en la red este programa que aunque no es software libre al menos es de código abierto, os dejo una captura de pantalla para que veais a qué me refiero:

SQLite Database Browser ( sqlitebrowser.org )
SQLite Database Browser ( sqlitebrowser.org )

El enlace hacia SQLite Database Browser  lo he obtenido al leer el siguiente artículo sobre cómo reparar una base de datos SQLite «corrupta». Desconozco la razón de por qué suceden estos casos donde se daña el formato de los bases de datos, pero acá en Venezuela mi experiencia indica las fallas eléctricas en primer lugar y la «solución mágica» de los empleados y empleadas de reiniciar de manera forzada («hard reset») los servidores.

Esto último me han insistido mucho que han de estar bajo llave (los servidores) pero pienso que uno debe confiar en los empleados y si ellos o ellas «abusan» pues…

Resumen de objetivos alcanzados

Esperamos les haya gustado esta entrada, la hicimos de tal manera de que que cubra lo mínimo necesario pero con la complejidad suficiente para ser utilizada es sus futuras aplicaciones.

Language programming books, photo by Helder da Rocha
Fuentes consultadas

En idioma castellano

En idioma francés

  • «».

En idioma inglés

<Eso es todo, por ahora>.

Download PDF