topic Re: SharePoint Connector August 2022 - read in multiple files (QlikSense) in Connectivity & Data Prep

SharePoint Connector August 2022 - read in multiple files (QlikSense)

laura_gmp — Tue, 07 Mar 2023 15:40:55 GMT

Hi Community,

I am trying to load multiple files from a specific folder from Sharepoint into QlikSense. I am using Qlik Sense August 2022 with the new Office 365 Sharepoint connector version included.

Having looked on various other threads i only find solutions using web connectors.

Using the new 365 connector I am able to load files individually, however it won't suitable to load multiple files at once.

The following error occurs "badFileSize Not found", when i use this FROM Statement:

FROM [lib://Sharepoint/DemoData/Data_*.xls] (biff, embedded labels, table is Data$);

Every stored file at this location has the same format and uses the same prefix followed by a different date (e.g. Data_230123/Data_240123/Data_250123).

Is there any way to get this result without using web connectors?

Thanks a lot in advance. Hope you can help!

Edit 07-02-2023: I even tried to fetch all files in a folder and loop over them as a workaround. The generation of the file list fails and therefore no lines are loaded. With a trace I found out that the loop is not executed at all, because no file list was generated.

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

Ken_T — Wed, 15 Feb 2023 14:51:03 GMT

facing same issue...
with FOLDER connection, wildcard in the file name works fine and loads multiple files

in the new SharePoint connection created from the HUB, the wildcard in the file name gives the error you mentioned.

IF you are on Enterprise, you can use a network shared folder and a FOLDER connection and the wildcard will work to load multiple files. Did you ever find another workaround for this? I am reporting it to Qlik Support today

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

laura_gmp — Wed, 15 Feb 2023 15:11:55 GMT

Hi @Ken_T ,

All files are stored within Sharepoint and would need to be copied to the network shared folder on a daily basis. Therefore, a shared folder is not a option. I created a support case, still no workaround or solution has been found.

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

Ken_T — Wed, 15 Feb 2023 15:24:10 GMT

power automate could probably move the files, (I am not a power automate guru) if you hit a dead end on support. Let us know how your ticket turns out. I am logging one, too.

PS, on Qlik CLOUD, Data Files (a special folder in each space) do seem to allow wildcard in the file name, so it will work there. I just tested that.

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

laura_gmp — Tue, 07 Mar 2023 16:20:48 GMT

Hi @Ken_T ,

I even tried to use the ListFiles to fetch all files in a folder and to loop through as a workaround. The generation of the file list fails and therefore no lines are loaded. With a trace I found out that the loop is not executed at all, because no file list was generated.

According to Qlik Support "Reading Multiple files directly seems to be product limitation" and we should submit a feature request.

This missing functionality, is making it impossible to use the connector! Should work similar to folder connections.

I submitted this Feature as Idea: https://community.qlik.com/t5/Suggest-an-Idea/SharePoint-Connector-August-2022-read-in-multiple-files/idi-p/2046313#M12118

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

Ken_T — Tue, 07 Mar 2023 16:24:29 GMT

we logged a ticket with support for this...
so far I have not found any workaround for this

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

starke_be-terna — Wed, 08 Mar 2023 10:49:00 GMT

Could it be an option for you to install Web Connectors on a server and use this in the meantime?
It's basically just a webservice that could be made available within a network, although this might be a risk you do not want to take, if you are using Qlik Cloud.
This should work: Solved: Load multiple excel file from Sharepoint by using ... - Qlik Community - 1902447

Best regards
Benjamin

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

AlexOmetis — Wed, 08 Mar 2023 11:49:51 GMT

Am I missing something here or do you just want the SharePoint Metadata Connector - this does ListFiles and other items and can be built into a loop. That's how we load multiple files from SharePoint in on-prem and SaaS these days (although the performance - especially of the on-prem one - is nowhere near the old Web Connectors - about 10x slower).

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

Ken_T — Wed, 08 Mar 2023 13:51:38 GMT

@AlexOmetis perhaps you could give a code sample if it so easy to do?
we would all appreciate that.
this post is about how a wildcard in a file name works in Enterprise Folder connection, works in SaaS/QCG Data Files connection, but does not work in sharepoint File connection on SaaS/QCG.
If you have a viable workaround, please share a code sample that illustrates this.

thanks!

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

hwen — Tue, 14 Mar 2023 20:25:03 GMT

If all files are in the same Sharepoint folder, it's very easy to load multiple files into QLIK Sense using Web File Connector. I used this canned script to process hundreds of files from multiple apps (in my case the SP connection points to the folder with all Excel files).

// first to load from a QVX table to get the file names and GUID:

FileIDs:
LOAD UniqueId, Name
FROM [lib://<connector_name>] (qvx)
Where WildMatch(Name, 'Data_*.xls');

// Next step to just loop through this FileIDs table. Assume all files have the same structure

FOR i = 0 to (NoOfRows('FileIDs') - 1)
Let vId=Peek('UniqueId',$(i), 'FileIDs');

Load * From (URL is [<SP Site URL>$(vId)<Access Token>], biff, embedded labels, table is Data$);

Next i

Drop Table FileID;

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

Ken_T — Tue, 14 Mar 2023 18:01:55 GMT

thanks for the reply, this post is about the *new* SharePoint connector, that is in the HUB I forget what version it was introduced --- and also same as in Qlik Saas and Qlik Cloud Government. ---- not about using web connector! but that is a good workaround for folks on older versions or still using web connector.

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

starke_be-terna — Wed, 15 Mar 2023 12:52:49 GMT

You are very correct. I was missing the Metadata Connector. With this, we can load from multiple files!

Thank you very much for your help.

Best regards,
Benjamin

Needed connectors: Office 365 SharePoint Metadata Connector, Office 365 SharePoint Connector.

This is the working snippet for everyone wondering:

// This connection is set up with sub_site only
LIB CONNECT TO 'Office_365_Sharepoint_Metadata';

LOAD
Name as [ListFiles.Name]
;

SELECT UniqueId,
Name
FROM ListFiles
WITH PROPERTIES (
subSite='/my_subsite',
folder='/my_subsite/path/to/folder',
maxResults=''
);

// Current Setup: Office_365_Sharepoint points to sub_site
for each vRemoteFile in FieldValueList('ListFiles.Name')

// This part can be extended to solve issues like concatenate, etc.
LOAD
*
FROM [lib://Office_365_Sharepoint/path/to/folder/$(vRemoteFile)]
(qvd);

next vRemoteFile;

Re: SharePoint Connector August 2022 - read in multiple files (QlikSense)

Ken_T — Thu, 30 Mar 2023 14:12:58 GMT

we got this working, @starke_be-terna your script was very helpful!