EXCEL - get file lists in Directory and metadata into a spreadsheet as text


jimbo45

Well-known member
Power User
VIP
Local time
10:02 AM
Posts
2,223
Location
Hafnarfjörður IS
OS
Windows XP,7,10,11 Linux Arch Linux
Hi folks
This is not as easy as it seems on first sight but actually it's not too difficult.

The EXCEL power query function is fine but why on earth do Ms go out of there way to make bits of this people will find useful hidden away or make it awkward to extract the data properly.

The main problem with using the query functios - even when transform data is that after you've created the query with all the nice data shown on the query pane there's no export query to csv, another worksheet or whatever. And say your files are all music or video then the load data function tries to do exactly that - which apart from taking a long time will probably bomb out as it tries to load large mp4 files etc into the spreadsheet when all you want is the text details of the folders.

Anyway here's what to do

1) go to the data option on the main spreadsheet sorry my example is in Icelandic but in Eng it's the same number of headings across -- should be called data

Skjámynd 2022-03-21 142058.png
2) Choose data sources Select folder :

Skjámynd 2022-03-21 143212.png

pull the folder in

Now create the query via Transform data
(note recursive works if you have folders within folders -- remember though don;t have 1000;s of files or you'll run out of memory)

e.g old TV series I have 6 Million Dollar Man - I want a list of all the files in the set of series.

from the preview select transform data

Skjámynd 2022-03-21 143829.png


Now you should see the results of the query -- this is where Ms makes it stupidly difficult to save this into a nice EXCEL spreadsheet as text columns !!!! Delete the columns you don't want e/g I don't want the CONTENT column so selct and remove column

Skjámynd 2022-03-21 144329.png

Now choose copy entire table -- click the little table box
Bonkers thing is now you have to discard the querey -- then back to your spreadsheet and simply ctrl-v the saved table from the query and insert into worksheet
.

Skjámynd 2022-03-21 145228.png


As you can see files from folders within the main folder are copied i.e subfolders
- e.g season 2 (S02E01.xxx)


Have fun !!!!!



I'm not sure why Ms makes this process so STOOOOOOOOPID. If I want a list of files I don't need to copy the actual Binary DATA from those files into my spreadsheet surely !!!!. I just want the names and attributes. !!!!!

Cheers
jimbo
 
Last edited:

My Computer

System One

  • OS
    Windows XP,7,10,11 Linux Arch Linux
    Computer type
    PC/Desktop
    CPU
    2 X Intel i7

Latest Support Threads

Top Bottom