How to import a list of file names into Excel
Use the new Get & Transform Tools in Excel 2016
If you don't have 2016, them download the free Power Query add-in for Excel 2010 or Excel 2013
Power Query is not available for Android phones, the iPad, iPhone, Surface RT, or the Mac
Start from a blank worksheet
Data, Get Data, From File, From Folder
Enter the folder name (or browse)
Make sure to click Edit
Open the filter on file type and remove anything that is not a PDF
Open the filter on folder and remove any garbage subfolders
Keep only File Name and Folder
Drag Folder heading to the left of File
Select both columns
Choose Add Column, Merge Columns, Type a new Name
Click the New Column and Remove Other Columns
Home, Close & Load
The amazing part… you can refresh the query later.
Use the new Get & Transform Tools in Excel 2016
If you don't have 2016, them download the free Power Query add-in for Excel 2010 or Excel 2013
Power Query is not available for Android phones, the iPad, iPhone, Surface RT, or the Mac
Start from a blank worksheet
Data, Get Data, From File, From Folder
Enter the folder name (or browse)
Make sure to click Edit
Open the filter on file type and remove anything that is not a PDF
Open the filter on folder and remove any garbage subfolders
Keep only File Name and Folder
Drag Folder heading to the left of File
Select both columns
Choose Add Column, Merge Columns, Type a new Name
Click the New Column and Remove Other Columns
Home, Close & Load
The amazing part… you can refresh the query later.
Transcript of the video:
Learn Excel from Mr. Excel podcast episode 2181 List folder files in Excel Hey welcome back to the Mr. Excel NetCast I'm Bill Jelen Jill and today's question: someone has a list of tax invoice PDF files in a folder and they need to get the list of all those file names in Excel. All right and one way to do that is to type them all or copy and paste from Windows Explorer but there's a great tool that can solve this and my first question was well what version of Excel do you have? Because if you happen to have Excel 2016 they're gonna have this amazing new functionality called Get and Transform Data. Now in office 365 it's on the left hand side. I think in the original version of Excel 2016 it was in the third group. All right so just look for Get and Transform. If you're in Excel 2010 or Excel 2013 for Windows you can download Power Query and you'll have your own tab with this exact stuff. Now let's take a quick look at this folder.
Alright I just created a fake folder here with some fake data you'll see that there are Excel files in this folder and PDFs I only want the PDFs, and there's also some subfolders. I don't want these PDFs I only want the PDFs in the main folder so C:\budgets. I'm going to copy that and then come back here to Excel and we're say we want to get data from a file from an entire folder like this and then type the folder path in there or use the Browse button either one and when you get this first screen you definitely want to edit and now we're in the Power Query editor.
Alright so my goal here: I don't need the contents. I'm going to right-click and say remove that column. There's my list of files I only want PDF files so if there's anything that's not a PDF I only want PDFs click OK I guess it's just the PDF files oh and then look over here see now they're pulling in things from just the original folder and from the garbage folder so I open this up and I uncheck everything that's not the original folder all right so now I have a nice little list and this list is you know what 9 records but in real life I bet they probably have you know dozens or hundreds of these alright I don't need any other stuff now so I can right-click and remove those columns. Alright now what I really need here is I need the folder path and the filename together. So I am going to to take folder path and drag it to the left and drop it there and then the magic step here: In regular Excel we would have to do concatenation for this but what I'm gonna do is I'm going to merge columns so I'm gonna add column and choose merge columns the separators gonna be none the new columns gonna be called file name and click OK alright so we have the folder name the slash and the file name like that now that's actually the only thing we need so I'm gonna right-click and say remove the other columns and then finally home close and load and we get a brand new sheet with a our data alright now it comes in as a table and so I'm gonna just copy this ctrl C and then come over here to where I really wanted the data up here and paste special values click OK now it's not on that table anymore it's just my pure data like that and now here's the really beautiful thing about this so we set this up once and wow that took less than three minutes to set up but let's come back to that budgets folder and let's move some stuff around let's take one of these garbage records and we'll copy it up to the main folder control V alright so now there's more stuff here there's 10 PDF files instead of 9 if I come here to where the query is and over on the right hand side of the screen and the queries and connections you might have to make this wider I've made mine wider already you'll see our budgets with 9 rows loaded I'm gonna click the little refresh icon here and very quickly budgets now has 10 rows loaded so it's picking up the new records you set this up once and then you'll be able to just refresh to get the new data well this is the point in the podcast where I usually ask you to buy my book put instead today I'm gonna ask you to buy this book M is for (Data) Monkey by Ken Puls and Miguel Escobar - An amazing book that will teach you all about using power query or the Get and Transform Data. Everything I learned about power query I learned from this book. Okay wrap up from this episode our goal is how to import a list of filenames into Excel if you have Excel 2016 you can use the new get and transform data if you don't have 2016 but you have a real version of Excel Excel running under windows then you can download the free power query add-in that's for Excel 2010 or Excel 2013 it's not gonna work on your Android phone or iPad or iPhone or Surface RT or your Mac.
Right it's only for Windows versions of Excel so we're gonna start from blank worksheet data get data from file from folder enter the folder name or browse make sure to click edit instead of load and then on the filter our filter on the file type to get rid of anything that's not a PDF filter on the folder name to get rid of all the garbage subfolders keep only the file name and folder so right-click those others and say remove column then drag the folder heading to the left of file that allows the merge to work select both columns then on the add column tab choose merge columns type a new name click OK and then click on that right click on that new column and remove the other columns home close and load and it will give you your list the amazing part you can refresh the query later by using this refresh icon over in the queries and connections well hey I want to thank you for stopping by we'll see you next time for another NetCast from MrExcel
Alright I just created a fake folder here with some fake data you'll see that there are Excel files in this folder and PDFs I only want the PDFs, and there's also some subfolders. I don't want these PDFs I only want the PDFs in the main folder so C:\budgets. I'm going to copy that and then come back here to Excel and we're say we want to get data from a file from an entire folder like this and then type the folder path in there or use the Browse button either one and when you get this first screen you definitely want to edit and now we're in the Power Query editor.
Alright so my goal here: I don't need the contents. I'm going to right-click and say remove that column. There's my list of files I only want PDF files so if there's anything that's not a PDF I only want PDFs click OK I guess it's just the PDF files oh and then look over here see now they're pulling in things from just the original folder and from the garbage folder so I open this up and I uncheck everything that's not the original folder all right so now I have a nice little list and this list is you know what 9 records but in real life I bet they probably have you know dozens or hundreds of these alright I don't need any other stuff now so I can right-click and remove those columns. Alright now what I really need here is I need the folder path and the filename together. So I am going to to take folder path and drag it to the left and drop it there and then the magic step here: In regular Excel we would have to do concatenation for this but what I'm gonna do is I'm going to merge columns so I'm gonna add column and choose merge columns the separators gonna be none the new columns gonna be called file name and click OK alright so we have the folder name the slash and the file name like that now that's actually the only thing we need so I'm gonna right-click and say remove the other columns and then finally home close and load and we get a brand new sheet with a our data alright now it comes in as a table and so I'm gonna just copy this ctrl C and then come over here to where I really wanted the data up here and paste special values click OK now it's not on that table anymore it's just my pure data like that and now here's the really beautiful thing about this so we set this up once and wow that took less than three minutes to set up but let's come back to that budgets folder and let's move some stuff around let's take one of these garbage records and we'll copy it up to the main folder control V alright so now there's more stuff here there's 10 PDF files instead of 9 if I come here to where the query is and over on the right hand side of the screen and the queries and connections you might have to make this wider I've made mine wider already you'll see our budgets with 9 rows loaded I'm gonna click the little refresh icon here and very quickly budgets now has 10 rows loaded so it's picking up the new records you set this up once and then you'll be able to just refresh to get the new data well this is the point in the podcast where I usually ask you to buy my book put instead today I'm gonna ask you to buy this book M is for (Data) Monkey by Ken Puls and Miguel Escobar - An amazing book that will teach you all about using power query or the Get and Transform Data. Everything I learned about power query I learned from this book. Okay wrap up from this episode our goal is how to import a list of filenames into Excel if you have Excel 2016 you can use the new get and transform data if you don't have 2016 but you have a real version of Excel Excel running under windows then you can download the free power query add-in that's for Excel 2010 or Excel 2013 it's not gonna work on your Android phone or iPad or iPhone or Surface RT or your Mac.
Right it's only for Windows versions of Excel so we're gonna start from blank worksheet data get data from file from folder enter the folder name or browse make sure to click edit instead of load and then on the filter our filter on the file type to get rid of anything that's not a PDF filter on the folder name to get rid of all the garbage subfolders keep only the file name and folder so right-click those others and say remove column then drag the folder heading to the left of file that allows the merge to work select both columns then on the add column tab choose merge columns type a new name click OK and then click on that right click on that new column and remove the other columns home close and load and it will give you your list the amazing part you can refresh the query later by using this refresh icon over in the queries and connections well hey I want to thank you for stopping by we'll see you next time for another NetCast from MrExcel
