CRUZAR DOS TABLAS EN EXCEL USANDO SQL

Hola a todos!

Hace tiempo que no escribía una entrada, pero estos últimos días he estado bastante mal debido a una gastroenteritis que me ha dejado destrozado, y aún sigo en recuperación.

Aún así, he querido sacar tiempo para escribir un nuevo post. Esta vez me gustaría hablar sobre el cruce de archivos, algo tan corriente en Excel como el uso de función buscarv. Y es que realmente, lo que se pretende hacer cuando se cruzan archivos es buscar un valor y determinar si está o no en esa base de datos.

Existe una forma un poco más elaborada para realizar esta tarea, muy útil para trabajar con procesos definidos y con la misma estructura de datos. Por ejemplo, imaginad que tenéis la información de los empleados de unos grandes almacenes en dos momentos distintos, uno anterior y otro actual:

CRUZAR DOS TABLAS EN EXCEL USANDO SQL

Y ahora lo que queremos saber es, qué empleados están en la base de datos anterior y no en la actual (Bajas), y viceversa, es decir, (Altas). Estas dos consultas se puede realizar con SQL utilizando ADO:

Para las Altas:

obSQL = "SELECT [BBDD_ACTUAL$].[ID], [BBDD_ACTUAL$].[NOMBRE COMPLETO], [BBDD_ACTUAL$].[SECCION], 'NUEVO EMPLEADO' AS ESTADO " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE(([BBDD_ANTERIOR$].[ID]) IS NULL)"

Para las Bajas:

obSQL = "SELECT [BBDD_ANTERIOR$].[ID], [BBDD_ANTERIOR$].[NOMBRE COMPLETO], [BBDD_ANTERIOR$].[SECCION], 'BAJA' " & _
"FROM [BBDD_ANTERIOR$] LEFT JOIN [BBDD_ACTUAL$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE(([BBDD_ACTUAL$].[ID]) IS NULL)"

Efectivamente, con estos códigos podemos extraer la información requerida. Pero imaginad que queremos obtener los movimientos que han sucedido entre “Secciones”, es decir qué empleados han dejado una sección y qué empleados han entrado en una nueva sección. Obviamente, los empleados son los mismos, lo único que cambia es la sección.

Para ello, solo debemos modificar levemente el código:

Altas en una sección:

obSQL = "SELECT [BBDD_ACTUAL$].[ID], [BBDD_ACTUAL$].[NOMBRE COMPLETO], [BBDD_ACTUAL$].[SECCION], 'ALTA SECCION' " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE([BBDD_ACTUAL$].[SECCION]) NOT LIKE [BBDD_ANTERIOR$].[SECCION] "

Bajas en una sección:

obSQL = "SELECT [BBDD_ANTERIOR$].[ID], [BBDD_ANTERIOR$].[NOMBRE COMPLETO], [BBDD_ANTERIOR$].[SECCION], 'BAJA SECCION' " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE([BBDD_ANTERIOR$].[SECCION]) NOT LIKE [BBDD_ACTUAL$].[SECCION] "

Estos fragmentos de código solo son la consulta SQL específica. Para que funcione es necesario embeberla en entorno VBA. Os dejo aquí la primera de las cuatro consultas, no reproduciré el resto dado que ya las podéis ver en el archivo de descarga.

Sub ALTA()
Dim Dataread As ADODB.Recordset, obSQL As String
Dim cnn As ADODB.Connection, fin As Integer, milibro As String
Application.ScreenUpdating = False
fin = Application.CountA(Sheets("MOVIMIENTOS").Range("A:A"))
'Borramos datos de consultas anteriores
If fin > 0 Then Sheets("MOVIMIENTOS").Range("A1:D" & fin).Clear
'indicamos los parámetros de la consulta SQL
obSQL = "SELECT [BBDD_ACTUAL$].[ID], [BBDD_ACTUAL$].[NOMBRE COMPLETO], [BBDD_ACTUAL$].[SECCION], 'NUEVO EMPLEADO' AS ESTADO " & _
"FROM [BBDD_ACTUAL$] LEFT JOIN [BBDD_ANTERIOR$] ON [BBDD_ACTUAL$].[ID] = [BBDD_ANTERIOR$].[ID]" & _
"WHERE(([BBDD_ANTERIOR$].[ID]) IS NULL)"
'Obtenemos el nombre del libro
milibro = ThisWorkbook.Name
'Creamos la conexión ADO
Set cnn = New ADODB.Connection
With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "DATA SOURCE=" & Application.ActiveWorkbook.Path + "\" & milibro
.Properties("Extended Properties") = "Excel 8.0"
.Open
End With
Set Dataread = New ADODB.Recordset
With Dataread
.Source = obSQL
.ActiveConnection = cnn
.CursorLocation = adUseClient
.CursorType = adOpenForwardOnly
.LockType = adLockReadOnly
.Open
End With
Do Until Dataread.EOF
Dataread.MoveFirst
'pasamos información a la hoja movimientos
With Worksheets("MOVIMIENTOS")
.Cells(2, 1).CopyFromRecordset Dataread
'mostramos encabezados
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
.Cells(1, i + 1) = dfecha
Next
End With
Loop
'ejecutamos el resto de consultas
Call BAJA
Call ALTA_SECCION
Call BAJA_SECCION
Set Dataread = Nothing
Set cnn = Nothing
End Sub

Como podéis observar, después de ejecutar la primera consulta, ejecutamos el resto de consultas en el mismo proceso.

El resultado de ejecutar el código es el siguiente:

CRUZAR DOS TABLAS EN EXCEL USANDO SQL_1

Y ya tenemos un resumen detallado de los nuevos empleados, las bajas producidas y los cambios de sección (anterior y actual).

Aunque es un proceso un poco largo, si se trata de consultas que siempre tendrán la misma estructura y periodicidad, es el método adecuado. Resulta también muy interesante además su uso conjunto con Access o archivos de texto (sería necesario realizar cambios en la programación).

Os recuerdo que es necesario marcar la referencia Microsoft ActiveX Data Object 2.8 Library (o la que corresponda) para que todos los componentes funcionen correctamente.

Para poder generar los nombres y confeccionar vuestras propias bases de datos os remito al siguiente post

Descarga el archivo de ejemplo pulsando en: CRUZAR DOS TABLAS EN EXCEL USANDO SQL

¿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

8 pensamientos en “CRUZAR DOS TABLAS EN EXCEL USANDO SQL

  1. Pingback: REALIZAR VARIAS CONSULTAS SQL CON ADO EN LA MISMA MACRO | EXCEL SIGNUM

  2. buenas noches, he visto la consulta “CRUZAR DOS TABLAS EN EXCEL USANDO SQL” y me gustaría saber como hacer lo mismo pero que ambas tablas ( hojas de datos ) se encuentren en distintos archivos excel 2007. Hace semanas que vengo leyendo y leyendo blogs pero no logro establecer esta conexión.

    Tengo un archivo que utilizo como BASE y otro mas pequeño del cual diariamento necesito cruzarlo con la BASE y sólo Actualizar / Agregar los registros que difieren.

    La conexión la establezco por ODBC para excel y no por JET, ya que los datos en mi BASE se encuentran a partir de la fila 6 y sólo con ODBC logré leer los datos con sus nombres de campo sin problema.

    Set cn_Archivo = New ADODB.Connection
    cn_Archivo.Open “Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};” & “DBQ =” & strDB

    Esta es la consulta que estuve queriendo hacer entre ambos archivos, pero no lo logro hacer…. siempre me sale ” Error de sintaxis cerca de la clausula FROM ” pero no logro darme cuenta donde esta el error puntualmente.

    ‘consulta SQL archivo de actualizacion
    strSQL = “SELECT * FROM [INTRAWAY$A1:H10] tabla ”
    strSQL = strSQL & “LEFT JOIN [” & strSQL_Base & “,” & ConnectionString_base & “] base ”
    strSQL = strSQL & “ON tabla.MAC = base.SERIE ”
    strSQL = strSQL & “WHERE tabla.CLIENTEbase.CLIENTE_I ”
    strSQL = strSQL & “OR tabla.SERVICIObase.SERVICIO_I ”
    strSQL = strSQL & “OR tabla.TELEFONObase.TELEFONO_I ”
    strSQL = strSQL & “OR tabla.MODELNUMBERbase.MODELO_I ”
    strSQL = strSQL & “OR tabla.DOCSISVERSIONbase.DOCSIS_I ”
    strSQL = strSQL & “ORDER BY tabla.MAC, tabla.TELEFONO”

    Si me pueden ayudar con esto se los agradeceré muchísimo.

    Me gusta

    • Hola Andrés:

      Tendría que ver el archivo, así es complejo analizar cuál es el problema. No obstante, te recomiendo que verifiques las comillas dobles y que las variables que utilizas contienen la información de forma correcta.

      En el from, “tabla” sobra, no es necesario, ya estás especificando la hoja y el rango.

      Verifica el uso de las comillas dobles. Si tienes más errores, prueba a realizar esta tarea en Access y luego estudia el código SQL en el Access, te dará la pista del problema.

      Saludos.

      Me gusta

¿Te ha gustado?, Realiza un comentario.

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.