Load Multiple Excel Files from SharePoint via Web Connectors

[qlikdash2000]

I'm able to load a table that is a list of Excel files in a SharePoint location. This is all done via Web Connectors. The code and results look like this:

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

ListFiles_UniqueId	ListFiles_id
1cccww47-1702-21be-9b07-8n23858205sy	https://share.companyx.com/sites/ABC20140212073005/_api/Web/GetFileByServerRelativePath(decodedurl='...')
8tkt8kt8-rd54-hgfs-5ys5-ytjdtr45shs5	https://share.companyx.com/sites/ABC20140212073005/_api/Web/GetFileByServerRelativePath(decodedurl='...')
2aag5hsn-26sr-57dd-26hs-6js5shs5hssh	https://share.companyx.com/sites/ABC20140212073005/_api/Web/GetFileByServerRelativePath(decodedurl='...')
...	...

I want to combine all these Excel files into one giant master table in Qlik Sense. New files are added to the SharePoint location monthly, and would need to automatically be appended to the end of the master table. My thoughts on doing this were:

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

Unfortunately, this does not work. How can I load this list of Excel files and bind the rows into a giant master table?

[msKarthikeyan]

Hi ,

Since you have alread fetched the files list.

you can loop through the table and read the files using the file name

for i = 0 to NoOfRows('Office365Connector_ListFiles') - 1
Let vFileId = peek('ListFiles_id',$(i),'Office365Connector_ListFiles');
next i;

You need to replace the special characters with ASCII code

-Karthik 

[qlikdash2000]

Thanks @msKarthikeyan . I pasted that code at the end of my current code and it appears to be working. Everything looks like this:

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 i = 0 to NoOfRows('Office365Connector_ListFiles') - 1
Let vFileId = peek('ListFiles_id',$(i),'Office365Connector_ListFiles');
next i;

However, when I go into my data model viewer  I can't see this newly created master table. I tried to enter MasterTable: above this line, like this;

MasterTable:
for i = 0 to NoOfRows('Office365Connector_ListFiles') - 1

Adding the MasterTable: line only seems to break everything. I can't "Load Data" with the MasterTable: line in the code. How do I create or access this master table, which would be all the Excel files bound together, one after the other?

[NellyAcko]

Did you ever get this to work?