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!!

Anuncios

APLICAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

Hola a todos 🙂

Espero que os vaya muy bien!. En el post de hoy vamos a trabajar con ADO para dar formato de fecha a una cadena de texto.

El tema de las fechas aunque puede parecer sencillo, siempre es susceptible de complicarse. Y en muchas ocasiones el problema se debe al formato con el que llegan los datos que nos envían. En el post de hoy vamos a trabajar uno de esos formatos.

Vamos a ilustrarlo con un ejemplo, imaginad que abrís una petición en la red a favor del control de horarios y ruidos de los locales de ocio nocturno (¡todo un reto!). Y la aplicación que ha recogido los datos os envía un archivo con la siguiente información:

DAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

Efectivamente, los campos NOMBRE, PETICIÓN y FIRMA no tienen nada de particular, pero el campo FECHA DE FIRMA debería contar con un formato de fecha y si embargo nos llega como un texto. Esto es un problema, dado que a priori no vamos a poder trabajar fácilmente con este tipo de fechas y es más, necesitamos exportar algunas de las columnas del fichero a otra aplicación para generar otro tipo de informaciones.

Hemos elegido ADO para transferir la información de la hoja BASE a la hoja INFORMACIÓN y será en ese proceso en el que vamos a utilizar SQL para formatear las fechas y también controlar aquellas celdas que se encuentren vacías.

Para hacer el trabajo, os dejo esta macro que es capaz formatear solo las celdas que contienen la fecha:

Sub CONEXION_SQL_FECHAS()
'Declaramos variables
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection
Dim Fin As Integer, i As Long, MiLibro As String, Tit As String
'Desactivamos actualización de pantalla
Application.ScreenUpdating = False
'Eliminamos datos de hoja INFORMACION anteriores
Sheets("INFORMACION").Select
With Sheets("INFORMACION")
'Eliminamos datos de la consulTa SQL anterior
Fin = Application.CountA(.Range("A:A"))
If Fin > 0 Then .Range("A2:D" & Fin).ClearContents
'Realizamos consulta SQL, y componemos un string para crear los datos y darle formato de fecha
obSQL = "SELECT [BASE$].[NOMBRE], [BASE$].[PETICION], [BASE$].[FIRMA], " & _
"IIF(NOT ISNULL([BASE$].[FECHA DE FIRMA]),CDATE(MID([BASE$].[FECHA DE FIRMA],1,2) & '/' & MID([BASE$].[FECHA DE FIRMA],3,2) & '/' & MID([BASE$].[FECHA DE FIRMA],5,4)),NULL) AS [FECHA DE FIRMA] " & _
"FROM [BASE$] "
MiLibro = ActiveWorkbook.Name
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Grabamos la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Pegamos datos y añadimos encabezados
Do Until Dataread.EOF
Dataread.MoveFirst
.Cells(2, 1).CopyFromRecordset Dataread
For i = 0 To Dataread.Fields.Count - 1
Tit = Dataread.Fields(i).Name
.Cells(1, i + 1) = Tit
Next
Loop
'Por seguridad formateamos la columna FECHA DE FIRMA a fecha
.Columns("D:D").NumberFormat = "m/d/yyyy"
'liberamos y desconectamos variables
Dataread.Close: Set Dataread = Nothing
cnn.Close: Set cnn = Nothing
End With
Application.ScreenUpdating = True
End Sub

Como podéis observar,  usamos ADO para importar la información de una hoja a la otra, y en el proceso aprovechamos para realizar los cambios y controles necesarios. En concreto, en esta sentencia SQL:

"IIF(NOT ISNULL([BASE$].[FECHA DE FIRMA]),CDATE(MID([BASE$].[FECHA DE FIRMA],1,2) & '/' & MID([BASE$].[FECHA DE FIRMA],3,2) & '/' & MID([BASE$].[FECHA DE FIRMA],5,4)),NULL) AS [FECHA DE FIRMA]

Donde controlamos que solo sean formateadas las celdas que contienen datos usando un IIF que valida si son nulas. Si no lo son, componemos una nueva cadena de texto creando la fecha y aplicamos la función CDate que nos devolverá una expresión tipo fecha, ¡Justo lo que queremos!.

Una vez ejecutada la macro, el resultado es el siguiente:

DAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA1

Este es un ejemplo muy concreto donde los datos a formatear deben ser texto, de hecho, cuando no nos envían la información con formato de fecha, casi siempre viene así.

Antes de finalizar, os recuerdo la necesidad de marcar en las referencias la librería de ADO Activex Data Objects 2.8 Library, es importante que lo hagáis, de lo contrario, la macro no va a funcionar:

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

Y eso es todo, espero que os resulte de utilidad a la hora de trabajar con fechas en Excel 🙂

Descarga el archivo de ejemplo pulsando en: APLICAR FORMATO DE FECHA A UNA CADENA DE TEXTO USANDO ADO EN VBA

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

Donate Button with Credit Cards

¡¡Muchas gracias!!

EXTRAER REGISTROS ÚNICOS CON UNA CONSULTA SQL USANDO DISTINCT

Hola a todos 🙂

Espero que todo vaya bien!. Llevo unos días con ganas de escribir este post, pero la verdad es que me habéis enviado bastantes consultas, y ya sabéis que lo primero es atender las dudas de los lectores y luego escribir las entradas del blog.

El post de hoy surge por una consulta en la que se me pedía una macro para extraer registros únicos de una hoja a otra. Así como para extraer los registros únicos dentro de la misma hoja podemos usar el filtro avanzado, y así lo publique en su momento: EXTRAER REGISTROS ÚNICOS CON FILTRO AVANZADO EN VBA, para pasarlo a otra hoja debemos utilizar otro tipo de código, de hecho podemos utilizar diferentes métodos.

Hoy veremos uno de ellos, utilizaremos ADO y una consulta SQL en la que incluyendo la palabra clave DISTINCT podremos extraer los registros únicos a otra hoja.

Vamos con el siguiente ejemplo, imaginad que tenemos nuestra hoja de Excel con una serie de registros duplicados:

EXTRAER REGISTROS ÚNICOS CON UNA CONSULTA SQL USANDO DISTINCT

Y queremos pasar a la hoja UNICOS todos los registros SIN duplicados. Para ello vamos utilizar el siguiente código:

Sub CONSULTA_SQL_UNICOS()
'Definimos las variables
Dim Dataread As ADODB.Recordset, obSQL As String, Res As String
Dim cnn As ADODB.Connection, i As Integer, MiLibro As String
'Limpiamos hoja con los registros únicos
Eliminar = Application.CountA(Worksheets("UNICOS").Range("A:A"))
If Eliminar > 0 Then Worksheets("UNICOS").Range("A1:GG" & Eliminar).ClearContents
'realizamos consulta SQL incorporando la palabra clave Distinct
obSQL = "SELECT distinct * FROM [DATOS$] "
'Guardamos el nombre del libro activo
MiLibro = ActiveWorkbook.Name
'Realizamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Procedemos a grababar los datos de la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
Do Until Dataread.EOF
Res = obRes & Dataread.Fields(0).Value & " " & Dataread.Fields(1).Value
Dataread.MoveFirst
'Copiamos los datos a la hoja UNICOS
With Worksheets("UNICOS").Select
Worksheets("UNICOS").Cells(2, 1).CopyFromRecordset Dataread
End With
'Grabamos los nombres de cada encabezado de columna
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
dfecha = CDate(Dataread.Fields(i).Name)
Else
dfecha = Dataread.Fields(i).Name
End If
Worksheets("UNICOS").Cells(1, i + 1) = dfecha
Next
Loop
End Sub

Ya sabéis que para este tipo de método es necesario activar la referencia Microsoft ActiveX Data Object 2.8 Library en el editor de VBA:

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

Siguiendo con el código, lo realmente importante es que en la consulta SQL estamos utilizando la palabra clave DISTINCT, la cual elimina los registros que se encuentran duplicados. El resto del código es el que solemos utilizar en esta web para este tipo de consultas.

El resultado de aplicar la macro es el siguiente:

EXTRAER REGISTROS ÚNICOS CON UNA CONSULTA SQL USANDO DISTINCT1

Como podéis ver, ya tenemos nuestros datos sin duplicados.

Y esto es todo, en próximos post, publicaré otra forma más sencilla de extraer registros únicos. Espero que os sea de utilidad, como siempre os dejo la macro:

Descarga el archivo de ejemplo pulsando en: EXTRAER REGISTROS ÚNICOS CON UNA CONSULTA SQL USANDO DISTINCT

 

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNIÓN

Hola a todos:

Hace unos días recibí una consulta acerca de cómo agrupar la información de varias hojas en una hoja específica del mismo libro.

Aunque tengo publicaciones con macros para agrupar información de varios libros,  el método utilizado fue mediante bucles (For – next). Para esta petición utilizaré otro método, trabajaré con ADO y realizaré una consulta de UNION para consolidar la información de las hojas seleccionadas.

Dado que el usuario tendrá siempre la misma estructura en la información de esas hojas y además serán tres, esta macro será perfecta para este trabajo. Con ADO conseguiremos mayor rapidez en la ejecución del código y, si cabe mayor claridad.

Veamos las tres hojas que vamos a consolidar, las llamaré TABLA1, TABLA2 y TABLA3 y la hoja que recibirá toda la información se llamará CONSOLIDADO:

La estructura sería esta:

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNION

Tal como podéis ver, cada una de las tablas tiene la misma estructura que la que muestro arriba. Un ID, Nombre, Estudios, Inglés, Vehículo, Provincia y Edad.

Antes de poner la macro, me gustaría volver a incidir en que es imprescindible que las tablas tengan las mismas columnas con los mismos nombres. Si esto no es así, la macro no funcionará.

Ahora sí, ya podéis pegar el siguiente código en un módulo estándar:

Sub CONSULTA_SQL_UNION()
'Definimos las variables
Dim Dataread As ADODB.Recordset, obSQL As String, Res As String
Dim cnn As ADODB.Connection, i As Integer, MiLibro As String
'Limpiamos hoja que consolida, CONSOLIDADO
Eliminar = Application.CountA(Worksheets("CONSOLIDADO").Range("A:A"))
If Eliminar > 0 Then Worksheets("CONSOLIDADO").Range("A1:GG" & Eliminar).ClearContents
'realizamos consulta SQL de UNION y no tenemos en cuenta los registros nulos
obSQL = "SELECT * FROM [TABLA1$] WHERE NOT [TABLA1$].[ID] IS NULL UNION " & _
"SELECT * FROM [TABLA2$] WHERE NOT [TABLA2$].[ID] IS NULL UNION " & _
"SELECT * FROM [TABLA3$] WHERE NOT [TABLA3$].[ID] IS NULL"
'Guardamos el nombre del libro activo
MiLibro = ActiveWorkbook.Name
'Realizamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & MiLibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
'Procedemos a grababar los datos de la consulta
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
Do Until Dataread.EOF
Res = obRes & Dataread.Fields(0).Value & " " & Dataread.Fields(1).Value
Dataread.MoveFirst
'Copiamos los datos a la hoja CONSOLIDADO
With Worksheets("CONSOLIDADO").Select
Worksheets("CONSOLIDADO").Cells(2, 1).CopyFromRecordset Dataread
End With
'Grabamos los nombres de cada encabezado de columna
For i = 0 To Dataread.Fields.Count - 1
If IsDate(Dataread.Fields(i).Name) Then
dfecha = CDate(Dataread.Fields(i).Name)
Else
dfecha = Dataread.Fields(i).Name
End If
Worksheets("CONSOLIDADO").Cells(1, i + 1) = dfecha
Next
Loop
End Sub

Antes de ejecutar el código debéis verificar que tenéis la referencia: Microsoft ActiveX Data Object 2.8 Library. En este archivo no es necesario que la marquéis (ya la he marcado yo), pero si pegáis el códido en un libro nuevo, sí que la tendréis que marcar.

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

El resultado de aplicar la macro es el siguiente:

CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNION1

Y ya tenemos los registros de las hojas especificadas en la hoja CONSOLIDADO. En este código si existen duplicados, es decir dos filas o más idénticas, importará solo uno, y también si existen elementos nulos (null) no los tendrá en cuenta. Si queremos que tuviese en cuenta los duplicados, es decir, que los importase, debemos poner “ALL” después de UNION, de esta forma estaremos especificando que unimos todo (incluidos duplicados):

obSQL = "SELECT * FROM [TABLA1$] WHERE NOT [TABLA1$].[ID] IS NULL UNION ALL " & _ "SELECT * FROM [TABLA2$] WHERE NOT [TABLA2$].[ID] IS NULL UNION ALL " & _
"SELECT * FROM [TABLA3$] WHERE NOT [TABLA3$].[ID] IS NULL"

Obviamente, sobre los nulos, no vamos a querer que se cuelen filas en blanco o con caracteres extraños, por eso resulta imprescindible incluir en la sentencia el famoso Not Is Null 🙂

Y esto ha sido todo, os dejo el archivo de ejemplo:

Descarga el archivo de ejemplo pulsando en: CONSOLIDAR HOJAS DE UN LIBRO CON UNA CONSULTA SQL DE UNIÓN