topic Re: Load a directory of XLSX from a webpage? in QlikView

Load a directory of XLSX from a webpage?

tripwireqv — Thu, 22 Jun 2017 18:18:56 GMT

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?

Re: Load a directory of XLSX from a webpage?

rwunderlich — Thu, 22 Jun 2017 18:40:28 GMT

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

Re: Load a directory of XLSX from a webpage?

tripwireqv — Thu, 22 Jun 2017 22:36:07 GMT

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;

Re: Load a directory of XLSX from a webpage?

rwunderlich — Fri, 23 Jun 2017 04:26:26 GMT

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

Re: Load a directory of XLSX from a webpage?

tripwireqv — Fri, 23 Jun 2017 16:17:57 GMT

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.

Re: Load a directory of XLSX from a webpage?

rwunderlich — Fri, 23 Jun 2017 17:22:30 GMT

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

-Rob

Re: Load a directory of XLSX from a webpage?

tripwireqv — Fri, 23 Jun 2017 17:45:33 GMT

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

Re: Load a directory of XLSX from a webpage?

tripwireqv — Fri, 23 Jun 2017 17:51:24 GMT

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

Re: Load a directory of XLSX from a webpage?

andreicatalin — Wed, 01 May 2019 17:01:58 GMT

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?

Re: Load a directory of XLSX from a webpage?

QlikviewRaj11 — Mon, 17 Aug 2020 22:16:47 GMT

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

Re: Load a directory of XLSX from a webpage?

hastiecraig — Tue, 08 Dec 2020 12:42:14 GMT

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.