Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikdash2000
Contributor II
Contributor II

How to LOAD files from a `for` loop

I've created a sheet in Qlik Sense with a table. The table lists all Excel files, through Web Connectors, that are located in an Office 365 SharePoint location. All Excel files are similar, based off the same template. On my Qlik Sense sheet I've chosen to list the unique ID for each file, and the path to the file. It looks like this:

ListFiles_UniqueIdListFiles_id
1cccww47-1702-21be-9b07-8n23858205syhttps://share.companyx.com/sites/ABC201402/.../file01.xlsx')
8tkt8kt8-rd54-hgfs-5ys5-ytjdtr45shs5https://share.companyx.com/sites/ABC201402/.../file02.xlsx')
2aag5hsn-26sr-57dd-26hs-6js5shs5hsshhttps://share.companyx.com/sites/ABC201402/.../file03.xlsx')
......

 

I've also got a `for` loop in my code. I want this `for` loop to combine all these Excel files, and all the sheets within each Excel file, into one giant table in Qlik. That will be my "master table". I thought the `for` syntax below would achieve this but it doesn't. In my data model viewer I've only got one table, an `Office365Connector_ListFiles` table. I want another table in my data model viewer that is the aggregation of all my Excel files. It would have all the columns from my Excel workbooks (eg "Date", "Store", "Sales", etc.)

What do I do? Here's the code:

 

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)';

Office365Connector_ListFiles:
LOAD
           UniqueId as ListFiles_UniqueId,
           id as ListFiles_id
FROM [$(vQwcConnectionName)]
(URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fABC20140212073005&folder=%2fsites%2fSTS20140212073005%2fMSQR%2f2.+Reporting%2f2020-04&maxResults=40&appID=&loadAccessToken=mcd2x4lxzk], qvx);

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)'; Office365Connector_ListFiles: UniqueId as ListFiles_UniqueId, id as ListFiles_id FROM [$(vQwcConnectionName)] (URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fABC20140212073005&folder=%2fsites%2fSTS20140212073005%2fMSQR%2f2.+Reporting%2f2020-04&maxResults=40&appID=&loadAccessToken=mcd2x4lxzk], qvx);
 

for each file in filelist('lib://vQwcConnectionName/path/to/directory/*.xlsx')
load * from [$(file)](xlsx);
next file

 

 

2 Replies
pradosh_thakur
Master II
Master II

You are correct till here

 

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)';

Office365Connector_ListFiles:
LOAD
           UniqueId as ListFiles_UniqueId,
           id as ListFiles_id
FROM [$(vQwcConnectionName)]
(URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fABC20140212073005&folder=%2fsites%2fSTS20140212073005%2fMSQR%2f2.+Reporting%2f2020-04&maxResults=40&appID=&loadAccessToken=mcd2x4lxzk], qvx);

 

Then load one file from the listed files above using get file an copy paste the code, there would be a parameter field in the url, you have to pass a variable t fetch that. enclose them between like below

 

FOR Each file_i in FieldValueList('TYPE')

load * from [ your connection name etc](where url is ...... field=$(file_i)&...... token = .....);

next

Learning never stops.
qlikdash2000
Contributor II
Contributor II
Author

Thank you @pradosh_thakur  I did use getFile and the getFile URL is this

http://qlikselfservice:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fRootDir&fileId=v39mrxx7-c90a-4mn9-cc84-73e26k5002t3&appID=&loadAccessToken=owp8x0uwmm 

In that getFile URL I went ahead and replaced:

fileId=v39mrxx7-c90a-4mn9-cc84-73e26k5002t3&

with:

field=$(file_i)&

so that the entire script looks like:

let vQwcConnectionName = 'lib://General sharepoint connector (userID123)';

Office365Connector_ListFiles:
LOAD
           UniqueId as ListFiles_UniqueId,
           id as ListFiles_id
FROM [$(vQwcConnectionName)]
(URL IS [http://qlikselfservice:5555/data?connectorID=Office365Connector&table=ListFiles&subSite=%2fsites%2fABC20140212073005&folder=%2fsites%2fSTS20140212073005%2fMSQR%2f2.+Reporting%2f2020-04&maxResults=40&appID=&loadAccessToken=mcd2x4lxzk], qvx);

FOR Each file_i in FieldValueList('TYPE')
LOAD *
FROM [$(vQwcConnectionName)]
(where url is http://qlikselfservice:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fRootDir&fileId=$(file_i)&appID=&loadAccessToken=owp8x0uwmm);

next

BUT, it doesn't work. I must still be doing something wrong? How do I fix this?