Comparar listas con Excel y Access

Una tarea frecuente es comparar listas con Excel, ya sea para detectar valores duplicados o valores faltantes en una u otra lista.

Hay varias técnicas posibles al usar Excel para este tipo de tareas. Muchas de ellas las he mostrado en distintas notas en este blog (pueden leerse haciendo un clic a la etiqueta "Comparar Listas" en la nube de etiquetas).

Las técnicas más usuales incluyen usar Formato Condicional, fórmulas (con COINCIDIR, CONTAR.SI, etc.), e inclusive la poco valorada funcionalidad Datos-Consolidar). Existen también complementos (Add-ins)

El problema con estos métodos es que son poco prácticos, en particular si tenemos que comparar listas con centenas o miles de registros.

Podemos comparar listas de miles de registros con facilidad, seguridad e inclusive obtener los resultados en hojas de Excel con unos pocos clics. Todo lo que necesitamos es una herramienta presente en casi todo computador (o red) que tenga instalado el paquete de Office: Access.

Si, sin saber Access! Vamos a demostrarlo con un ejemplo. Supongamos dos listas, Lista A y Lista B, que contienen nombres de países y su PIB. Cada lista se encuentra en una hoja de un cuaderno Excel.

Nos piden crear los siguientes reportes:

1 – países en la Lista A que no se encuentran en Lista B;
2 – países en Lista B que no se encuentran en Lista A.

Para dificultar las cosas las listas no están ordenadas.


Primer paso: preparamos una base de datos en Access (si, no hace falta saber Access!, tengan confianza)
Abrimos Access y creamos la base de datos

cambiamos el nombre por defecto y elegimos la carpeta

Al terminar el proceso, veremos esto en la pantalla:

Access a creado una base de datos con una tabla vacía. Esta tabla no nos hace falta; al cerrarla Access la borrará.

Segundo paso: insertar las tablas de datos en la base de datos.

Para esta tarea accionamos la pestaña "Datos Externos" en la cinta de opciones (como ven, muy parecido a Excel) y en Importar y Vincular elegimos Excel

Al hacerlo se abre un diálogo que nos guiará en el proceso de importar los datos de las tablas de Excel a la base de datos de Access

Al apretar Aceptar se abre el asistente para importación de hojas de cálculo

Elegimos la hoja Lista A (luego repetiremos el proceso para Lista B) y apretamos "Siguiente"

Si no está marcado, señalamos la opción "Primera fila contiene encabezados...".

En el próximo paso podemos definir el tipo de dato que debe contener cada campo (columna). Para nuestra tarea no nos detendremos en esto y apretamos Siguiente.

En el paso siguiente podemos definir una clave principal para la tabla; tampoco esto es importante a los efectos de nuestra tarea y podemos sencillamente señalar la opción "Sin clave principal"

En el último paso podemos dar nombre a la tabla (por defecto Access usará el nombre de la hoja)

y al apretar Finalizar, Access creará la tabla.

El icono de la tabla aparece en la ventana de objetos (a la derecha). Un doble clic al icono abre la tabla.

Repetimos el proceso para la segunda tabla; al final de proceso tendremos una base de datos con ambas tablas

Tercer paso: crear las consultas.

Access tiene un asistente de para consultas que nos permite crear una consulta para encontrar los elementos faltantes en cada tabla en relación a la otra. Y si, nuevamente no necesitamos saber Access para hacerlo, sólo seguir las instrucciones del asistente.

En la cinta de opciones seleccionamos "Crear" y "Consultas—Asistente para consultas"

En el asistente elegimos la opción "Búsqueda de no coincidentes"

y apretamos Continuar

En los siguientes pasos seleccionamos qué tabla vamos a comparar con cual

En el próximo paso debemos señalar el campo común a ambas tablas (en nuestro caso País). Por lo general Access "adivina" cuál es el campo y todo lo que tenemos que hacer es apretar el botón con el icono <=>

El último paso es definir que campos queremos ver en la consulta, lo que hacemos señalando el campo y apretando el icono ">" (el icono ">>" selecciona todos los campos de una vez).

Apretamos Finalizar y "abracadabra"!!

Repetimos el proceso para comparas Lista B con A

Cuarto paso (opcional): exportar las consultas a hojas de Excel.

En este caso podemos bastarnos con ver los resultados en la ventana de la consulta. Pero en ciertos casos podemos querer exportar los resultados a hojas de Excel para posterior análisis o presentación. También aquí Access nos facilita la vida con el asistente de exportación de datos en la pestaña Datos Externos

Activamos la ventana de la consulta a exportar y definimos en el asistente la carpeta de destino, el formato de Excel, si queremos exportar la consulta con su formato y diseño (recomendado) y si abrir el archivo Excel al terminar el proceso (recomendado).

Todo lo que queda por hacer es apretar Aceptar.

Transferir datos de Excel a Access - tercera nota

En las notas anteriores vimos cómo transferir datos de Excel a Access manualmente y programáticamente.

La necesidad de usar Access surge como una de las soluciones posibles cuando queremos, por ejemplo, consolidar datos de varias hojas en una única base de datos para generar a partir de ella una tabla dinámica.
Si el total de filas a consolidar supera el límite de una hoja de Excel (hasta la versión 2003 incluida, 65536 filas) no podemos almacenar los datos consolidados en una única hoja.
Si trabajamos con Excel 2007 podemos almacenar más de un millón de filas en una hoja, lo cual nos exime de tener que usar herramientas externas a Excel. Pero como más del 50% de los usuarios usa versiones anteriores a Excel 2007, almacenar datos en Access es una buena solución.

En las soluciones que muestro en las notas mencionadas suponemos que Access está instalado en la máquina del usuario. Por supuesto, éste no es siempre el caso y en esta nota mostraremos como usar Access como recipiente para nuestros datos aún cuando no esté instalado en nuestra máquina.

Empecemos por aclarar que los archivos con formato .mdb no requieren la presencia de Access para poder ser utilizados. Estos archivos están asociados con el Microsoft Jet Database Engine, que está incluido en el paquete de Office. Si bien este componente ha sido abandonado por Microsoft, sigue siendo vigente por motivos de compatibilidad.

Nuestra meta es crear dos rutinas, una para crear el archivo “.mdb” y otra para almacenar los datos de la hoja u hojas Excel en la tabla.

Nuestro primer paso es crear una referencia a

* Microsoft Ext. ADO 2.5 for DDL Security (o posteriores).
* Microsoft ActiveX Data Object 2.5 Library (o posteriores).
en el editor de VB, en el menú Tools—References

Un detalle a tomar en cuenta es que las macros que mostraré más adelante están construidas para el ejemplo específico de esta nota y hay que editarlas para adaptarlas a otros usos.
En nuestro ejemplo tenemos un cuaderno Excel con tres hojas. Cada hoja contiene datos de tres distintos años (2005, 2006 y 2007) que queremos consolidar en una única hoja para construir a partir de ella una tabla dinámica.
La rutina para crear el archivo “.mdb” con una tabla para almacenar los datos es la siguiente:

Option Explicit

Const dataSource As String = "provider=microsoft.jet.oledb.4.0;" _
& "data source=d:\BaseDeDatos.mdb"
Const tableName As String = "datos_export"

Sub crearDB()
Dim catalog As ADOX.catalog
Dim new_table As ADOX.Table


'crear la base de datos

Set catalog = CreateObject("adox.catalog")
catalog.Create dataSource

' crear la tabla para contener los datos

Set new_table = CreateObject("adox.table")
new_table.Name = tableName
new_table.Columns.Append "mes", adDate
new_table.Columns.Append "pais"
new_table.Columns.Append "suma", adDouble
catalog.Tables.Append new_table

End Sub

En la parte superior del módulo, antes de la rutina “crearDB” hemos definido dos constantes. Lo hacemos de esta manera ya que también en la segunda rutina haremos uso de estas constantes.
Esta macro crea una base de datos Access (BaseDeDatos.mdb) con una tabla (datos_export)



Ahora tenemos que exportar los datos de la hoja activa al la tabla “datos_export”, lo que hacemos con esta macro:

Sub AgregarDatos()
' exporta los datos de la hoja activa a una tabla de Access
' este procedimiento es especifico para el ejemplo.
' Para otros usos hay que adaptarlo

Dim cn As ADODB.Connection, rs As ADODB.Recordset, fila As Long

Set cn = New ADODB.Connection
cn.Open dataSource

Set rs = New ADODB.Recordset
rs.Open tableName, cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
fila = 2 ' la primer fila en la hoja despues de los encabezamientos

Do While Len(Cells(fila, 1)) > 0
With rs
.AddNew
.Fields("mes") = Range("A" & fila).Value
.Fields("pais") = Range("B" & fila).Value
.Fields("suma") = Range("C" & fila).Value
.Update
End With
fila = fila + 1 ' next row
Loop
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub


Esta rutina es la misma que hemos usado en la segunda nota de la serie, adaptada a nuestro ejemplo.

Ahora activamos la hoja cuyos datos queremos exportar y corremos la macro para cada una de las hojas que queremos consolidar.

A esta altura de los acontecimientos tenemos un archivo Access con un tabla que contiene los datos de las hojas (podemos ver que el tamaño del archivo se ha incrementado)

Ahora podemos crear la tabla dinámica usando la opción Fuente de datos Externa



En el segundo paso creamos una nueva fuente de datos, que en nuestro ejemplo llamaremos “consolidarMdb” usando el driver de Access












Después de apretar Connect, apretamos OK con lo cual hemos creado la nueva conexión. De aquí en adelante seguimos los pasos del asistente tal como hemos mostrado en esta nota.

El resultado:

Technorati Tags: MS Excel

Transferir datos de Excel a Access – segunda nota.

En la nota anterior vimos como transferir datos de Excel a Access manualmente usando los mecanismos nativos de Access.
Otra alternativa es exportar los datos de Excel a Access programáticamente, es decir usando Vba.

Para nuestro ejemplo usaremos nuevamente el archivo de la nota anterior.

En la nota anterior usamos los mecanismos de Access para importar los datos de Excel.
Al realizar la tarea con Vba haremos el camino a la inversa, decir crearemos una conexión a Access desde Excel. Esto lo haremos usando objetos de la ADO library (ADO = ActiveX Data Objects).

Antes de sumergirnos en los vericuetos del código, una observación importante: para poder usar las funciones de ADO en Excel debemos primero crear una referencia con el menú Tools-Reference del editor de Vb





Como en el ejemplo anterior, empezamos por crear una base de datos que contiene una tabla a la cual queremos exportar los datos de Excel. Nuestra base de datos (bd1.mdb) contiene la tabla Ventas que por ahora está vacía



Sin entrar demasiado en detalles técnicos, podemos describir la tarea de la siguiente manera:

# - creamos una conexión a la base de datos

#- definimos en qué tabla de la base de datos queremos poner los datos

# - definimos un objeto que contenga los datos a transferir (recordset)

# - definimos donde se encuentran los datos a transferir (en nuestro ejemplo el cuaderno tiene dos hojas con datos)

# - transferimos los datos

He agregado la hoja Parámetros en la cual podemos definir donde se encuentra la base de datos, cuál es la tabla a la cual queremos copiar los datos y en qué cuaderno se encuentran. De esta manera, podemos controlar los parámetros del código con facilidad.


Como ven he agregado también la línea a partir de la cual hay que transferir los datos de la hoja. En general los datos empiezan en la fila 2 (la 1 está ocupada por los encabezamientos). También podemos elegir de qué hoja pasar los datos, con una lista desplegable en la celda B3.
El código de la macro es el siguiente

Sub ExcelaAccess_ADO()

Dim Conn As ADODB.Connection, RecSet As ADODB.Recordset
Dim fila As Long, primerFila As Integer, ultimaFila As Long, iX As Long
Dim dataSource As String, Tabla As String
Dim wsName As String


'definimos los parametros que seran usados por el codigo

dataSource = Sheets("parametros").[B1]
Tabla = Sheets("parametros").[B2]
wsName = Sheets("parametros").[B3]
primerFila = Sheets("parametros").[B4]


' establecemos la conexion a la base de datos

Set Conn = New ADODB.Connection
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & _
dataSource & ";"

' definimos un recordset
Set RecSet = New ADODB.Recordset
RecSet.Open Tabla, Conn, adOpenKeyset, adLockOptimistic, adCmdTable

ultimaFila = WorksheetFunction.CountA(Sheets(wsName).Range("A:A"))

For iX = primerFila To ultimaFila 'mientras que la celda la columna A
'tenga un valor transferimos la fila
With RecSet
.AddNew
.Fields("Factura") = Sheets(wsName).Cells(iX, 1).Value
.Fields("Fecha") = Sheets(wsName).Cells(iX, 2).Value
.Fields("Producto") = Sheets(wsName).Cells(iX, 3).Value
.Fields("Descripcion") = Sheets(wsName).Cells(iX, 4).Value
.Fields("Cantidad") = Sheets(wsName).Cells(iX, 5).Value
.Fields("Precio") = Sheets(wsName).Cells(iX, 6).Value
.Fields("Total") = Sheets(wsName).Cells(iX, 7).Value
.Update
End With
Next iX


RecSet.Close
Set RecSet = Nothing
Conn.Close
Set Conn = Nothing
End Sub



Este código debe ponerse en un módulo común de Vba.

Transferir datos de Excel a Access – primera nota.

Después de escribir la nota sobre cómo consolidar datos de más de dos cuadernos con MS Query, decidí que había llegado el momento de mostrar cómo podemos transferir datos de una hoja de Excel a una tabla de Access. Usar Access es una solución práctica cuando nuestra tabla de datos supera el límite de Excel (para las versiones 97-2003) de 65536 filas.

En esta nota mostraremos que no hay motivo para sentirse inhibido de usar Access, también si no estamos del todo familiarizados con su uso.

Básicamente hay dos caminos posibles para transferir los datos: manualmente y programáticamente (Vba). Esta nota se ocupa de cómo transferir datos de Excel a Access manualmente. Para poder hacerlo necesitamos, obviamente, que Access esté instalado en nuestra máquina (o servidor).

Para nuestro ejemplo consideraremos un cuaderno Excel con dos hoja. La primera contiene ventas del mes de abril y la segunda del mes de mayo. Por motivos de claridad usamos pocos datos.

El primer paso es abrir Access y crear una base de datos en blanco



En nuestro ejemplo aceptamos el nombre propuesto por Access (bd1), pero podemos darle cualquier otro nombre, preferentemente más significativo.

Una vez creada la base de datos veremos esta pantalla

Seleccionamos con un clic Tablas en el área de Objetos y abrimos el menú contextual (clic con el botón derecho del mouse); aquí seleccionamos la opción Importar.



En el diálogo que se abre elegimos Excel en Tipo de Archivo y seleccionamos el cuaderno que contiene la hoja que queremos importar.



En nuestro ejemplo Excel detecta que hay dos hojas en el cuaderno. Elegimos “abril” y apretamos Siguiente



Ahora debemos definir si la primer fila es contiene títulos. En caso afirmativo los títulos son usados como nombres de los campos de la tabla.



En este paso debemos definir donde guardar los datos. En nuestro caso no hemos creado ninguna tabla así que la opción es “en una nueva tabla”.

El próximo paso nos permite definir distintas opciones para los campos. En nuestro ejemplo, sencillamente seguimos adelante apretando “Siguiente”.

Ahora podemos definir claves principales (índices) para la tabla. Para nuestro ejemplo este paso no es crítico, pero en general es recomendable dejar la opción por defecto de Access.



En el último paso podemos definir el nombre de la tabla. Por defecto, Access propone el nombre de la hoja. Por lo general, como en nuestro ejemplo debemos cambiar el nombre a algo más significativo, como Ventas.

El último paso es apretar Finalizar. Al cabo de unos instantes Access nos informa que el proceso ha terminado (o fracasado si es que hemos cometido algún error o los datos no son los adecuados). Access ha creado ahora la tabla Ventas



que contiene los datos de la hoja



Ahora repetimos el proceso pero elegimos la hoja “mayo”.



En el tercer paso elegimos la tabla Ventas



Finalmente apretamos Finalizar. Los nuevos datos han sido agregados a los existentes en la tabla



Para totalizar y analizar los datos de base de datos podemos usar tablas dinámicas con la opción Fuente de datos externa

Technorati Tags: MS Excel