He recibido una consulta mas que buena. El departamento de sistemas de la empresa en donde trabaja mi lector periódicamente genera una serie de reportes, los cuales deben ser analizados en Excel. El principal problema que se plantea es que esos reportes pueden contener cientos de miles (y a veces superar el millón) de registros. Nuestro querido Excel admite hasta 65536 filas, salvo en las versiones 2007 o 2010, que superan el millón y también el número de columnas se extiende hasta mas allá de las 16.000. Pero claro, como ya he dicho varias veces..... me gustaría tener una PC (o Mac) tan poderosa que corra convenientemente semejante archivo. Y mas aún si luego debemos aplicar Funciones o Tablas Dinámicas a tal cantidad de registros, desde Excel.
Entonces la solución, me parece, viene de la mano de una herramienta espectacular: generar Tablas Dinámicas a partir de archivos de texto externos.
Obviamente que no tenía muchas ganas de escribir y generé una tabla de tan solo tres campos... disculpen la vagancia, pero realmente no es importante. Aquí lo primordial es llevar ese TXT a una Tabla Dinámica, omitiendo la migración, ordenamiento, limpieza, etc, etc, de esos datos en Excel.
Como primer medida guardamos el TXT en un directorio que luego recordemos. Abrimos Excel y nos vamos al menú "Datos / Informes de Tablas y Gráficos Dinámicos", acción que nos presentará este formulario en pantalla:
seleccionamos la opción "fuente de datos externa", ya que los datos se encuentran fuera del libro actual, en un archivo de texto.
Presionamos "Siguiente".
Ahora damos a "obtener datos", como muestra la siguiente figura:
El formulario que llamamos contiene los orígenes de datos presentes en el sistema. Por lo general viene por defecto un par: dBase, Excel y MS Access, los cuales son las fuentes de donde comunmente obtenemos los datos externos. Vemos que no hay opción para archivos de texto, así que seleccionaremos la opción :
y damos en "aceptar".
Observen bien como completé la info requerida:
1) le damos un nombre conveniente al nuevo origen de datos, para ubicarlo facilmente en un futuro.
2) selecciono el tipo de controlador (driver) que Excel necesitará para entenderse correctamente con el archivo de texto. En este caso: Microsoft Text Driver.
Luego hacemos click en el botón "Conectar" y el asistente continuará solicitando datos:
debemos destildar el checkbox "usar directorio actual", para luego hacer click sobre "seleccionar directorio" e indicarle a Excel en que carpeta se encuentra nuestro TXT. en mi caso: C:\USERS\DAMIAN\DOCUMENTS
Aceptamos y el primer formulario debería quedarnos así:
aceptamos.
Recordarán que al inicio de este proyecto solo teníamos a dBase, Excel y Access como posibles orígenes de datos. Bueno, ahora se agregó el origen que terminamos de crear:
excelente. aceptamos nuevamente.
Y ahora se viene el típico asistente para la importación de datos. (aunque cuidado, recordemos que esto no es una importación: estamos llevando directamente el TXT a una tabla dinámica). Mostraré a continuación una serie de imágenes para ilustrar convenientemente cada paso:
selecciono el archivo y presiono el botón " > ", para volcar todos los campos. "siguiente"
si bien en este ejemplo no lo utilizaré, este paso es un herramienta fantástica: nos permitirá filtrar la información trayedo, por ejemplo, los datos de una sucursal entre dos fechas, los importes mayores a $1000, etc, etc, etc. Una consulta 100% personalizable. No tocamos nada y "siguiente"
aqui Excel nos permite ordenar los datos. "siguiente"
dejamos seleccionada la primer opcion. si lo deseamos, podemos guardar la consulta, la cual lo hará bajo la extensión ".dqy" (query-->consulta). "finalizar"
Y retornamos al inicio, pero noten como ahora aparece la leyenda "Se han recuperado campos de datos":
Presionamos "siguiente" y tendremos en pantalla el formulario que nos solicita el lugar donde se creará la tabla dinámica:
Damos en finalizar (aunque podríamos presiona sobre "diseño" y configurar desde allí la tabla dinámica) y se nos presentará este panorama:
Excel.... cada día te quiero mas. Tengo el TXT vinculado a una Tabla Dinámica
Ahora resta acomodar los campos, lo cual haremos clickeando sobre cada uno de ellos (sin soltar) y arrastrándolos a la ubicación deseada. En este caso: sobre la columna A arrojé el campo Sucursal ; Fecha en la fila 1 y en el panel central ("coloque datos aqui") hice lo propio con Importe. El resulatado:
Excel... cada día te quiero mas.
Si este resultado no es lo que esperábamos, tenemos la posibilidad de acomodar los campos de otra forma (y aquí muchachos: a probar hasta que salga =) )
otra opción
y otra mas. la configuración dependerá de nuestras necesidades, lo bueno es saber sobre la infinita posibilidad de agrupamientos que tenemos a mano, mostrando la misma información de muchas formas distintas.
Es bueno saber también que contamos con un "personalizador gráfico" excelente a la hora de darle ciertos formatos mas atractivos a nuestras tablas. Seleccionamos la TDy nos vamos al menú "formato / autoformato", siendo viable optar por cualquiera de los formatos de tabla disponibles (excel 2003)
Y nuestra TD obviamente será otra:
ojo: cada formato afecta al criterio de campos que hayamos impuesto. por eso es conveniente probar con varios, hasta lograr el balance justo.
Bien mi estimado lector, creo que ya estamos en condiciones de analizar con todas las herramientas de Excel un archivo de texto con cientos o millones de registros. Aqui es bueno resaltar que: el archivo Excel pesa tan solo 20 KB, no necesitamos importar datos, no requerimos de distribuir cientos de miles de registros en distintos libros / hojas, etc, etc. Las ventajas son impresionantes., aunque obviamente dependeremos del archivo externo como fuente de datos. Pero aquí también se nos brinda otra herramienta espectacular: agreguemos un par de registros a nuestro TXT y luego desde la barra de herramientas de la tabla dinámica hagamos click sobre "actualizar" ( ! ):
y sí, pasó lo que ya intuyen: todos los datos agregados/modificados o eliminados se reflejan automáticamente en nuestra tabla dinámica. un lujo.
Y si analizan los pasos mostrados en este post se darán cuenta que practicamente podemos utilizar cualquier origen de datos existente... o crearlo.
Salu2.xlsx
Comentarios
Publicar un comentario