Ficheros de texto a bases de datos SQL. Un caso práctico

Nuestra tarea es seleccionar algunas de esas columnas que se pueden encontrar separadas por un "pipe" para crear una tabla en una base de datos tipo SQL (SQLite, MySQL, Postgres, etc.) Es de suponer que cuando dicho fichero* tiene miles de registros la tarea se complica. Para ello vamos a utilizar tres herramientas super potentes: Awk, nl y SQLiteManager, las cuales son muy fáciles de usar.

1. Antecedentes

Tenemos creada nuestra tabla en la base de datos. Primero vamos a examinar cuáles columnas del fichero de texto corresponden con los campos de nuestra tabla. A cada uno de esos campos lo vamos a asociar con un número. Por ejemplo, si nuestro fichero tiene 20 columnas y estas están separadas por un pipe (|). En realidad se pueden separar por otro FS (field separator) como una coma, o un espacio. En este ejemplo, utilizamos el pipe. y a nosotros solo nos interesan las columnas 1, 2, 3, 4, 5, 7, 8, 10, 11 y 14 la selección sería:

$1 "," $2 "," $3 "," $4 "," $5 "," $7 "," $8 "," $10 "," $11 "," $14. 

2. Just Testing

Hagamos una prueba: Abrimos la terminal y escribimos el comando:

awk -F"|" '{ print $1 "," $2 "," $3 "," $4 "," $5 "," $7 "," $8 "," $10 "," $11 "," $14 }' foo.txt

Aquí notamos que el separador de campos, Field Separator (FS), es un pipe; enseguida es necesario abrir unas comillas simples y dentro un par de llaves que es donde insertaremos la acción a ejecutar. Dentro de las llaves le damos un "print" a las columnas a imprimir las cuales irán separadas por unas comillas y dentro unas comas. Fuera de las comillas simples, el siguiente argumento del comando awk es el fichero de texto que deseamos transformar. 

OJO: Antes de darle "enter" es necesario considerar el tamaño del fichero. Si es de varios MB sería conveniente tener una versión reducida para ir experimentando con las salidas generadas y verificar lo que realmente requerimos.

3. La papa

Una vez que ya tenemos el resultado esperado, simplemente rederigimos la salida del comando: al fichero correspondiente:

awk -F"|" '{ print $1 "," $2 "," $3 "," $4 "," $5 "," $7 "," $8 "," $10 "," $11 "," $14 }' foo.txt > foo.csv

Ademas hay que señalar que nuestra tabla va indexada y el fichero de texto original no incluye ningún campo del tipo id... Estelo vamos a agregar con nl:

nl -s, foo.csv > foo2.csv

A continuación vamos a crear una base de datos en sqlite3, luego crearemos la tabla correspondiente y posteriormente la poblaremos con el contenido de "foo2.csv"

a) Crear db: sqlite3 codigos_postales.db

b) Crear la tabla con un script SQL: 

sqlite> .read createTable.sql

sqlite> .table <- Verificamos la tabla "foo" recién creada

4. SQLiteManager

Hasta ahora únicamente hemos trabajado con la terminal. Llego la hora de abrir el Firefox e instalarle un plugin super-recomendado: SQLiteManager (Así como hay un phpMyAdmin para MySQL, hay un SQLiteManager para el SQLite Instálenlo... no se arrepentirán ;-)

Desde ahí haremos la importación de nuestro csv y poblaremos la tabla. En la imagen se presenta la tabla cp en la base de datos códigos, también podremos hacer queries y otras operaciones.

Con el Asistente de importación la tarea se facilita aún más:

Y listo, unas cuantas consultas:

5. Importación de bases de datos

Un último comentario: Para MySQL hay un script en Bash, mysql2sqlite.sh,que permite la importación de una base de datos de MySQL a SQLite el cual hace uso extensivo del Awk para lograr la importación. Un ejemplo de uso sería:

./mysql2sqlite.sh -u root -p'tupasswd' foo | sqlite3 database.sqlite

Aunque el ejemplo está hecho en SQLite, la importación a otros RDBM como MySQL es casi igual de fácil. Se pueden utilizar herramientas como el phpMyAdmin o algunos otras utilerías. Es el mismo caso para PostgreSQL u Oracle. 

Notas

*Por el momento no vamos a tratar hojas de cálculo Excel. Eso lo vere en un post  de cómo utilizar módulos, como el Spreadsheet::ParseExcel en Perl para crear scripts que nos permitan la conversión xls->csv. 

Dónde leer más de esto:

IBM developerWorks

LinuxJournal

Awk Guide <- En particular este es una de los mejores y más completas guías para el Awk

SQLite Guía breve <- Breve guía de opciones para la línea de comandos de sqlite3 

SQLite Manager-Plugin del Firefox <- SQLite Manager Excelente plugin para el Firefox y que permite importar ficheros csv con múltiples formatos

https://github.com/lazierthanthou <- Repo del desarrollador de SQLiteManger.