TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. PARTE 1: IMPORTAR LA INFORMACIÓN

Hola a todos ¿Cómo estáis?.

Llevo unos días preparando nuevo material para actualizar Excel Signum. Desde hace tiempo tengo ganas de abordar el tema de los grandes volúmenes de datos, del famoso «BIG DATA» y las posibilidades que nos puede ofrecer Excel (tanto sus funciones como VBA) para trabajar con estos datos.

Antes de comenzar me gustaría realizar algunas apreciaciones sobre este asunto:

– Existen múltiples herramientas y plataformas especializadas en el BIG DATA. En esta web vamos a ver algunos ejemplos que podemos utilizar para trabajar con Excel llegando a rozar sus limitaciones, pero con resultados efectivos. Es decir, no pretendo indicar con esto que Excel puede cubrir todas nuestras necesidades en lo que a BIG DATA se refiere, dado que contamos con limitaciones importantes que iré comentando a lo largo del POST.

– De la misma forma que Excel tiene sus limitaciones, también podemos contar con algunas funciones, herramientas y código VBA para llegar a resultados muy interesantes y dignos cuando estamos trabajando con grandes volúmenes de información.

– Existen varios complementos en Excel como Power Query y Power Pivot que nos pueden resultar muy interesantes y también facilitar mucho nuestro trabajo. No obstante, de estos recursos hablaré en futuros post.

– Dado que será un tema bastante largo y posiblemente «denso» (habrá mucho código), he decidido realizar el post en tres partes:

* La primera: dedicada a la importación masiva de datos a Excel.
* La segunda: dedicada a la extracción de información.
* La tercera: dedicada al análisis de los datos, con herramientas y funciones estadísticas. Data Mining.

Por lo tanto, vamos a comenzar con la primera parte: La importación masiva de la información.

Cuando hablamos de importación masiva de datos, podemos hacer referencia a muchos tipos de datos, formatos, tipos de archivo, etc. y cada uno con su técnica específica de importación. Para este ejemplo vamos a trabajar con un fichero plano, un TXT de ancho fijo.

Aunque ya he dedicado un post al tema de importar archivos TXT de ancho fijo:  IMPORTAR ARCHIVOS TXT DE ANCHO FIJO, para este ejemplo vamos a utilizar otra técnica que veremos a continuación. Pero antes necesitaré un archivo TXT en el que basar el ejemplo, y que mejor lugar para encontrarlo que el INE!

Buscando entre toda la información y tipos de encuestas, me ha parecido muy interesante esta: Encuesta Europea de Salud en España donde tenemos una gran cantidad de información que nos servirá para realizar todo el ejercicio.

En esta captura de pantalla podéis ver en el lugar en el que debemos descargar el archivo TXT, en la pestaña “Microdatos”  >  Encuesta 2014 > Fichero de Microdatos > Elegir Formato y elegimos Formato TXT en ZIP:

Una vez que lo hemos descargado, debemos descargar otro archivo importante, que se encuentra justo encima “Fichero de registro y valores válidos de las variables”.

Este fichero es imprescindible, dado que nos informará de la longitud de los campos que vamos a importar en el archivo PDF, el ancho fijo y el nombre de cada campo o variable así como su definición valor, es decir las especificaciones.

Por ejemplo, en la pestaña “Diseño de Registro” podemos ver el nombre de la variable, la longitud, la posición de inicio, la posición final y la descripción de la variable:

En la pestaña: Variables y valores, veremos los valores que pueden tener cada una de las variables:

Para esta primera parte solo vamos a necesitar de la pestaña “Diseño de Registro”: el nombre de la variable y la longitud. Estos campos los vamos a copiar a nuestro archivo de Excel en la pestaña «ESPECIFICACIONES» y añadiremos una nueva columna que vamos a denominar “TIPO FORMATO”:

También vamos a renombrar una nueva pestaña como «DATOS», que es donde mostraremos los datos que importemos.

Ahora os dejo el código que debemos pegar en nuestro editor VBA en un módulo estándar:

Sub IMPORTAR_TXT_ANCHO_FIJO()
'Definimos variables
Dim Ancho As Variant, Tipo As Variant, Titulo As Variant
Dim Archivo As String, nFilas As Integer
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Generamos los array necesarios para determinar
'ancho y tipo de datos a la hora de importar el TXT
With Sheets("ESPECIFICACIONES")
nFilas = Application.CountA(.Range("A:A"))
Titulo = Application.Transpose(.Range("A2:A" & nFilas).Value)
Ancho = Application.Transpose(.Range("B2:B" & nFilas).Value)
Tipo = Application.Transpose(.Range("C2:C" & nFilas).Value)
End With
'Abrimos cuadro de diálogo para seleccionar TXT
Filtro = " TXT(*.TXT),"
Archivo = Application.GetOpenFilename(Filtro)
'Si no seleccionamos nada, salimos del proceso
If Archivo = "Falso" Or Archivo = Empty Then
Exit Sub
End If
'Eliminamos los datos de la hoja "DATOS"
Call ELIMINA_DATOS
'Iniciamos el proceso
Sheets("DATOS").Select
With Sheets("DATOS")
'Indicamos encabezado de columnas
'que se encuenta en las especificaciones
.Range("A1:PL1").Value = Titulo
'Iniciamos Query y nos traemos la información del TXT
With .QueryTables.Add(Connection:= _
"TEXT;" & Archivo, Destination:=Range( _
"$A$2"))
.Name = "CONSULTA_1"
.AdjustColumnWidth = False
.TextFileParseType = xlFixedWidth
'Hacemos referencia al tipo de datos que queremos importar
'en este caso, número (1)
.TextFileColumnDataTypes = Array(Tipo)
'Indicamos el ancho de cada columna
'que se encuentra también en las especificaciones y que hemos
'convertido en un array, al igual que el tipo y el título
.TextFileFixedColumnWidths = Array(Ancho)
.TextFileTrailingMinusNumbers = True
'On Error Resume Next
.Refresh BackgroundQuery:=False
'On Error GoTo 0
End With
End With
Application.ScreenUpdating = True
End Sub

Para que la macro funcione correctamente, es necesario pegar esta otra a continuación:

Sub ELIMINA_DATOS()
'Definimos variables
Dim cnn As Object, table As Object
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Seleccionamos la hoja Datos
Sheets("DATOS").Select
With Sheets("DATOS")
'Borramos TODAS las conexiones que tenga el libro
For Each cnn In ThisWorkbook.Connections
cnn.Delete
Next cnn
'Borramos todas las tablas de la hoja activa
For Each table In .QueryTables
table.Delete
Next table
'Borramos todos los contenidos de la hoja activa
.Range(.Cells(1, 1), ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Delete Shift:=xlToLeft
.Range("A1").Select
End With
Application.ScreenUpdating = True
End Sub

Y ahora vamos a comentar algunas partes del código que me parece interesante. En primer lugar, para que podamos capturar el archivo TXT he incluido un cuadro de diálogo para seleccionarlo más cómodamente.

Dado estamos utilizando el método «QueryTables.Add«, es necesario que automaticemos las matrices que dan valor al ancho fijo y al tipo de formato de los datos que vamos a importar con la consulta.

Para eso, obtendremos las matrices de cada campo con los datos que hemos pegado anteriormente en la hoja ESPECIFICACIONES. Estos son los tres campos que necesitamos:

Titulo = Application.Transpose(.Range("A2:A" & nFilas).Value)
Ancho = Application.Transpose(.Range("B2:B" & nFilas).Value)
Tipo = Application.Transpose(.Range("C2:C" & nFilas).Value)

Y los vamos a utilizar en las siguientes líneas de código.

.TextFileFixedColumnWidths = Array(Ancho)
.TextFileColumnDataTypes = Array(Tipo)

Para el tipo de formato, indicar que estoy utilizando el 1, que se refiere a número. Si quisiéramos importar con formato texto sería el 2, (es importante decidir el formato que nos interesa, dado que posteriormente puede ser relevante para los cálculos).

Como también tenemos el nombre de todos los campos y los hemos pasado a una matriz, podemos pasar el rango a la primera fila de la hoja datos, indicando la última fila, es decir la columna 428 o PL, esto lo podemos automatizar, pero he preferido dejarlo así para mayor claridad.

.Range("A1:PL1").Value = Titulo

Por último, la macro ELIMINA_DATOS(), elimina la tabla que hemos importado anteriormente por si es necesario repetirla varias veces.

Es importante comentar que con esta macro, a diferencia de la que escribí en el post anterior, nos ahorramos 10 minutos en la importación. Tanto los métodos utilizados como la posibilidad de automatizar la carga de las matrices, son la forma más eficiente de trabajar con esta información en Excel.

No vamos a poder trabajar directamente con ADO, dado que tiene la limitación de los 255 campos y hacerlo mediante procesos for ralentizaría demasiado la tarea.

Una vez que ejecutamos la macro, esta es la información que obtenemos:

En total, tenemos 428 columnas y 22.843 filas, una cantidad bastante importantes de datos con la que podemos empezar a extraer información (aunque esto lo haremos en el siguiente post) 😉

Esta macro está pensada y diseñada para trabajar con versiones de Excel 2010 en adelante. No lo he probado en Excel 2007, pero debería funcionar correctamente. La extensión del archivo siempre ha de ser xlsm, dada la extensión de las columnas (superan las 255). De hecho, si estuviésemos trabajando en Access tendríamos la limitación de los 255 campos (incluso en Access 2016), sin embargo en Excel no tenemos ese problema.

Con los ajustes adecuados podremos realizar importaciones hasta los límites que nos asigne EXCEL, tanto en filas como en columnas, e incluso podríamos pensar en utilizar varias hojas para guardarnos tablas mucho más grandes.

Y este ha sido el primero de los tres post sobre trabajar con grandes bases de datos. Os dejo la información en Google Drive, dado que en WordPress no se permiten archivos TXT ni XLSM.

En el próximo post trataré el cómo vamos a extraer la información que nos interesa, y no voy a utilizar bucles, sino otra herramienta que nos va a venir muy bien y que estamos acostumbrados a usar de otra forma.

Descarga el archivo de ejemplo pulsando en: TRABAJAR CON GRANDES BASES DE DATOS EN EXCEL. IMPORTAR LA INFORMACIÓN