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? 

[rwunderlich]

As you've discovered, you can't use a wildcard in a load from an http source.  Instead , you have to build a list of files and then loop through those files using a FOR loop, converting the filename to the full URL for the LOAD...From.

The first trick is to get a list of files. Two options to try:

1. Load the directory page as an HTML page using the webfile wizard. If you can load the list as a table, you are good to build the FOR loop.

2. Another approach I've used is to use an FTP link with the webfile wizard to get a list of files in the directory. FTP is unlikely to work with sharepoint though.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[tripwireqv]

Well, I have not yet been successful, but I can generate a list of file names.  I suspect my problem is syntax in the FOR EACH statement.  It runs through the FACT load, and then just hangs.

Set vDirectory =[https://Sharepoint/Directory];

Filenames:

LOAD left(FileText,29) & '.xlsx' as FileName,

     mid(FileText, 20,10) as CreateDate,

     Trim(Replace(left(FileText,29) & '.xlsx',' ','20%')) as Webfile,

     ModifiedDate

FROM $(vDirectory)

(html, codepage is 1252, embedded labels, table is @6, filters(

Top(3, 'FileText'),

Top(4, 'ModifiedDate')

));

Fact: 

Load '' as Temp AutoGenerate 0;

For each Webfile in Filenames

set vCurrentFile = $(Webfile)

Concatenate (Fact)  

   LOAD   *

   FROM  [$(vDirectory)&$(vCurrentFile)]    <---

   (ooxml, embedded labels, table is [Detail]);

next Webfile;

[rwunderlich · Accepted solution]

You are on the right track. Problem is in your loop of the Filenames. Filenames is a QV table with n rows, with the field of interest being "Webfile". So you'll need a loop that processes each row of the Filenames table and extracts the value of the "Webfile" field.

For idx = 0 to NoOfRows('Filenames')-1

  LET vCurrentFile = peek('Webfile', $(idx), 'Filenames');

  Concatenate (Fact)

  LOAD  *

  FROM  [$(vDirectory)&$(vCurrentFile)]  

  (ooxml, embedded labels, table is [Detail]);

next idx

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

[tripwireqv]

I am hoping this is still just syntax problems, but I'm back to getting Bad Zip File errors.  Have yet to prove this functions (though it looks good.)  I'm assuming it's this line being the problem

  FROM  [$(vDirectory)&$(vCurrentFile)] (ooxml, embedded labels, table is [Detail]);

I've gone with & without the "&", with a final "/" in the vDirectory variable and with it built in the FROM statement and without it at all.  The log and error message replace the variables with their values, and the resulting string looks correct in my current view, but I'm still getting Bad Zip.

[rwunderlich]

Is that the same FROM statement you get when you successfully load a single file using the wizard?

-Rob

[tripwireqv]

Yup.  Looks to be the same file, just doesn't work.

[tripwireqv]

Wait, no, I found a misplaced character.  I think I have it!  Thanks for sticking with me, Rob!

[andreicatalin]

Does this method work with qlik sense?

I am trying to do exactly the same task for a qlik sense app i am developing.

I am connected to the sharepoint via the REST connector.

I have a test folder with 6 xlsx files from which i need to load 4 tabs (structurally identical). any insight as to how to go about doing in?

[QlikviewRaj11]

Hi Rob,

could you please help on where I am going wrong.. I have the share point within the network.

I have folders setup like DEV,QA,PRD - I want to load all of the .xlsx files in say DEV folder into a table in Qlikview

Set vDirectory = 'https://sp.Company123.net/sites2/spqtfsvl/TrueStore%20Management/BCI/Reports%20-%20BCI%20-%20DEV';


Filenames:

LOAD left(FileText,10) & '.xlsx' as FileName,
//mid(FileText, 20,10) as CreateDate,

//Trim(Replace(left(FileText,29) & '.xlsx',' ','20%')) as Webfile,

ModifiedDate

FROM $(vDirectory) (html, codepage is 1252, embedded labels);

I cannot get the first step that is getting the list of all .xlsx files into my table called FileNames - the error is FileText is not found - any help - once I get the list have to load each file into a table