Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
laura_gmp
Partner - Contributor II
Partner - Contributor II

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

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.

Labels (1)
2 Solutions

Accepted Solutions
AlexOmetis
Partner Ambassador
Partner Ambassador

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).

Qlik Partner Ambassador 2024

View solution in original post

starke_be-terna
Partner - Contributor III
Partner - Contributor III

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;

View solution in original post

12 Replies
Ken_T
Specialist
Specialist

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

 

 

laura_gmp
Partner - Contributor II
Partner - Contributor II
Author

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.

Ken_T
Specialist
Specialist

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. 

laura_gmp
Partner - Contributor II
Partner - Contributor II
Author

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-file...

Ken_T
Specialist
Specialist

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

starke_be-terna
Partner - Contributor III
Partner - Contributor III

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

AlexOmetis
Partner Ambassador
Partner Ambassador

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).

Qlik Partner Ambassador 2024
Ken_T
Specialist
Specialist

@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!

hwen
Contributor
Contributor

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;