La importancia del tema es evidente. Una hoja en Excel Clásico puede contener hasta 65536 filas, lo cual puede ser una limitación crítica si tenemos que analizar grandes cantidades de datos. Si bien Excel 2007 y 2010 han extendido este límite más allá del millón de filas, no creo que un cuaderno con semejante cantidad de datos sea una alternativa razonable.
Excel viene provisto con un mecanismo que le permite conectarse con fuentes de datos externas. Hemos visto este mecanismo en acción en algunas de las notas sobre el uso de MS Query y la importación de datos externos a hojas de Excel.
En esta nota mostraremos como crear una tabla dinámica a partir de datos remotos, es decir, que no se encuentran en el cuaderno que contiene la tabla dinámica.
Estos datos remotos pueden estar en archivos de distinto tipo como Access (.mdb), Texto (.csv, .txt), Excel y otros. Si la fuente de datos aparece en la lista de Excel, el proceso es sencillo. Pero en ciertos casos la fuente no existe y debemos crearla.
En nuestro caso vamos suponer que tenemos los datos en un archivo de texto tipo .csv (comma separated values). En Excel Clásico empezamos el proceso abriendo un cuaderno Excel y usando el menú Datos-Informe de Tablas y Gráficos Dinámicos elegimos la opción Fuente de datos externa. Al apretar el botón Siguiente se abre el diálogo para ubicar la fuente de los datos
En nuestro caso podemos ver que el tipo de datos que queremos usar no figura en la lista. En este caso usamos la opción "Nuevo origen de datos"
En la ventanilla superior ponemos el nombre que queremos dar a la nueva fuente (archivoTXT o cualquier otro que crean conveniente), en la ventanilla 2 elegimos el controlador (driver) indicado para el tipo de datos y finalmente apretamos el botón Conectar.
En el nuevo formulario que se abre ubicamos el archivo que queremos que sirva de base a nuestra tabla dinámica
Si el directorio no coincide con el que aparece en el formulario, quitamos la señal de la casilla "Use current directory" para poder elegir la ubicación indicada.
Finalmente apretamos OK y Aceptar. Con este hemos creado una nueva fuente de datos
El próximo paso es elegir el archivo que contiene los datos
Y seguimos apretando Aceptar hasta llegar a la etapa final
Aquí señalamos la opción "Devolver datos a Microsoft Excel" y Aceptar, lo que no lleva de regreso al asistente de tablas dinámicas (por si no se dieron cuenta, hasta ahora hemos trabajado en el MS Query)
El próximo paso nos lleva al conocido formulario de ubicación de la tabla dinámica en la hoja
Al apretar Aceptar se creará la tabla dinámica
En Excel 2007 hay algunas diferencias, por lo que mostraremos dos caminos. Excel 2007 nos permite usar la interfaz de tablas dinámicas de Excel Clásico usando el atajo de teclado Alt+T+B
A partir de aquí procedemos como mostramos más arriba.
Otra alternativa es comenzar el proceso en la pestaña Datos – Obtener datos externos-de otras fuentes-MS Query
Esto abre el diálogo del MS Query para elegir el origen de datos, tal como sucede con Excel Clásico.
Impresionante la entrada, que además son las que me gustan (importación de datos); una pregunta, para practicar he creado a partir de una hoja excel un archivo .csv (nunca había experimentado con este tipo de archivos); el archivo .csv si lo abro es un archivo normal, como el de una hoja de cálculo; creía por lo que vi en internet que cuando la abrían era algo estilo un .txt separado por comas.
ResponderBorrarCuando abrís un archivo de tipo .csv Excel lo abre en una hoja sin más trámite. Si bien funciona como un cuaderno de Excel, si no cambiás el tipo a .xls, será guardado como .csv, no como .xls. Si abrís un archivo de tipo .txt Excel abre primero el asistente de importación de archivos de texto lo que te permite controlar como el proceso de importación.
ResponderBorrarPodés fijarte en este nota sobre importación de archivos de texto.
Ok, gracias por la respuesta y abusando un poco y en relación al link que me pusiste: ¿Cuál sería la diferencia entre "DATOS-OBTENER DATOS EXTERNOS-IMPORTAT DATOS" Y "DATOS-OBTENER DATOS EXTERNOS-NUEVA CONSULTA DE BASE DE DATOS"?.
ResponderBorrarSaludos de nuevo, Jorge.
Al usar la opción "nueva consulta de base de datos", activás al complemento MS Query que te permite trabajar con los datos de varias tablas. En el blog hay varias notas sobre el tema, podes verlas haciendo una búsqueda con la palabra Query.
ResponderBorrarCon la opción "importar datos" sólo extraés los datos de la fuente y los insertás en la hoja de Excel.
Hola Jorge, felicitaciones primero por el Blog; al grano: tengo excel 2007 y quiero conectar con archivo txt, pero no separados por coma, sino por pipe "|", al hacer la conexion paso a paso funciona, pero no reconoce los saltos de campos, al momento de configurar la conexión ODBC voy a definir formato de texto y le digo que considere el pipe como separador de campo, pero al terminar la configuracion me dice que no se puede guardar lo atributos de tabla y se cae la consulta.
ResponderBorrarMe interesa esta forma de accesar datos ya que trabajo con tablas sobre 250 mil registros descargados de un servidor Web, y prefiero la versatilidad de Excel antes que Access, pero me limita la capacidad del PC.
Hola Marco Antonio,
ResponderBorrarse puede hacer pero la explicación excede el marco de un comentario. Estaré publicando una nota sobre el tema en breve.
Gracias Jorge, este es el primer comentario que hago pero leo tu Blog desde hace bastante tiempo. reitero las felicitaciones y estaré atento a la nueva entrada.
ResponderBorrarhola jorge, excelente articulo. estuve buscando en tu pagina algo sobre tablas dinamicas tipo CUBOS, pero no encontre. si tienes algo sobre esto, favor dejarnos saber.
ResponderBorrargracias
Bien, supongo que te referís a usar cubos como fuente de datos para una tabla dinámica. Esto es posible tanto en Excel 2003 como en Excel 2007. Sin embargo la posibilidad de crear cubos ha sido cancelada en Excel 2007.
ResponderBorrarPor otro lado Excel 2007 tiene siete nuevas funciones que permiten extraer datos de un cubo y colocarlos en una hoja de Excel.
Hasta ahora no he tratado el tema de crear cubos con Excel. No creo que sea algo muy en uso por el usuario promedio.
Excelente entrada y post. Mi más sincera enhorabuena.
ResponderBorrarUn saludo a todos.
Jorge:
ResponderBorrarCuando agrego una variable como filtro me tira el siguiente error, a que se debe esto?, es una limitante de excel?
“El campo tiene más de 10.000 elementos bajo uno o más elementos principales. Sólo se mostrarán los primeros 10.000 elementos en cada elemento principal”
saludos,
marcia
Si, es una limitación. ¿Qué versión de Excel estás usando?
ResponderBorrarBuenos dias. una pregunta en relación a crear una tabla dinámica sobre Obtener datos externos. Sé que dándo a doble click en cualquier posición de la tabla se crea una hoja con el detalle pero si quisiera modificar algo que quedase plasmado en la tabla dinámica ¿como lo haría? ¿donde está el detalle que alimenta la tabla dinámica?
ResponderBorrarUn saludo y gracias
La tabla se alimenta de los datos externos y los cambios tienen que realizarse allí, en la fuente. Al crear una tabla dinámica Excel genera una instancia oculta (cache) de los datos. Esto permite mostrar el detalle de un dato al hacer un doble click. Lo que ves es una copia, por llamarlo así, de los datos originales.
ResponderBorrarMuchas gracias por la respuesta. Pero hay algo que no entiendo, si ésto fuera así, si enviara ese fichero a un destinatario por email no podría abrirlo puesto que los datos de origen no los tendría él. Sin embargo sí puede, ¿será que los datos quedan almacenados en el excel aunque sea de una manera codificada?
ResponderBorrarAsí es; Excel “fotografía” todos los datos y los guarda en una memoria especial llamada Cache. Por eso podemos, por ejemplo, borrar los datos de origen y el reporte dinámic seguirá funcionando. Te sugiero leer esta nota del blog.
ResponderBorrarmi archivo .csv está limitado por ";" y no por ",". cuando hago la conexión a la base de datos no me crea bien las columnas. Hay alguna forma sin cambiar el archivo .csv de que te haga bien la transformación?. gracias por adelantado.
ResponderBorrarMª carmen
El separador del formato .csv no es necesariamente el ;. Puede ser tambien "," o inclusive un espacio.
ResponderBorrarLo que hay que asegurarse es la presencia del driver.
Hola, antes de nada, mil gracias por compartir tu sabiduria.
ResponderBorrarLlevo varios años usando el MsQuery y me acaba de surgir un "problema" que me está volviendo loco.
El caso es que traigo de otro fichero excel vía MsQuery una tabla, en esa tabla una de las columnas tiene valores numéricos y de texto y sus celdas están definidas como texto, al importar los datos, los valores de dicha columna me aparecen como texto, no se pueden sumar (evidentemente eligiendo aquellos que son numéricos) y en la celda aparecen a la izquierda.
Si entro en una celda y pulso entrar automáticamente se me alinean a la derecha y ya se pueden tratar como número, el problema es que son casi 15000 registros y además, cada vez que actualizo los datos vuelven a "ponerse" como originariamente.
Muchas gracias por adelantado.
Un saludo
Hola, el MSQuery tiene sus (muchas) limitaciones y una de ellas es que no podemos transformar los datos dentro del Query. Pero si se puede hacer con el Power Query (si trabajas con Excel 2010 0 2013).
ResponderBorrarTe invito a leer mi serie de notas sobre Power Query haciendo un clic sobre el "Power Query" en la nube de etiquetas.
SI no puedes usar el Power Query una solución puede ser esta:
1 - creas el query y pasas los datos a la hoja de Excel
2 - si Excel no lo ha hecho, conviertes el rango en Tabla
3 - agregas una columna con la formula =B2*1 (suponiendo que B es la columna que contiene los números formados como texto y que la tabla comienza en la fila 2, la fila 1 contiene los encabezamientos). En la nueva columna todos los valores son numéricos.
4 - Al agregar valores al archivo de origen, todo lo que hay que hacer es actualizar la tabla, lo que hara correr el Query y a su vez ampliara la columna auxiliar para los nuevos valores.
tengo un problema con el msquery,,al momento de cargar un archivo.csv, los campos me aparecen en un solo registro,, es decir no puedo seleccionar algunos de ellos, el resultado me arroja el primer campo mostrado,, eso me lo hace diferente en dos maquinas,, es decir un excel si lo procesa correcto y otro no,, estoy hablando de la misma version de excel 2010 ,, es alguna configuracion ??,,, saludos
ResponderBorrarNo me queda claro lo de "los campos me aparecen en un solo registro[. Te sugiero que me mandes una captura de pantalla para que pueda hacerme una idea más precisa (fijate en el enlace Ayuda, en la parte superior del blog).
ResponderBorrarHola Jorge Felicidades x tu Blog es de gran utilidad, estoy en una nueva etapa en mi carrera profesional y tengo que trabajar bases de mas de 1 millón de registros, excel y access no me dan. No tengo permisos para instalar el el pc de la compañía programas diferentes y acostumbraba a trabajar con sql y power pivot en experiencias anteriores, seria de gran ayuda tus sugerencias. mil gracias
ResponderBorrardiegoatuservicio@gmail.com
Access y Power Pivot pueden trabajar con varios millones de filas, estando limitados sólo por la memoria disponible. Access tiene una limitación de 2 GB, si no me equivoco.
ResponderBorrar