Hola a todos:
De nuevo este fin de semana me apetecía seguir programando y probando cosas. En el post de hoy voy a trabajar con una base de datos (o mejor dicho) un archivo CSV de una extensión considerable (1,41GB). En el pasado ya había trabajado con esta base de datos y otras similares, cuando no existía ni Power Query, ni BI ni ninguna otra herramienta de este tipo, únicamente Excel y VBA.
Hoy de nuevo me he puesto a realizar programación y tratar los datos con ADO pero con un archivo CSV.
La información para este ejemplo la podéis encontrar aquí: Crimes – 2001 to present, se trata de la relación de crímenes sucedidos en Chicago desde el año 2001 hasta la actualidad. Y la podéis descargar aquí.(dado el volumen, tarda un poco).
Este es el contenido del archivo:

Los campos que tenemos a nuestra disposición son estos:
ID |
Case Number |
Date |
Block |
IUCR |
Primary Type |
Description |
Location Description |
Arrest |
Domestic |
Beat |
District |
Ward |
Community Area |
FBI Code |
X Coordinate |
Y Coordinate |
Year |
Updated On |
Latitude |
Longitude |
Location |
Y ahora para poder lanzar consultas sobre esta base de datos debemos tener en cuenta dos cosas:
- El tamaño de la base de datos.
- El tamaño de la respuesta de los datos.
Dado estamos haciendo pruebas, voy a escribir 3 consultas en SQL que voy a programar con ADO:
obSQL = "SELECT `Primary Type` as `TIPO DE DELITO`, " & _
"Description as DESCRIPCION, YEAR(Date) as AÑO, count (`Primary Type`) as CASOS FROM [" & Dir(narchivos) & "] " & _
"WHERE Arrest like 'true' GROUP BY `Primary Type`, Description, YEAR (Date) "
Aquí seleccionamos el tipo de delito, la descripción, el año y agrupamos y contamos por tipo de delito y año.
obSQL1 = "SELECT `Primary Type` as `TIPO DE DELITO`, " & _
"Description as DESCRIPCION, YEAR(Date) as AÑO, count (`Primary Type`) as CASOS FROM [" & Dir(narchivos) & "] " & _
"WHERE Arrest like 'true' and Year (Date)> '2010' and Year (Date)< '2012' GROUP BY `Primary Type`, Description, YEAR (Date) "
En esta segunda consulta únicamente me interesan los datos del año 2011, así que filtramos por esa fecha.
obSQL2 = "SELECT `Primary Type` as `TIPO DE DELITO`, " & _
"Description as DESCRIPCION, YEAR(Date) as AÑO, count (`Primary Type`) as CASOS FROM [" & Dir(narchivos) & "] " & _
"WHERE Arrest like 'true' and `Primary Type`='ASSAULT' and Description = 'AGGRAVATED: HANDGUN' GROUP BY `Primary Type`, Description, YEAR (Date) "
En la tercera consulta, necesito sacar los datos por tipo de delito «ASALTO» y con el agravante de «REVOLVER».
Y el código que vamos a utilizar es el siguiente:
Sub EXTRAER_DATOS_CSV()
'Definimos variables
Dim cnn As Object, dataread As Object, nHoja As Integer
Dim directorio As String, archivo As String
Dim encabezados As String, obSQL As String, obSQL1 As String, obSQL2 As String
Dim i As Long
'Creamos objetos ADO
Set cnn = CreateObject("ADODB.Connection")
Set dataread = CreateObject("ADODB.Recordset")
'Seleccionamos archivo CSV
narchivos = Application.GetOpenFilename("Excel Files (*.csv*), *.csv*", _
Title:="Seleccionar archivo")
'Si no hay selección salimos de la rutina
If narchivos = FALSE Then Exit Sub
'Indicamos el mensaje a mostrar
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & Split(narchivos, "\")(0)
.Properties("Extended Properties") = "text;"" "
.Open
End With
nHoja = 1
'Componemos primera 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 [" & Dir(narchivos) & "] " & _
"WHERE Arrest Like 'true' GROUP BY `Primary Type`, Description, YEAR (Date) "
'Componemos segunda consulta SQL para extraer la información
obSQL1 = "SELECT `Primary Type` As `TIPO DE DELITO`, " & _
"Description As DESCRIPCION, YEAR(Date) As AÑO, count (`Primary Type`) As CASOS FROM [" & Dir(narchivos) & "] " & _
"WHERE Arrest Like 'true' and Year (Date)> '2010' and Year (Date)< '2012' GROUP BY `Primary Type`, Description, YEAR (Date) "
'Componemos segunda consulta SQL para extraer la información
obSQL2 = "SELECT `Primary Type` As `TIPO DE DELITO`, " & _
"Description As DESCRIPCION, YEAR(Date) As AÑO, count (`Primary Type`) As CASOS FROM [" & Dir(narchivos) & "] " & _
"WHERE Arrest Like 'true' and `Primary Type`='ASSAULT' and Description = 'AGGRAVATED: HANDGUN' GROUP BY `Primary Type`, Description, YEAR (Date) "
'Iniciamos loop de consultas
For Each consulta In Array(obSQL, obSQL1, obSQL2)
With dataread
.Source = consulta
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
'Pasamos los datos del recordset a la hoja activa
With ThisWorkbook.Sheets("Hoja" & nHoja)
.Select
.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
nHoja = nHoja + 1
dataread.Close
Next consulta
'Liberamos memoria
Set cnn = Nothing
Set dataread = Nothing
End Sub
Y aquí ya tenemos la consulta y código preparado para usar. Como podéis observar, lo que hacemos es realizar la consulta directamente sobre la tabla tratando de que la respuesta no exceda los límites de nuestra versión de Excel.
Es obvio que tiene limitaciones y tarda tiempo en devolver una respuesta, pero es eficaz a la hora de generar y aplicar la consulta. He probado a descomponer la tabla en Query y rápidamente ha saltado el error de falta de memoria, en pivot lo mismo y supongo que en BI igual (no he probado).

Este es el ejemplo de la primera consulta.
Y esto es todo, os dejo el archivo para que lo examinéis y si es necesario lo podáis usar.
Descarga el archivo de ejemplo pulsando en: REALIZAR CONSULTAS SQL CON ADO EN ARCHIVOS CSV INMENSOS
¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.
¡¡Muchas gracias!!
Mediante la suscripción al blog, la realización comentarios o el uso del formulario de contacto estás dando tu consentimiento expreso al tratamiento de los datos personales proporcionados según lo dispuesto en la ley vigente (LOPD). Tienes más información al respecto en esta página del blog: Política de Privacidad y Cookies
Que conste que soy un fan incondicional tuyo, y que me parece muy interesante la consulta SQL que presentas. Sobre todo para ordenadores con limitaciones.
Pero te diré, que me he bajado los datos, los he cargado en power query y tal cual sin hacerles nada los he volcado en una tabla dinámica y aunque ha tardado un ratillo me ha permitido hacer el análisis sin problemas. Una vez aparece la tabla dinámica puedes realizar consultas sin retraso alguno.
He utilizado la versión 365 de excel y tengo un ordenador Intel Core i5 7400 CPU 3 GHZ con 16 GB de RAM. No es malo pero tampoco es un formula uno
Mi excel es para 64 bits que creo puede ser algo a tener en cuenta.
Buenas, precisamente un Excel para 32 bits no permite cargar la tabla en query. Fallo de memoria. Y todas mis versiones son en 32 bits. Pero bueno, era el trasteo del fin de semana. Saludos Adolfo!
Hola, excelente la explicación, tengo un inconveniente al ejecutarlo, falla en la linea .open , sera que me falta habilitar alguna referencia dentro de herramientas en VB?
verifica que tienes todas las referencias activas en el editor vba. Saludos