Lista alla filer i en mapp i Excel med Power Query - Excel Tips

Innehållsförteckning

Kolla på video

Dagens fråga: Marcia måste få en lista över alla PDF-filer för skattefakturor från en mapp till ett Excel-kalkylblad. Det här är lätt att göra om du använder Excel 2016 på en Windows-dator med de nya Get & Transform Data-verktygen.

Om du har Excel 2010 för Windows eller Excel 2013 för Windows måste du ladda ner gratis Power Query-tillägget från Microsoft. Gå till din favoritsökmotor och skriv "Ladda ner Power Query" för att hitta den aktuella länken. (Microsoft älskar att byta webbadresser varje kvartal och min fantastiska webbkille hatar när våra länkar är inaktuella, så jag tänker inte ens försöka sätta en länk här.)

Videon nedan visar de fullständiga stegen, men här är översikten:

Börja från ett tomt kalkylblad
Data, få data, från fil, från mapp
Bläddra till mappen
Klicka på Redigera istället för Ladda
Öppna rullgardinsmenyn för filtyp och ta bort allt som inte är en PDF
Öppna filtret i mappen och ta bort eventuella undermappar
Behåll endast filnamn och mapp - högerklicka på varje kolumnrubrik och välj Ta bort
Dra mapprubriken till vänster om filrubriken. Detta gör att sammanslagningen kan fungera.
Välj båda kolumnerna. Klicka på en rubrik. Skift + Klicka på den andra rubriken.
Välj Lägg till kolumn, slå samman kolumner, skriv ett nytt namn för kolumnen. Klicka på OK.
Högerklicka på rubriken för den nya kolumnen och ta bort andra kolumner
Hem, stäng & ladda
Den fantastiska delen … du kan uppdatera frågan senare. Klicka på ikonen Uppdatera i panelen Frågor och anslutningar.

Medan Power Query är otroligt kraftfull är det här en av mina favorituppgifter. Jag vill ofta köra en VBA-makro på varje fil i en mapp. Att få en lista över alla PDF-filer i en mapp är en bra utgångspunkt.

Kolla på video

Videoutskrift

Lär dig Excel från podcast, avsnitt 2181 - Lista mappfiler i Excel!

Hej, välkommen tillbaka till netcast, jag heter Bill Jelen. Dagens fråga, någon har en lista över skattefakturapdf-filer i en mapp, och de måste få en lista över alla dessa filnamn i Excel. Okej, och ett sätt att göra det är att skriva dem alla, eller kopiera och klistra in från Windows Explorer, men det finns ett bra verktyg som kan lösa detta. Och min första fråga var "Vilken version av Excel har du?" För om du råkar ha Excel 2016 kommer de att ha denna fantastiska nya funktionalitet som heter "Get & Transform Data"! Nu, i Office 365, är det på vänster sida, jag tror att det i originalversionen av Excel 2016 var i den tredje gruppen, okej, så leta bara efter Get & Transform. Om du är i Excel 2010 eller Excel 2013 för Windows kan du ladda ner Power Query så får du din egen flik med exakta saker.

Låt oss nu ta en snabb titt på den här mappen, okej, jag skapade just en falsk mapp här med några falska data. Du ser att det finns Excel-filer i den här mappen och PDF-filer, jag vill bara ha PDF-filer och det finns också några undermappar, jag vill inte ha dessa PDF-filer, jag vill bara ha PDF-filer i huvudmappen. Så C: Budgetar, jag ska kopiera det och sedan komma tillbaka hit till Excel, och vi kommer att säga att vi vill hämta data, från en fil, från en hel mapp, så här och sedan skriv mappvägen där inne, eller använd knappen Bläddra, antingen en. Och när du får den här första skärmen vill du definitivt redigera, och nu är vi i Power Query-redigeraren.

Okej, så mitt mål här, jag behöver inte innehållet, så jag ska högerklicka och säga Ta bort den kolumnen. Det finns min lista med filer, jag vill bara ha PDF-filer, så om det finns något som inte är en PDF, vill jag bara ha PDF-filer, klicka på OK, jag kan se att det bara är PDF-filerna. Ah, och titta sedan hit, se, nu drar de in saker från bara den ursprungliga mappen och från Garbage-mappen, så jag öppnar det här och avmarkerar allt som inte är den ursprungliga mappen. Okej, så nu har jag en fin liten lista, och den här listan är, ni vet vad, 9 skivor, men i verkligheten slår jag vad om att de antagligen har, du vet, dussintals eller hundratals av dessa. Okej, jag behöver inga andra saker nu, så jag kan högerklicka och ta bort de kolumnerna.

Okej, nu behöver jag verkligen mappsökvägen och filnamnet tillsammans. Okej, så jag ska ta FolderPath och dra den åt vänster och släppa den där, och sedan det magiska steget här: I vanlig Excel skulle vi behöva göra en sammankoppling för det här, men vad jag ska göra är att jag Jag ska slå samman kolumner. Så jag ska lägga till kolumn och välja Sammanfoga kolumner, avgränsaren kommer att vara Ingen, den nya kolumnen kommer att kallas FileName och klicka på OK, okej, så vi har mappnamnet, snedstrecket och filnamnet , sådär. Nu är det faktiskt det enda vi behöver, så jag kommer att högerklicka och säga Ta bort de andra kolumnerna och sedan äntligen Hem, Stäng och ladda, så får vi ett helt nytt ark med våra data. Okej nu, det kommer in som en tabell, och så ska jag bara kopiera det här, Ctrl + C,och kom sedan hit dit jag verkligen ville ha data här uppe och klistra in speciella värden, klicka på OK. Nu är det inte en tabell längre, det är bara mina rena data, så, och nu, här är det riktigt vackra med det här.

Så vi ställde in det här en gång, och wow, det tog mindre än 3 minuter att ställa in, men låt oss komma tillbaka till den budgetmappen och låt oss flytta några saker. Låt oss ta en av dessa Garbage-poster, och vi kopierar den till huvudmappen, Ctrl + V, okej så nu finns det fler saker här, det finns 10 PDF-filer istället för 9. Om jag kommer hit där frågan är, och över på höger sida av skärmen, i frågor och anslutningar, kanske du måste göra detta bredare, jag har redan gjort min bredare, du kommer att se våra budgetar med 9 rader laddade. Jag ska klicka på den lilla uppdateringsikonen här, och mycket snabbt har nu 10 rader laddats. Så det hämtar de nya posterna, du ställer in det här en gång och då kan du bara uppdatera för att få de nya uppgifterna.

Det här är punkten i podcasten där jag vanligtvis ber dig att köpa min bok, men istället i dag ska jag be dig att köpa den här boken ”M is for (DATA) MONKEY” av Ken Puls och Miguel Escobar. En FANTASTISK bok som lär dig allt om hur du använder Power Query eller Get & Transform Data, allt jag lärde mig om Power Query fick jag lära mig från den här boken.

OK, avsluta från det här avsnittet: Vårt mål är hur man importerar en lista med filnamn till Excel. Om du har Excel 2016 kan du använda nya Get & Transform Data. Om du inte har 2016, men du har en riktig version av Excel som körs under Windows, kan du ladda ner det gratis Power Query-tillägget som är för Excel 2010 eller Excel 2013. Det fungerar inte på din Android-telefon, eller din iPad eller din iPhone eller Surface RT eller din Mac, eller hur, det är bara för Windows-versioner av Excel. Så vi ska börja från tomt kalkylblad, Data, Hämta data, Från fil, Från mapp, ange mappnamnet eller Bläddra, se till att klicka på Redigera istället för Ladda. Och sedan på filtret, filtrera på filtypen för att bli av med allt som inte är en PDF, filtrera efter mappnamnet för att bli av med alla undermappar. Behåll bara filnamnet och mappen,så högerklicka på de andra och säg Ta bort kolumn och dra sedan mapprubriken till vänster om filen så att sammanslagningen fungerar. Välj båda kolumnerna, välj sedan Sammanfoga kolumner på fliken Lägg till kolumn, skriv ett nytt namn, klicka på OK och högerklicka på den nya kolumnen och ta bort andra kolumner, hem, stäng och ladda, så får du din lista. Den fantastiska delen, du kan uppdatera frågan senare genom att använda den här uppdateringsikonen i frågor & anslutningar.Du kan uppdatera frågan senare genom att använda den här uppdateringsikonen i frågor och anslutningar.Du kan uppdatera frågan senare genom att använda den här uppdateringsikonen i frågor och anslutningar.

Tja hej, jag vill tacka dig för att du stannade, vi ses nästa gång för en ny netcast från!