Qlik Community

App Development

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

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikdash2000
Contributor II
Contributor II

Load Multiple Excel Files from SharePoint via Web Connectors

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_UniqueIdListFiles_id
1cccww47-1702-21be-9b07-8n23858205syhttps://share.companyx.com/sites/ABC20140212073005/_api/Web/GetFileByServerRelativePath(decodedurl='...')
8tkt8kt8-rd54-hgfs-5ys5-ytjdtr45shs5https://share.companyx.com/sites/ABC20140212073005/_api/Web/GetFileByServerRelativePath(decodedurl='...')
2aag5hsn-26sr-57dd-26hs-6js5shs5hsshhttps://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?

Labels (3)
3 Replies
msKarthikeyan
Employee
Employee

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
Contributor II
Contributor II
Author

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
Contributor II
Contributor II

Did you ever get this to work?