Skip to main content

Suggest an Idea

Vote for your favorite Qlik product ideas and add your own suggestions.

Announcements
Marching toward a simplified navigation! READ ON

Office 365 Sharepoint Connector - Load all files in folder

erik_tengstam
Contributor II
Contributor II

Office 365 Sharepoint Connector - Load all files in folder

Intead of finding one unique ID to load, being able to load all files in a folder. Often we have several files, and adding new ones, which is making it impossible to use the connector.

 

Of course best case would be to make the function native and work similar to folder connections

Tags (1)
7 Comments
chrisbrain
Partner - Specialist II
Partner - Specialist II

@erik_tengstam Is this for the standalone Qlik Web Connectors?

Can't you use the ListFiles table to loop through all the IDs in a folder and load each one in a for/next loop?

erik_tengstam
Contributor II
Contributor II

As far as I know there is only one conenctor for sharepoint.

I am not able to do that, due to that I need to create unique connection for each file. If you have that solution please do share it with me

chrisbrain
Partner - Specialist II
Partner - Specialist II

@erik_tengstam You should see when you generate the load script for loading a file that the file's ID is part of the load script - and so can be replaced with a variable e.g. $(vFileId) and then you can update this in a for/next loop iterating through the results of a previously run ListFiles table.

erik_tengstam
Contributor II
Contributor II

I see now, had to built it from the other functions since getfile doens't provide the "URL IS" part by default. I got it to work, but still I feel that it is far to much work to load files.

 

Would still like it as a feature.

 

Thanks @chrisbrain  for the sugggestion

Boulanger
Contributor II
Contributor II

Hi,

I am sorry to update this please let me know if it's preferable that I create new thread.

I'm facing the same issue and I didn't get the solution. What i'm thinking is that Sharepoint doesn't work like a windows folder and there is a mess with URL, paths etc..

FOR EACH _file in filelist('lib://MyConector'&'/*');
MD:
Load *
From $(_file)
(ooxml, embedded labels, header is 4 lines, table is [Bericht 1]);   // all my excel files

 

For example this script doesn't load anything. I expected at least It would load all metadata of the files listed in this folder

 

Thanks if anyone come back here for helping me on that 😉

erik_tengstam
Contributor II
Contributor II

@Boulanger What you want to the in the loop i to point to each unique file ID. Not the actual lib connection - which should be one to your webconnector. See  the script below:

 

//point to existing connection to webconnectors
let vQwcConnectionName = 'lib://WebConnectors';


//get a list of all files in a folder - update URL IS with folder to load **Subsite and Folder must be updated
Office365Connector_ListFiles:
LOAD
UniqueId as ListFiles_UniqueId,
Name as ListFiles_Name,
title as ListFiles_title,
id as ListFiles_id,
updated as ListFiles_updated,
Author_Entry as ListFiles_Author_Entry,
CheckedOutByUser_Entry as ListFiles_CheckedOutByUser_Entry,
EffectiveInformationRightsManagementSettings_Entry as ListFiles_EffectiveInformationRightsManagementSettings_Entry,
InformationRightsManagementSettings_Entry as ListFiles_InformationRightsManagementSettings_Entry,
ListItemAllFields_Entry as ListFiles_ListItemAllFields_Entry,
LockedByUser_Entry as ListFiles_LockedByUser_Entry,
ModifiedBy_Entry as ListFiles_ModifiedBy_Entry,
Properties_Entry as ListFiles_Properties_Entry,
VersionEvents_Feed as ListFiles_VersionEvents_Feed,
Versions_Feed as ListFiles_Versions_Feed,
CheckInComment as ListFiles_CheckInComment,
CheckOutType as ListFiles_CheckOutType,
ContentTag as ListFiles_ContentTag,
CustomizedPageStatus as ListFiles_CustomizedPageStatus,
ETag as ListFiles_ETag,
Exists as ListFiles_Exists,
IrmEnabled as ListFiles_IrmEnabled,
Length as ListFiles_Length,
Level as ListFiles_Level,
LinkingUri as ListFiles_LinkingUri,
LinkingUrl as ListFiles_LinkingUrl,
MajorVersion as ListFiles_MajorVersion,
MinorVersion as ListFiles_MinorVersion,
ServerRelativeUrl as ListFiles_ServerRelativeUrl,
TimeCreated as ListFiles_TimeCreated,
TimeLastModified as ListFiles_TimeLastModified,
UIVersion as ListFiles_UIVersion,
UIVersionLabel as ListFiles_UIVersionLabel
FROM [$(vQwcConnectionName)]
(URL IS [https://YOURWEBCONSERVER:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=YOURSUBSIT...], qvx);

//get Ids for the files to load, in this case with a name as TestConnector
FilesToLoad:
Load Distinct
ListFiles_UniqueId AS FilesToLoad
Resident Office365Connector_ListFiles
where WildMatch(ListFiles_Name,'*TestConnector*');


//loop thourgh the files to be loaded
FOR Each File in FieldValueList('FilesToLoad')

//as usual the columns and format must be correc setup -- $(File) in URL IS part will point to specific files -- **Subsite must be updated
Data:
LOAD
A,
"B",
C,
Filename()
FROM [$(vQwcConnectionName)]
(URL IS [https://YOURWEBCONSERVER:5555/data?connectorID=Office365Connector&table=GetFile&subSite=YOURSUBSITEf...],
ooxml, embedded labels, table is Sheet1);

//next file in loop
Next

P-O_Davidson
Employee
Employee

The Office 365 Sharepoint storage connector is available in Qlik Sense SaaS. It was released in May 2021 and is working similar to other file storage connectors (e.g. S3, Google Drive, etc.). Additional general improvements will be added to these connectors over time, with better filtering and browsing experience.

Status changed to: Closed - Already Available