25 septiembre, 2023

USAR EXCEL PARA TRABAJAR CON BIG DATA

Hola a todos : ) Qué tal estáis?, seguro que bien!

Desde ya hace unos años, con el auge del denominado Big Data, y la necesidad de tratar enormes cantidades de información, han surgido multitud de plataformas, métodos y aplicaciones para gestionar esa ingente cantidad de datos (estamos hablando de billones de registros).

Excel y también Access, nos permiten trabajar con grandes bases de datos, pero cuando hablamos de Big Data, existen varias limitaciones que debemos tener muy en cuenta. En primer lugar, los límites de Excel en lo que a registros se refiere y en segundo lugar, tanto en Excel como en Access a la memoria disponible.

Es necesario reconocer las limitaciones de Excel. Es obvio que no podrá procesar la información de billones de registros en un tiempo razonable, simplemente no está programado para poder hacerlo. Pero esto no es motivo para pensar que Excel no puede trabajar en el mundo Big Data, siempre existe una alternativa, un método diferente que nos puede ayudar.

En el post de hoy os voy a demostrar cómo podemos trabajar con un archivo de texto de 1,41GB (contiene unos 7 millones de registros) y ser capaces de obtener información de su contenido. No voy a entrar hoy en técnicas de Data Mining, eso lo dejaré para una entrada posterior con los datos que consigamos extraer de la base de datos.

Lo más complicado para hacer pruebas con Big Data, es precisamente conseguir una base de datos aceptable y que sea de contenido público. Para este ejemplo he acudido a esta web DATA.GOV (es una web de política de datos abiertos donde además de multitud de bases de datos, también ofrecen información a nivel mundial de otras web con la misma política, es este enlace os podéis descargar la dirección de todas esas webs.

Pues bien, como necesitaba una buena base de datos, después de buscar bastante, decidí quedarme con esta: Crimes – 2001 to present, se trata de la relación de crímenes sucedidos en Chicago desde el año 2001 hasta la actualidad (nov de 2017):

USAR EXCEL PARA TRABAJAR CON BIG DATA

Elegí la descarga del archivo CSV y aunque sabía que no podría ver toda la información  porque supera el límite del 1.048.576 filas, sí me dejaría abrir el archivo para poder ver qué campos tenía y cuál era el delimitador que se usaba. Una vez finalizada la descargar y abierto el CSV esta es la información que aparecía:

USAR EXCEL PARA TRABAJAR CON BIG DATA1

Como podéis observar, en la primera línea de datos tenemos el nombre de los campos de nuestra base de datos y también sabemos que el delimitador utilizado es la coma «,» (si fuese otro tipo de delimitador sería necesario crear un archivo «schema.ini» indicando el carácter concreto).

Dado que no podemos importar toda esa información a nuestra hoja Excel (o en realidad sí, pero utilizando varias hojas, lo que haría bastante complicada nuestra explotación de datos posterior), lo más sencillo y efectivo es seleccionar aquellos campos que realmente nos interesan y traer esa información mediante una consulta (o varias).

Para este ejemplo, realmente ¿qué es lo que me interesa?, pues: El tipo de delito, la descripción del delito, el año en el que sucedió y el número de casos. Con estos parámetros obtendremos la información que queremos y será fácil de tratar dado que agruparemos por año y por tipo de delito.

Pues bien esto lo vamos a lograr con el siguiente código:

Sub EXCEL_BIG_DATA()
'Definimos variables
Dim cnn As Object, dataread As Object
Dim directorio As String, archivo As String
Dim encabezados As String, obSQL As String
Dim i As Long
'Creamos objetos ADO
Set cnn = CreateObject("ADODB.Connection")
Set dataread = CreateObject("ADODB.Recordset")
'Creamos variables para nombrar archivo y directorio
directorio = "D:\"
archivo = "Crimes_-_2001_to_present.csv"
'Componemos y abrimos la cadena de conexión
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & directorio
.Properties("Extended Properties") = "text;"" "
.Open
End With
'Componemos consulta SQL para extraer la información
obSQL = "SELECT `Primary Type` as `TIPO DE DELITO`, " & _
"Description as DESCRIPCION, YEAR(Date) as AÑO, count (`Primary Type`) as CASOS FROM [" & archivo & "] " & _
"WHERE Arrest like 'true' GROUP BY `Primary Type`, Description, YEAR (Date) "
'Guardamos resultado de la consulta en el recordset
With dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenDinamic
.LockType = adLockOptimistic
.Open
End With
'Pasamos los datos del recordset a la hoja activa
With ThisWorkbook.Sheets("Hoja1")
.Cells(2, 1).CopyFromRecordset dataread
For i = 0 To dataread.Fields.Count - 1
encabezados = dataread.Fields(i).Name
.Cells(1, i + 1) = encabezados
Next
End With
'Liberamos memoria
Set cnn = Nothing
Set dataread = Nothing
End Sub

Como podéis observar estoy utilizando ADO para conectarme al archivo de texto y traerme los datos que he especificado en la consulta, de esta forma no tengo que importarme todo el archivo y solo aquello que necesito.

Aunque hemos creado los objetos conexión y recorset, es recomendable marcar la referencia Microsoft ActiveX Data Object 2.8 Library (o la que corresponda) para que todos los componentes funcionen correctamente, sobre todo .CursorLocation.LockType. Ya sabéis, las referencias las activáis en VBA aquí:

exportar-una-tabla-o-consulta-de-access-a-excel-con-ado2

Obviamente, en la variable «directorio» debemos especificar el directorio donde se encuentra el archivo CSV o TXT, (en este ejemplo lo tengo en la unidad «D», en un pendrive) y en «archivo» es el nombre completo del archivo con la extensión incluida.

El resultado es el siguiente:

USAR EXCEL PARA TRABAJAR CON BIG DATA2

Después de casi un minuto la consulta nos devuelve la información que necesitamos. Es decir, ahora podemos trabajar en VBA y en Excel con datos de manera más efectiva y responder a preguntas como: ¿qué delitos han aumentado desde 2001 hasta 2017?, ¿cuáles han disminuido?, podríamos hacer un pronóstico en aquellos delitos más graves para años posteriores, podríamos conocer cuál es la droga que motiva más arrestos, etc, etc. En pocas palabras, podríamos tomar decisiones en base a datos fiables y cuantificables, pero esto lo haremos más adelante en otro post dedicado a Data Mining.

Realmente la importancia radica en todo el código, tanto en la configuración de la cadena de conexión, como en la composición de la consulta SQL, donde determinamos qué campos necesitamos y como queremos agruparlos, unirlos, etc.

Parece un proceso sencillo, pero es necesario escribir y pensar bien la sentencia SQL. En este ejemplo:

obSQL = "SELECT `Primary Type` as `TIPO DE DELITO`, " & _
"Description as DESCRIPCION, YEAR(Date) as AÑO, count (`Primary Type`) as CASOS FROM [" & archivo & "] " & _
"WHERE Arrest like 'true' GROUP BY `Primary Type`, Description, YEAR (Date) "

Seleccionamos el tipo de delito primario, la descripción( por ejemplo, en detenciones por narcóticos, aquellos que son por heroína, metanfetamina, etc), el año y contamos por el campo tipo de delito (en esta consulta que el campo Arrest sea igual a «true» y finalmente agrupamos por tipo de delito y por el año en el que se ha cometido.

El uso de las comillas invertidas » ` » se debe a que algunos nombres de campo tiene espacios en blanco y eso genera errores en SQL, para solventarlo, usamos las comillas invertidas.

La consulta podría ser mucho más compleja y extensa, pero para realizar este ejemplo creo que es suficiente, el objetivo es demostrar que se puede trabajar con una base de datos de «gigas» y superando los límites de Excel con tiempos razonables. No necesitamos importar el archivo a Excel, simplemente lanzamos consultas desde Excel y obtenemos el resultado, evitando problemas de espacio y eficiencia.

La idea es, «si es demasiado grande, trabaja con dimensiones más pequeñas».

Esta técnica tiene sus límites en la capacidad de memoria que tenga nuestro equipo, pero es de mucha utilidad para establecer procesos automáticos donde debemos extraer y analizar información.

No subiré el archivo CSV, lo podéis descargar en el enlace que os dejé al principio. En cuando a la macro, debéis especificar el directorio en el que hayáis guardado el archivo para que funcione correctamente y el nombre del archivo.

Los tiempos con los que obtendréis los datos van a variar según el equipo con el que trabajéis (memoria disponible, procesador, etc), en este ejemplo he utilizado mi portátil, pero con el equipo de sobremesa ha sido mucho más veloz.

Os dejo el archivo con la macro, con las referencias marcadas y preparado para realizar el ejemplo:

Descarga el archivo de ejemplo pulsando en: USAR EXCEL PARA TRABAJAR CON BIG DATA

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Comparte este post

6 comentarios en «USAR EXCEL PARA TRABAJAR CON BIG DATA»

  1. Buenos días:

    Cuando dices : «es recomendable marcar la referencia Microsoft ActiveX Data Object 2.8 Library (o la que corresponda)», en mi caso que utilizo Office 365, en Windows 10, 64 bits ¿ debería marcar Microsoft ActiveX Data Object 6.1 Library ?.

    Por otra parte cuando ejecuto esta macro me da error en .CursorType = adOpenDinamic (error de compilación, no se ha definido la variable).

    Te agradecería me aclararas estas dudas.

    Muchas gracias por tu esfuerzo y atención.

    1. Hola Carlos, debería funcionarte con la referencia Microsoft ActiveX Data Object 2.8 Library sin probremas. Mi equipo es win 10 a 64 y con Excel 2010 32bits (office profesional) y en el portátil es win 10 a 64 con Excel 2016 a 32 bits (tambien Office profesional).

      La otra referencia que comentas también te funcionaría sin problema (la acabo de probar ahora mismo.

      El motivo de error es porque no consigue asociar la referencia con el objeto ADO que debe realizar una vez que la referencia está marcada.

      Para descartar problemas de equipo y programa, prueba la macro en otro ordenador diferente con Excel (la versión que tengas disponible y sin que sea 365).

      Saludos!

  2. Hola Segu!

    No tenía idea que se podía hacer esto con VBA. Has probado el Power Query o ‘Get & Transform’ que se encuentra dentro del Excel 2016 para este tipo de casos? o tal vez el Power Pivot?

    Muy comúnmente leo los canales de Microsoft que recomiendan estas 2 herramientas para cuando se trata de grandes cantidades de datos o simplemente procesos de BI o ETL.

    Saludos!

    1. Hola Miguel:

      Claro que he probado Query, Pivot y demás herramientas, y suelo trabajar con ellas. En todas las versiones, ya sea como complementos o integradas.

      Hacer este ejercicio en VBA me interesa, sobre todo porque sigue la idea de esta web, que es la de transmitir conocimiento. Pero es que ademas resulta muy útil para integrar este método en procesos automatizados con muchos subprocesos en los que resulta más eficiente hacerlo todo con código y no tener que abrir otras herramientas. Además el poder definir la consulta en SQL abre un importante abanico de opciones a la hora de transformar la información de la manera que más nos convenga. Y poder luego programar algoritmos o secuencias de código para realizar minería de datos (que es lo que realmente interesa).

      Otro motivo es que en muchas empresas a veces solo tienen Excel para realizar el trabajo ( sin posibilidad de descargar complementos (ya sea porque está bloqueado por el administrador, por políticas de homologaciones, etc). En ese caso, se ofrece una alternativa muy útil al poder hacerlo en VBA.

      ¿qué se tienen que tener conocimiento de programación? pues claro, pero para eso publico estos post : )

      Un saludo!!

  3. Yo la verdad estoy con Miguel [como para discutirle en estos temas 😉 ]. De VBA no controlo mucho -ésto es, nada- y por eso PQ me parece una forma accesible de realizar y depurar consultas y reutilizarlas luego con un simple cambio en el origen de datos. Y además, de ese modo se nutre el módulo powerpivot de una manera más «consistente». También permite implementar secuencias de SQL.

    Resumen: voto por PQ por su versatilidad y sencillez en el aprendizaje.

  4. Hola asa80:

    Y es normal que que tengas tu punto de vista, así debe ser, y es así como se construye (con opiniones diferentes y resultado diversos). 🙂 Estos comentarios siempre son bienvenidos por este blog!.

    Como comentaba, el mismo trabajo que se hace desde VBA en este ejercicio, se puede hacer en PowerQuery y también en Access (entre otros), esta es una alternativa más, y también el hecho (que ya me lo han comentado varios lectores), que muchos usuarios en sus empresas solo tiene Excel (raramente Access), normalmente por políticas de seguridad, etc, etc. y asumiendo esta premisa me pareció interesante hacerlo en VBA.

    Muchas gracias por comentar, a ambos (que veo que os conocéis).

    Ah! VBA es sencillo de aprender, y aquí hay muchos ejemplos, ejercicios y archivos de prueba, te invito a que, si te interesa, le eches un vistazo.

    Saludos!!

Si te ha gustado o tienes alguna duda, puedes dejar aquí tu comentario.

Este sitio web utiliza cookies para que usted tenga la mejor experiencia de usuario. Si continúa navegando está dando su consentimiento para la aceptación de las mencionadas cookies y la aceptación de nuestra política de cookies, pinche el enlace para mayor información.plugin cookies

ACEPTAR
Aviso de cookies