Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Load data from Excel files based on the file name

Hello.I hope someone can help me on this. I have a folder with excel files. The file names are Sales123, Sales789 etc. I want Qlikview to load the excel files according to the 3 numbers in the filename,which represent the branch. I create a for loop which works fine:

for Branch=123 to 789

load Results from Sales$(Branch).xlsx (ooxml, embedded labels, table is Sheet1);

next

The problem is that the number of branches may change, so i have to load the branch names from another excel file which has 2 columns, BranchNo (1,2 etc),Branch (123,789 etc). How can i pass that parameter in the for loop? Thanks in advance

1 Solution

Accepted Solutions
sparur
Valued Contributor II

Load data from Excel files based on the file name

If I right understood, you can use a map table for according branchNames.

1) create a map table:

BranchMAP: MAPPING LOAD BranchNo, Branch FROM Excel2.xls...

2) use it map for creating excel filename

for i=1 to 10 (10 - it' example)

LET vFilename = 'Sales' & ApplyMap('BranchMAP', $(i));

load Results from $(vFilename).xlsx (ooxml, embedded labels, table is Sheet1);

next

5 Replies
Not applicable

Load data from Excel files based on the file name

You can simply place all the branch files in a folder called 'branchfolder' and folder, drop the for next loop and use

load
subfield(Filename(),'.',1) as [BranchFromFilename],
Results
from branchfolder\Sales*.xlsx (ooxml, embedded labels, table is Sheet1);


and if your branch files have the branchname/number already in a field you dont even need the subfield line!

sparur
Valued Contributor II

Load data from Excel files based on the file name

If I right understood, you can use a map table for according branchNames.

1) create a map table:

BranchMAP: MAPPING LOAD BranchNo, Branch FROM Excel2.xls...

2) use it map for creating excel filename

for i=1 to 10 (10 - it' example)

LET vFilename = 'Sales' & ApplyMap('BranchMAP', $(i));

load Results from $(vFilename).xlsx (ooxml, embedded labels, table is Sheet1);

next

Not applicable

Load data from Excel files based on the file name

Thanks for the reply. The reason i use the for loop is that i want to load the excel files in groups, not all at the same time. What if the excel file that contains the branches has a column called group (A,B etc) and i want to load only the branches of group A? Thank you

Not applicable

Load data from Excel files based on the file name

Thank you again! That is exactly what i need.

Not applicable

Load data from Excel files based on the file name

It works ok, but i have another question. What can i do if the excel file with the branches has a column named 'group',with values(A,B etc), instead of BranchNo and i want to load only the files from the branches in group A?All the files have to be in the same folder.Thanks again.

Community Browser