Enumere todos los archivos de una carpeta en Excel con Power Query - Consejos de Excel

Tabla de contenido

Ver video

Pregunta de hoy: Marcia necesita obtener una lista de todos los archivos PDF de facturas de impuestos de una carpeta en una hoja de cálculo de Excel. Esto es fácil de hacer si está utilizando Excel 2016 en una PC con Windows utilizando las nuevas herramientas Obtener y transformar datos.

Si tiene Excel 2010 para Windows o Excel 2013 para Windows, tendrá que descargar el complemento gratuito Power Query de Microsoft. Vaya a su motor de búsqueda favorito y escriba "Descargar Power Query" para encontrar el enlace actual. (A Microsoft le encanta cambiar las URL cada trimestre y mi increíble chico web odia cuando nuestros enlaces están desactualizados, así que ni siquiera intentaré poner un enlace aquí).

El video a continuación le mostrará los pasos completos, pero aquí está la descripción general:

Comience desde una hoja de trabajo en blanco
Datos, obtener datos, de archivo, de carpeta
Busque la carpeta
Haga clic en Editar en lugar de Cargar
Abra el menú desplegable de filtros en el tipo de archivo y elimine todo lo que no sea un PDF
Abra el filtro en la carpeta y elimine las subcarpetas de basura
Conserve solo el nombre de archivo y la carpeta: haga clic con el botón derecho en cada encabezado de columna y seleccione Eliminar
Arrastre el título Carpeta a la izquierda del título Archivo. Esto permite que la combinación funcione.
Seleccione ambas columnas. Haga clic en un encabezado. Shift + Click en el otro encabezado.
Elija Agregar columna, Combinar columnas, Escriba un nuevo nombre para la columna. Haga clic en Aceptar.
Haga clic con el botón derecho en el encabezado de la nueva columna y elimine otras columnas
Inicio, Cerrar y Cargar
La parte asombrosa … puede actualizar la consulta más tarde. Haga clic en el icono Actualizar en el panel Consultas y conexiones.

Si bien Power Query es increíblemente poderoso, esta es una de mis tareas favoritas. Con frecuencia quiero ejecutar una macro VBA en cada archivo de una carpeta. Obtener una lista de todos los archivos PDF en una carpeta es un buen punto de partida.

Ver video

Transcripción del video

Aprenda Excel del podcast, episodio 2181 - ¡Listar archivos de carpetas en Excel!

Oye, bienvenido de nuevo al netcast, soy Bill Jelen. La pregunta de hoy, alguien tiene una lista de archivos PDF de facturas de impuestos en una carpeta, y necesita obtener la lista de todos esos nombres de archivos en Excel. Muy bien, y una forma de hacerlo es escribirlos todos o copiar y pegar desde el Explorador de Windows, pero hay una gran herramienta que puede resolver esto. Y mi primera pregunta fue "Bueno, ¿qué versión de Excel tienes?" Porque si tiene Excel 2016, ¡tendrán esta nueva y sorprendente funcionalidad llamada "Obtener y transformar datos"! Ahora, en Office 365, está en el lado izquierdo, creo que en la versión original de Excel 2016 estaba en el tercer grupo, está bien, así que busque Get & Transform. Si está en Excel 2010 o Excel 2013 para Windows, puede descargar Power Query y tendrá su propia pestaña con estas cosas exactas.

Ahora echemos un vistazo rápido a esta carpeta, está bien, acabo de crear una carpeta falsa aquí con algunos datos falsos. Verá que hay archivos de Excel en esta carpeta y PDF, solo quiero los PDF y también hay algunas subcarpetas, no quiero estos PDF, solo quiero los PDF en la carpeta principal. Entonces C: Presupuestos, voy a copiar eso y luego volver aquí a Excel, y vamos a decir que queremos obtener datos, de un archivo, de una carpeta completa, así, y luego escriba la ruta de la carpeta allí, o use el botón Examinar, cualquiera de ellos. Y cuando obtenga esta primera pantalla, definitivamente querrá Editar, y ahora estamos en el editor de Power Query.

Muy bien, mi objetivo aquí, no necesito el Contenido, así que voy a hacer clic derecho y decir Eliminar esa columna. Ahí está mi lista de archivos, solo quiero archivos PDF, así que si hay algo que no sea PDF, solo quiero archivos PDF, haga clic en Aceptar, puedo ver que son solo los archivos PDF. Ah, y luego mire aquí, vea, ahora están extrayendo cosas solo de la carpeta original y de la carpeta Garbage, así que abro esto y desmarco todo lo que no es la carpeta original. Muy bien, ahora tengo una pequeña lista agradable, y esta lista es, ya sabes, 9 registros, pero en la vida real, apuesto a que probablemente tengan, ya sabes, docenas o cientos de estos. Muy bien, no necesito más cosas ahora, así que puedo hacer clic con el botón derecho y Eliminar esas columnas.

Muy bien, ahora lo que realmente necesito aquí es que necesito la ruta de la carpeta y el nombre del archivo juntos. Muy bien, voy a tomar FolderPath y arrastrarlo hacia la izquierda y soltarlo allí, y luego el paso mágico aquí: en Excel normal tendríamos que hacer una concatenación para esto, pero lo que voy a hacer es, voy a fusionar columnas. Así que voy a Agregar columna y elegir Combinar columnas, el separador será Ninguno, la nueva columna se llamará Nombre de archivo y haga clic en Aceptar, está bien, así que tenemos el nombre de la carpeta, la barra y el nombre del archivo. , como eso. Ahora, eso es lo único que necesitamos, así que voy a hacer clic con el botón derecho y decir Eliminar las otras columnas, y finalmente Inicio, Cerrar y cargar, y obtendremos una hoja nueva con nuestros datos. Bien, ahora viene como una tabla, así que voy a copiar esto, Ctrl + C,y luego venir aquí a donde realmente quería los datos aquí, y Pegar valores especiales, haga clic en Aceptar. Ahora ya no es una tabla, son solo mis datos puros, así, y ahora, esto es lo realmente hermoso de esto.

Así que configuramos esto una vez, y vaya, tomó menos de 3 minutos configurarlo, pero regresemos a la carpeta de Presupuestos y muevamos algunas cosas. Tomemos uno de estos registros de basura y lo copiaremos en la carpeta principal, Ctrl + V, de acuerdo, ahora hay más cosas aquí, hay 10 archivos PDF en lugar de 9. Si vengo aquí a donde está la consulta, y en el lado derecho de la pantalla, en Consultas y conexiones, es posible que tenga que hacer esto más ancho, ya hice el mío más ancho, verá nuestros presupuestos con 9 filas cargadas. Voy a hacer clic en el pequeño icono Actualizar aquí, y muy rápidamente Presupuestos ahora tiene 10 filas cargadas. Entonces, está recogiendo los nuevos registros, lo configura una vez y luego podrá actualizar para obtener los nuevos datos.

Bueno, este es el punto del podcast donde normalmente les pido que compren mi libro, pero en cambio hoy les voy a pedir que compren este libro “M is for (DATA) MONKEY” de Ken Puls y Miguel Escobar. Un libro INCREÍBLE que le enseñará todo sobre el uso de Power Query o Get & Transform Data, todo lo que aprendí sobre Power Query lo aprendí en este libro.

Bien, termine este episodio: nuestro objetivo es cómo importar una lista de nombres de archivo a Excel, si tiene Excel 2016, puede usar el nuevo Obtener y transformar datos. Si no tiene 2016, pero tiene una versión real de Excel ejecutándose en Windows, puede descargar el complemento gratuito de Power Query que es para Excel 2010 o Excel 2013. No funcionará en su teléfono Android, o su iPad, o su iPhone, o Surface RT, o su Mac, correcto, es solo para versiones de Windows de Excel. Entonces, vamos a comenzar desde una hoja de trabajo en blanco, Datos, Obtener datos, Desde archivo, Desde carpeta, ingrese el nombre de la carpeta o Examinar, asegúrese de hacer clic en Editar en lugar de Cargar. Y luego en el Filtro, filtre por el tipo de archivo para deshacerse de cualquier cosa que no sea un PDF, filtre por el nombre de la carpeta para deshacerse de todas las subcarpetas basura. Conserve solo el nombre del archivo y la carpeta,así que haga clic con el botón derecho en esos otros y diga Eliminar columna, luego arrastre el encabezado de la carpeta a la izquierda del archivo, que permite que la combinación funcione. Seleccione ambas columnas, luego en la pestaña Agregar columna elija Combinar columnas, escriba un nuevo nombre, haga clic en Aceptar y haga clic con el botón derecho en esa nueva columna y Eliminar las otras columnas, Inicio, Cerrar y Cargar, y le dará su lista. Lo sorprendente es que puede actualizar la consulta más tarde usando este ícono Actualizar en Consultas y conexiones.puede actualizar la consulta más tarde utilizando este icono Actualizar en Consultas y conexiones.puede actualizar la consulta más tarde utilizando este icono Actualizar en Consultas y conexiones.

Bueno, oye, quiero darte las gracias por pasar, ¡nos vemos la próxima vez para otro netcast de!