Loading a set of Excel files from a folder into QlikView

In the process of building a data model in QlikView/Qlik Sense sometimes we may require to load a set of files like .txt,.csv or .xlsx/.xls, but assume a case where we need to load a set of 100 files which are snap shots of data like complete weeks data kept on next Monday with a Date to it, like below.

Now here is the task to load all the files at once with out loading them individually and this has to happen every time when we are loading the qvw file such that all the older files plus the new file should be loaded. This can be achieved with a loop in QlikView/ Qlik Sense, below is one such script which I used in my real time. Hope this helps you.

Note: One thing to be observed here is the name of the files are the same but with a different date at the end.

//Code Used:

let vQlikTech = ‘Your Folder Path‘;

Directory $(vQlikTech);

let i= 1;

For each File in filelist (‘Risk Register_*.xlsx’)

Risks1:     //to get sequence Snopshot ID
Load
RowNo() as Test
AutoGenerate 1;

i= peek(‘Test’)+i;

drop table Risks1;

Concatenate
Load
*,
Date(Date#(subfield(SubField(FileName(),’_’, 2),’.xlsx’,1), ‘DD.MM.YYYY’))as “Snapshot Date”,
‘$(i)’ as “Snapshot ID”
FROM $(File)
(ooxml, embedded labels, table is owssvr);

Next File;