24 septiembre, 2023

REALIZAR CONSULTAS SQL CON ADO EN ARCHIVOS CSV INMENSOS

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.

Donate Button with Credit Cards

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

Comparte este post

4 comentarios en «REALIZAR CONSULTAS SQL CON ADO EN ARCHIVOS CSV INMENSOS»

  1. 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.

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

  3. 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?

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