How to load multiple files from SharePoint using the Office 365 Connector?

QlikToFindOut — Thu, 02 Jun 2022 20:23:44 GMT

Hello,

I've run into a problem when trying to load multiple files from SharePoint using the Office 365 Connector that comes from the latest Qlik Web Connector. In addition, I watched this video:

https://www.youtube.com/watch?v=x6OK_2sRxCY

For the most part, it does exactly what it says it will do. I can pull in a single file and I confirmed it, but that's all it can do. Obviously, this is incredibly inefficient since there are over hundreds of Excel files in the SharePoint folder I'm trying to pull from.

I then had a thought in which I use the ListFiles table to get all of the UniqueIds of the Excel files. Then I can create the GetFile connection so I can pull the individual file.

The question now becomes, how can I dynamically do this so the GetFile connection will grab all files instead of a single file? My thought was to do this:

let vQwcConnectionName = 'lib://SharePoint_Connection_Test';
let vgetfileconnection = 'lib://GetExcelFiles';

Office365Connector_ListFiles:
LOAD
UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [web connector URL, it tripped the spam filter so removing it], qvx);

for i=0 to NoOfRows('Office365Connector_ListFiles') - 1
let vField = Peek('ListFiles_UniqueId',$(i),'Office365Connector_ListFiles');
LOAD
userPrincipalName,
userDisplayName,
mfaCapable,
passwordlessCapable,
ssprCapable,
ssprRegistered,
ssprEnabled,
methodsRegistered
FROM [$(vgetfileconnection)$(vField)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;
NEXT i

However, I'm getting this error:

In the back end, the connection looks something like this:

webconnectorurl?connectorID=Office365Connector&table=GetFile&subSite=sites%2fEnterpriseDataReportingAnalysis%2f&fileId=

I removed the fieldId at the end so I can just append a value onto it like I have in my for loop up there. Unfortunately, I'm getting that error message above.

This leads me to ask two questions:

Is what I'm doing even possible?
Is there something in the connector itself that allows me to pull multiple files dynamically without constantly needing to go to the web connector to create a new connection?

Thanks!

Re: How to load multiple files from SharePoint using the Office 365 Connector?

marcus_sommer — Fri, 03 Jun 2022 12:10:28 GMT

I think the loop-approach should be in general possible. But I'm not familiar enough with the lib-connection to say if it would be directly possible with a single-lib and then adding to it all dynamic parts or if each time a new lib would be needed. Further you need to make sure that the created path is complete and valide.

Therefore I suggest to compare these loop-path with the path which is created to load a single file outside from the loop. Maybe you don't try to load the files immediately in the loop else just writing the path-strings within a variable or a TRACE or a txt to check the logic and syntax without running into a load-error. If you could confirm that the paths are identically and correct and it didn't work you may switch to the legacy mode to exclude that the lib-feature prevents a successful loading.

- Marcus

topic How to load multiple files from SharePoint using the Office 365 Connector? in App Development

How to load multiple files from SharePoint using the Office 365 Connector?

Re: How to load multiple files from SharePoint using the Office 365 Connector?