topic Re: load multiple .xlsx files in a folder in SharePoint... in QlikView

load multiple .xlsx files in a folder in SharePoint...

QlikviewRaj11 — Mon, 17 Aug 2020 21:54:02 GMT

Hi , could someone please help with the below..

I have a share point with folders like DEV, QA, UAT, PRD these are sub folders under a folder called REPORTS

I am trying to load all of the .xlsx files into 1 single table ... in Qlikview.

FactTemp:
Load '' as Temp AutoGenerate 0;

https://sp.mycompany.net/sites2/spqtfssvvl/StoreTrust%20Management/BCCI/Reports%20-%20BCI%20-%20DEV]

This is how my SP URL looks like if I copy and paste into my inranet it shows me all of the .xlsx files in DEV folder

Set vDirectory =[https://-------- 20DEV/];

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 [DT]);

next FoundFile

next FileExtension

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

call ScanFolder(SubDirectory)

next SubDirectory

end sub;

Syntax error

Unexpected token: ':', expected one of: ',', ')'

sub ScanFolder(https>>>>>>:<<<<<<//sp.mycompany.net/sites2/spqtfssvvl/StoreTrust%20Management/BCCI/Reports%20-%20BCI%20-%20DEV)

There is something wrong in this.. could someone help ...I also need to adjust the file name in the FROM Clause

Re: load multiple .xlsx files in a folder in SharePoint...

QlikviewRaj11 — Tue, 18 Aug 2020 18:07:26 GMT

Anyone used the Qlikview to read the .xlsx files from a sharepoint folder

I have folders like DEV,QA,PRD - Each folder has few excel files - I cannot use the path/*.xlsx from sharepoint

how do I get the list of excel file names into a table and pass each file name into the FROM clause

tried something from other but I get the FileText is not a field error - any help

Re: load multiple .xlsx files in a folder in SharePoint...

Brett_Bleess — Tue, 01 Sep 2020 15:52:55 GMT

You can have a look at the Office365 SharePoint connector, but that one does require a license, just FYI. The Help on this does not recommend looping etc. due to API limitations, so wondering if that may be what you are running into here:

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-Source-Connectors/Office-365-Sharepoint-Connector.htm

Best I have, might want to do some searching on the SharePoint forums to see if you can find something there, as I would suspect this is more a SharePoint issue than a QlikView issue in this case.

Regards,
Brett

Re: load multiple .xlsx files in a folder in SharePoint...

Brett_Bleess — Thu, 03 Sep 2020 10:55:08 GMT

See duplicate post: https://community.qlik.com/t5/QlikView-App-Development/can-someone-hlep-on-figureing-out-how-this-is-working/m-p/1736902#M452686

Regards,
Brett