Tracking Folder Structure with Onedrive Metadata

[Meghann_MacDonald]

Hi all, I am using onedrive metadata to recreate my folderstructure. I started with this:

[tempFiles]:
LOAD name as [Items.name],
parentReference_path as [Items.parentReference_path],
folder_childCount as [Items.folder_childCount],
webUrl as [Items.webUrl];

SELECT name,
parentReference_path,
folder_childCount,
webUrl
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Nick',
maxResults=''
);

Which provides me with a list of the folder names within the first folder, "Nick". Great!

Now, based on that list, I want to have it look inside EACH folder and give me the names of THOSE files/folders. I think it would be basically the same code as above except with a "path" that is a variable, assigned to the file names from the table above. 

Additional info: this is what the code above gives me: 

These "Item Names" are the names of subfolders. i then want to generate a list of the 4 folders within the Matthew_court_cmaj folder, and then the 3 folders within TalkTooMuchProject folder, and so on. 

Happy Holidays!

[ksypolit]

Hello,

have you find a solution for your questions.

I want to do exactly the same thing.

[Meghann_MacDonald · Accepted solution]

Hi @ksypolit, yes I think I found a way. You can use variables in your select statements

You will load the first hierarchy to find the folders in your desired starting folder like this: 

LIB CONNECT TO 'OneDrive_Metadata - 'your credentials'';

Filelist:
LOAD id as [Items.id],
name as [Items.name],
parentReference_path as [Items.parentReference_path],
right(parentReference_path, len(parentReference_path)-13) as [Folder],
if(len(folder_childCount)>0, 'Folder', 'File') as [Items.type];

SELECT id,
name,
parentReference_path,
folder_childCount
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Ideation',
maxResults=''
);

Keep in mind you can remove my "AS" statements, the are just there to help me start to build the hierarchy. This gives you a table listing all of the items in the first Ideation folder, and also lets you know if they are files or folders. 

Then you use the following to create a variable 'x' with all of the item names from the list above (for the folders). 

FOR x = 0 TO NoOfRows('Filelist') - 1
LET vItem = peek('Items.name', $(x), 'Filelist');

Then, you load another list with the same info, but with the variable as the path: 

Filelist2:
LOAD id as [id],
name as [name],
parentReference_path as [referencepath],
right(parentReference_path, len(parentReference_path)-12) as [path],
if(len(folder_childCount)>0, 'Folder', 'File') as [type],
subfield(parentReference_path, '/', 4) as Folder;

SELECT id,
name,
parentReference_path,
folder_childCount
FROM Items
WITH PROPERTIES (
driveId='',
folderId='',
path='/Ideation/$(vItem)',
maxResults=''
);
NEXT x

And this gives you another table with every file or folder located in one of the folders that lies within the first Ideation folder. You basically repeat this with a new variable each time. I'm sure there is a way to just have this run over and over and concatenate into one table as well, rather than filelist1, filelist2, filelist 3 etc. Especially if you just changed to "let vItem = peek('Itemsname' where type = folder). Because if you have a list of all folders and their paths, then all items and their path, you can just take the paths and break them down with subfields to build the hierarchy. I didn't get that far. Hope this helps!

[ksypolit]

Hi @Meghann_MacDonald 

Thank you very much for the reply and the code.

Yes it works great and reads all subfolders and their content. I will try to make some changes to get the subfolders of all depth levels as you mention.

Thanks again 

[elakkians]

Hi @ksypolit 

Can you please post the code on how you have taken all the files from all subfolders? With the above code, we can get only the first level of the folders.