topic Re: How to pull data from multiple excel files from SharePoint document library using loops in QlikView

How to pull data from multiple excel files from SharePoint document library using loops

apoorvasd — Wed, 10 Oct 2018 10:06:14 GMT

Hello Everyone,

I am trying to pull data from excel files which are stored in SharePoint document library. There are multiple excel files in this site and I would want to pull data from all these files at one go. How can I do this?

Thought of using a for loop and a variable which holds the text that is after "Cost Breakdown". But there will be more files added to this library and I can't predict what could be the date in the date part of the file name. Hence not sure how to pull data from these files.

Any help on this?

Thank you.

Re: How to pull data from multiple excel files from SharePoint document library using loops

niclaz79 — Wed, 10 Oct 2018 11:19:04 GMT

Hi,

I normally use the following way of looping a Sharepoint-folder for specific files. Obviously you will need to replace parts of it to fit your specific excel files.

tempFileList:

LOAD

PurgeChar(trim([Name ]), chr(160)) as rawFileName,

right(purgeChar(trim([Name ]), chr(160)),8) as sortOrder

FROM

[$(DataFolder)]

(html, unicode, embedded labels, table is @5);

Filelist:

LOAD

concat(chr(39) & rawFileName & chr(39), ',', -sortOrder) as Filename

Resident tempFileList

where rawFileName = Condition;

Drop table tempFileList;

LET vFileList = peek('Filename',-1);

Drop table Filelist;

for each vFile in $(vFileList)

LET vFileFormatted = Replace('$(vFile)', ' ', '%20');

LET vTmpFileName = '[$(vDataFolder)$(vFileFormatted)' & '.xlsm] (ooxml, embedded labels, header is 1 lines, table is [TabName])';

Temp:

LOAD

FileName() as FileName,

FROM

$(vTmpFileName);

Re: How to pull data from multiple excel files from SharePoint document library using loops

apoorvasd — Thu, 11 Oct 2018 05:15:59 GMT

Hi Niclas,

Thanks for your suggestion. I have couple of questions as mentioned in the comments below.

tempFileList:

LOAD

PurgeChar(trim([Name ]), chr(160)) as rawFileName, //"Name" - does it mean name of the file?

right(purgeChar(trim([Name ]), chr(160)),8) as sortOrder

FROM

[$(DataFolder)] //How do I get the link to a SharePoint folder?

(html, unicode, embedded labels, table is @5);

Thank you.

Re: How to pull data from multiple excel files from SharePoint document library using loops

apoorvasd — Fri, 12 Oct 2018 10:52:32 GMT

Hi Niclas,

Did you get a chance to look into my questions?