May22 Web Storage Connectors OneDrive // O365 Sharepoint Connector read multable files

[petergassert]

Hi,

i am trying to read from the new Web Storage Connectors OneDrive // O365 Sharepoint Connector multiple files.
Sadly in the description of the OneDrive Connector the limitation tells, that the usage wildcards is not possible.
The Sharepoint Connector does not have the limitation in the description but in practise it is not working as well.

Am i doing someting wrong or what alternatives are given to read loads of files from the mentioned Web Storage Connectors. Is it possible to genereate a file list or something else to iterate through?

Qlik Sense Version May22. I do not really want to use the old web connector. 

Thanks in advance,
Peter

[petergassert · Accepted solution]

Solution found:

Please watch https://www.youtube.com/watch?v=_ufHf9Wm4XA in combination with https://community.qlik.com/t5/Connectivity-Data-Prep/How-to-load-multiple-Excel-Files-from-OneDrive-....

[ingoniclas]

Hi,

that's a great solution for the OneDrive connector. Is there a similar one for the Sharepoint Connector, too? I'm desperately looking for a solution.

Thanks,

Ingo

[petergassert]

Hi Ingo,

it's the same/similar with Sharepoint.
Most important is the O365 sharepoint metadata connection in the data editor.

You collect the information and loop through.

Best regards,

Peter

[ingoniclas]

Hi Peter,

Many thanks for the quick reply! I see that I can refer to the OneDrive setup for setting up the process for Sharepoint, too. But the Metadata Connector for Sharepoint is different, it doesn't have the table "Items". Which table should I refer to here, and where to go from there?

Cheers,

Ingo

[ingoniclas]

Hi there,

I'm still looking for the above mentioned solution, i.e. how to use Sharepoint Meta Data to establish a looping with the built-in Sharepoint connector through muliple files in a folder. Is anyone aware of a solution? Is there a description somewhere? Many thanks for any hint.

Best regards,

Ingo

[petergassert · Accepted solution]

Hi Ingo,
sorry for the late reply.

if you use the SP Connector:
1. Create the SP Metadata connection with the base URL
2. Open the Metadata connection and go to List Folder and add 

• Site/Subsite Path e.g. /subSiteName, /sites/mysitecollection, /sites/mysitecollection/subSiteName  
• Press preview and copy the ServerRelativeUrl to the field Folder 
  Repeat the last step until you reached the final location

3. Go to ListFiles and use the same Site/Subsite Path and Folder Info as for ListFolders

4. Preview and all your files will be displayed. 

5. Select the Field Name and add the script.

6. Open the Sharepoint connector and open one File from the respective folder.

7. With a loop you can load each file stored on Sharepoint 

for iFile = 0 to NoOfRows('Filelist') -1
let vFile = peek('Name', iFile, 'Filelist');

trace $(vFile);

Load * 
FROM [lib://<SharepointSource>/Shared Documents/General/<Foldername>/$(vFile)]
(txt, codepage is 28591, embedded labels, delimiter is ';', msq);
;
Next

[ingoniclas]

Hi Peter,

many thanks for your reply. It worked. What's unfortunate, though, is that you cannot establish a connection down to a certain subfolder like with the Sharepoint Connector. You can only establish the connection with the base url, thus providing access to all subfolders to anyone who is allowed to use the connection 😞

Best,

Ingo

[petergassert]

Hi Ingo,

it is not good documented, but the systematic is similar. 
Sadly the connections are incredable slow. Looping through does not make it better.

As long my solution fits all is good.

Best regards,
Peter