topic Load data from Excel files based on the file name in QlikView

Load data from Excel files based on the file name

Thu, 01 Jul 2010 08:41:58 GMT

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

Load data from Excel files based on the file name

Thu, 01 Jul 2010 09:03:32 GMT

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!

Load data from Excel files based on the file name

sparur — Thu, 01 Jul 2010 09:04:11 GMT

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

Load data from Excel files based on the file name

Thu, 01 Jul 2010 09:25:00 GMT

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

Load data from Excel files based on the file name

Thu, 01 Jul 2010 09:34:43 GMT

Thank you again! That is exactly what i need.

Load data from Excel files based on the file name

Thu, 01 Jul 2010 12:05:11 GMT

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.