SharePoint Address Loop Variable for Counting Files and Folders

[data_slug]

Hi There,
I am trying to create a report that counts all of the files and folders located within 400+ SharePoint URLs, looping through the address’s from an excel based list. I have managed to connect to the sharepoint site through my ‘microsoft authentication’ and test a single address via the ‘sharepoint online connector’, this all works fine.

I have tried to create a workflow that imports my address list from excel, however I cannot workout how to apply a flow variable (or any other way) to loop through the list one after the other.

I see that there are a couple of other threads related to this, however none are exactly like what I am trying to achieve. Any help would be greatly appreciated…

[LukasS]

Hi @data_slug, and welcome to the community!

That’s actually a great workflow already - I guess what’s missing is the configuration of the List Files/Folders Node to use the path as a flow variable (by clicking the v-Symbol in its configuration). However, you most likely have it present as a string from the excel file. So if you put a String to Path node in between the Excel Reader and the Table Row to Variable Loop Start Node, you can convert it to a the path-type (a little p symbol, instead of the s-symbol). If you then configure the Loop Start Node to use the path-type column, the List Files/Folders Node should be able to understand it.

I hope that helps! If not, please ask
Lukas

[data_slug]

Hi @LukasS, thank you for getting back to me. I have added in the string to path node b/w the excel reader and the table row to variable loop start nodes and saw the little p symbol next to the correct column in the output table. I then added the column to the List Files/Folders node as a variable, however it gives me this error:

and will not let me clear it. all of the paths in my list variables exist and i have tested manually that they work.

Any idea what might be the cause and or the fix here?
Much appreciated,
DS

[LukasS]

Hi @data_slug ,

huh, a NullPointerExceptions should really not happen The error happens at the List Files/Folder Node, right?

I didn’t think of one detail: the String to Path also needs a File System connector, so that the path is actually a sharepoint path. I just rebuilt your example, and this works for me:

I uploaded the example here:

Count Sharepoint Files and Folders – lukass

so you can investigate the node settings. Ofc, you’d have to adjust the credentials and replace the Table Creator with your Excel Reader, and the logic after the List Files/Folder node.

Crucial is the Additional File System Connection port at the String to Path, similar to the List Files/Folders Node, after that its pretty similar to what you did. I added a Constant Value Column, to identify which folder was counted and the GroupBy does the counting part (could also happen after the Loop End).

---

Still, NullpointerExceptions should not happen

Best would be if I could reproduce this error on my end, so I can investigate more closely, and we can fix this. Could you maybe send a minimal example, i.e. by uploading it to the Hub (or here in the forum)? Maybe you can replace the Excel Reader with a Table Creator so you don’t have to send the data? Please make sure to not have any credentials stored with the Authentication node. I will try to connect to a sharpoint of ours and see if the error persists.

Also, which version are you on (Help → About KNIME Analytics Platform)? 4.7.1 would be the latest one, maybe an update would already help (File → Update KNIME…)?

Thanks for the effort and sorry for the inconvenience!
Lukas

[data_slug]

Hi @LukasS , thank you for sending this workflow through, it is much cleaner and makes a lot of sense. I didn’t think of connecting the ‘SharePoint online connector’ to the ‘string to path’ node. The only issue I am having now is that my SharePoint URL needs a “/” at the end for it to be recognised within our SharePoint location.
The ‘string to path’ node removes that at the end of the string, even though it is included within the excel variable input file. Is there a way to add it without removing the path function from the output?

it is also missing a “/” at the end of the “https://” and I am wondering if that will affect the ability for the ‘List Files/Folders’ node to connect to the address?

I have updated to version 4.7.1 now, but because I have changed the workflow, I am not getting the same error. I will try and put together the data you asked for and repost here,

thank you
DS

[LukasS]

Hey @data_slug,

I’m glad the NullPointerException-error does not show up in the latest version anymore - this means that it was already fixed. So no need to send the workflow

For your workflow you’d have to change in which format you pass on the path - the List Files/Folders node expects a relative path, i.e. not a URL like
https://knime.sharepoint.com/Shared%20Documents/Archive but the path relative to the root of sharepoint like /Documents/Archive. I’d employ a String Manipulation node to get the relative path from the URL. The details might be a little different in your case, but this is how it worked for me:

You mentioned that the List Files/Folders Node initially did work when you put in the path by hand - do you remember whether this was in URL-format or somehow differently? I didn’t know that it can handle URLs

Kind regards,
Lukas