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.



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