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

[espen31415]

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

[SBDataspark]

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