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
2) Choose data sources Select folder :
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
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
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
.
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
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
2) Choose data sources Select folder :
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
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
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
.
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
