topic Re: How to LOAD files from a `for` loop in App Development

How to LOAD files from a `for` loop

qlikdash2000 — Sat, 16 Nov 2024 00:59:01 GMT

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_UniqueId	ListFiles_id
1cccww47-1702-21be-9b07-8n23858205sy	https://share.companyx.com/sites/ABC201402/.../file01.xlsx')
8tkt8kt8-rd54-hgfs-5ys5-ytjdtr45shs5	https://share.companyx.com/sites/ABC201402/.../file02.xlsx ')
2aag5hsn-26sr-57dd-26hs-6js5shs5hssh	https://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

Re: How to LOAD files from a `for` loop

pradosh_thakur — Thu, 10 Dec 2020 08:03:22 GMT

You are correct till here

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

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

Re: How to LOAD files from a `for` loop

qlikdash2000 — Fri, 11 Dec 2020 20:37:29 GMT

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?