Problem Loading Excels stored in two different Sharepoint sites

Enriquemmesteo — Tue, 28 Dec 2021 20:02:26 GMT

Hello everybody,

this issue has me going crazy lately cause I can't make it work. Let's see if anybody can help me with this.

Here are the requirements:

I have to load data from excel files stored in two different Sharepoint sites. Let's call them "Site1" and "Site2".
I need to load one sheet from each file, which for "Site2" is always called "SheetA" but for "Site2" it is called "SheetA" sometimes and "SheetB" others.
The structure of the data is essentially the same for all the files.

I think to get it working for just one Site using variables, here is the script:

let vQwcConnectionName = 'lib://Share_Point';
// Get Checklist ID list
Office365Connector_ListFiles:
LOAD
UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [Site1_URL, qvx);

Table_0:
Load
'dummy' as dummyfield
AutoGenerate (0);

// Load Files

Let ErrorMode = 0; //To make sure it keeps loading if it doesn't

Set a = 1;

Let b = FieldValueCount('ListFiles_UniqueId');

Do while a <= FieldValueCount('ListFiles_UniqueId')

Let vFileID = FieldValue('ListFiles_UniqueId', a);

FOR EACH vSheet in '[Sheet1]','[Sheet2]';

EXCEL_FILES:
Load

fields

FROM [http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fSite1%2f&fileId=$(vFileID)&appID=]
(ooxml, no labels, table is $(vSheet));

NEXT vSheet

End If;

Let a = a + 1;

loop

//////END OF SCRIPT VERSION1////

Now, this version works to load all the files from one of the Sharepoints, though I would like to optimize the load.

Now the Load From statement is for each Site:

[http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fSite1%2f&fileId=$(vFileID)&appID=]
(ooxml, no labels, table is $(vSheet))
[http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%2fSite2%2f&fileId=$(vFileID)&appID=]
(ooxml, no labels, table is $(vSheet))

So there is no reason why I should't be able to change the script before using a variable called vSite.

Here is that script:

let vQwcConnectionName = 'lib://Share_Point';

// Get Site1 files ID list

Office365Connector_ListFiles:
LOAD
UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [Site1_URL], qvx);
//Get Site2 files ID
concatenate
LOAD
UniqueId as ListFiles_UniqueId,

FROM [$(vQwcConnectionName)]
(URL IS [Site2_URL], qvx);

Table_0:
Load
'dummy' as dummyfield
AutoGenerate (0);
Let ErrorMode = 0;

Set a = 1;

Let b = FieldValueCount('ListFiles_UniqueId');

Do while a <= FieldValueCount('ListFiles_UniqueId')
//files from each site have it as prefix
Let vRoute =if(left(FieldValue('ListFiles_Name', a),5)='Site1','Site1%2f&fileId=',
if(left(FieldValue('ListFiles_Name', a),5)='Site2','Site2%2f&fileId='));

Let vFileID = FieldValue('ListFiles_UniqueId', a);
let vROOT ='http://localhost:5555/data?connectorID=Office365Connector&table=GetFile&subSite=%2fsites%'&vRoutevFileID&'&appID=';
FOR EACH vSheet in '[Sheet1]','[Sheet2]';
EXCEL_FILES:
Load
fields

FROM [$(vROOT)]
(ooxml, no labels, table is $(vSheet));

NEXT vSheet

End If;

Let a = a + 1;

loop

This gives me INTERNAL SERVER ERROR

Re: Problem Loading Excels stored in two different Sharepoint sites

Brett_Bleess — Thu, 12 Nov 2020 17:06:34 GMT

@Enriquemmesteo Maybe the following Design Blog may be of some help as it is along these lines, but I am not sure:

https://community.qlik.com/t5/Qlik-Design-Blog/Loops-in-the-Script/ba-p/1473543

Regards,
Brett

topic Re: Problem Loading Excels stored in two different Sharepoint sites in Connectivity & Data Prep

Problem Loading Excels stored in two different Sharepoint sites

Re: Problem Loading Excels stored in two different Sharepoint sites