Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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;
we got this working, @starke_be-terna your script was very helpful!