Load a directory of XLSX from a webpage?

[tripwireqv]

I have a sharepoint site with a couple directories full of XLSX documents I'd like to load into a table.  I can successfully use the Webfiles wizard to load the data in one file, so I have permission to reach the location.  But I'd like to load all the docs (incluing those added in future, who's names I don't yet know), and I don't see a means to do so.  Anyone have different results, let me know what's wrong with my syntax!  What other commands might do the trick.

Wildcard:

Load *

From [https://sharepoint/directory/*.xlsx](ooxml, embedded labels, table is [Detail]);

This gives a zip error.  The internet suggests change ooxml to biff, but that just is the difference between xls and xlsx, as far as I see, and doesn't load the files either.

FileList:

For each vFile in FileList('https://sharepoint/directory/*.xlsx')

Fact:

    Load *

from  $(vFile)  (ooxml, embedded labels, table is [Gross Pipeline Detail]);

Next vFile;

No errors, no data (doesn't create a FACT at all.  Seems to not actually try to load files.  Other community posts imply Filelist doesn't work for websites, support didn't know this.

ScanFolder

FactTemp: 

Load '' as Temp AutoGenerate 0; 

Set vDirectory =[https://sharepoint/directory/];

sub ScanFolder($(vDirectory))  

    for each FileExtension in 'xlsx'  

        for each FoundFile in filelist( Root & '/*.' & FileExtension)  

           Concatenate (FactTemp)  

              LOAD      *

              FROM [$(FoundFile)]  (ooxml, embedded labels, table is [Gross Pipeline Detail]);

            next FoundFile  

        next FileExtension  

for each SubDirectory in dirlist( $(vDirectory) & '/*' )  

     call ScanFolder(SubDirectory)  

next SubDirectory  

end sub;

Throws a script line error on the Scanfolder - also doesn't work on web

What other ideas are out there? 

[hastiecraig]

Hi Raj,

One solution to this could be to use the XML view of the SharePoint directory to build a table of the files for you to loop through. If the directory was created within SharePoint, then while viewing the SharePoint directory in a web browser, use the Library Tab > Export to Excel option - this will generate an owssvr.iqy file which you can open in a text editor (don't open it in excel). The web address on line 3 of the file will direct you to an XML view of the folder which you can get Qlikview to read in as a list of the files to loop through.

For example:

FileNames:
LOAD ows_DocIcon,
ows_LinkFilename,
ows_Modified,
ows_Editor
FROM [http://YourCompanySharePoint/_vti_bin/owssvr.dll?XMLDATA=1&List={00000000-0000-0000-0000-000000000000}&View={0000000-0000-0000-0000-000000000000}

&RowLimit=0&RootFolder=XYZ]

//URL from owssvr.iqy
(XmlSimple, Table is [xml/data/row]);

LET vNumberOfRows = NoOfRows(FileNames);
FOR vI = 0 to (vNumberOfRows - 1)
LET vFileName = Peek('ows_LinkFilename',vI,'FileNames');
SET vFilePath = 'http://YourCompanySharePoint/Folder/$(vFileName)';

//load file here

NEXT;

Hope this helps.