Qlik Sense Saas - Need to perform a folder scan (excel files)

espen31415 — Thu, 03 Feb 2022 08:32:48 GMT

Hi,

I have been working in Qlikview running a loop that scan all my excel files inside a folder and add them to a list for further use... However, this code does not work in my Qlik Sense Saas cloud solution.

I moved all my files to a Onedrive directory, created a connection, but the code fails.

This is my script:

Set vConcatenate = ;

FinanceplanList:
LOAD
'' As %FileStamp,
'' AS FSource,
'' As FName,
'' As FTime,
'' As _Processed
AUTOGENERATE 0;

sub ScanFolder (Root)

for each FileExtension in 'xls*'

for each FoundFile in filelist( Root & '/*.' & FileExtension)
FileName = Mid(FoundFile, Len(Root)+2, Len(FoundFile)-Len(Root)-6) //trying to pick the filename without path and extension

If WildMatch(FileName, '*5 yrs financeplan new version*') Then //Searching for financeplan

FinanceplanList:
LOAD
'$(FoundFile)' as FSource,
FileName() & ' - ' & Timestamp(FileTime(), 'dd.MM.yyyy hh:mm:ss') As %FileStamp,
FileName() As FName,
FileTime() As FTime,
'0' As _Processed
From '$(FoundFile)';
//AUTOGENERATE 1;
Set vConcatenate = Concatenate;
End If
next FoundFile
next FileExtension

for each SubDirectory in dirlist( Root & '/*' )
call ScanFolder(SubDirectory)
next SubDirectory
end sub

Call ScanFolder('$(vFinanceFolder)');

Store FinanceplanList Into '$(vL.ExtractPath)FinanceplanList.qvd'
(qvd)
;
Drop tables FinanceplanList
;

I would be happy if anyone could guide me in the right direction here.

Regards E

Re: Qlik Sense Saas - Need to perform a folder scan (excel files)

SBDataspark — Thu, 24 Feb 2022 15:34:21 GMT

Hi there espen31415,

Maybe this blog post can help you solve your issues:

Looping through cloud-based file data using a meta... - Qlik Community - 1796499

Please take into account that you can't save files in OneDrive from Qlik Sense:

OneDrive ‒ Qlik Connectors

Limitations and known issues

When authoring load scripts for use with the OneDrive Web Storage Provider Connector, there are specific limitations to be aware of.

The STORE command is not supported (due to an API limitation around total file size).
You cannot use a wildcard or masking syntax to only return files matching a specific pattern.

Kind regards,

Sebb

topic Qlik Sense Saas - Need to perform a folder scan (excel files) in App Development

Qlik Sense Saas - Need to perform a folder scan (excel files)

Re: Qlik Sense Saas - Need to perform a folder scan (excel files)

Limitations and known issues