topic Re: Load Multiple Excel Files from SharePoint via Web Connectors in App Development

Load Multiple Excel Files from SharePoint via Web Connectors

qlikdash2000 — Sat, 16 Nov 2024 17:44:36 GMT

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='/sites/STS20140212073005/path/to/directory/file01.xlsx')
8tkt8kt8-rd54-hgfs-5ys5-ytjdtr45shs5	https://share.companyx.com/sites/ABC20140212073005/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/STS20140212073005/path/to/directory/file02.xlsx')
2aag5hsn-26sr-57dd-26hs-6js5shs5hssh	https://share.companyx.com/sites/ABC20140212073005/_api/Web/GetFileByServerRelativePath(decodedurl='/sites/STS20140212073005/path/to/directory/file03.xlsx')
...	...

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?

Re: Load Multiple Excel Files from SharePoint via Web Connectors

msKarthikeyan — Sat, 05 Dec 2020 08:06:41 GMT

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

Re: Load Multiple Excel Files from SharePoint via Web Connectors

qlikdash2000 — Sun, 06 Dec 2020 14:14:52 GMT

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

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?

Re: Load Multiple Excel Files from SharePoint via Web Connectors

NellyAcko — Fri, 23 Apr 2021 15:47:54 GMT

Did you ever get this to work?