Loading data from multiple excel files

[#1]

I need to load the data from multiple xls files present in one folder. The different excel files contain the many sheet with different names. How can I load the data from these excel files to the one qvd provided all sheets have same data format(same no of columns and their respective details).

[#2]

Hello:

You can load multiple XLS files in one folder with this code

Load field1,
 field2,
 field3
from .\Folder\*.xls (biff, embedded labels, table is Sheet1$);

there you'll load every file in the folder, nevertheless every file need to have a "Sheet1", you can't load every sheet in an XLS file (or i don't find the way), you need to load one by one every sheet.

I hope I could help you.

Best Regards

Enrique

[#3]

Yeap Thats a working solution but what if the no of excels present in the folder are varying with time . Please help me with some other good solution.

[#4]

If the number or excels files are diferente every time that you reload the aplication this code work fine.

I have implemented this same solution in one customer, where the excel files is a very large o very short number depending of the reports of his people in the field .

[#5]

yeah but you must be having the excel files with same sheet name which I dont have Thats why I am looking for some kind of code which can handle loading the multiple excels with different sheet names with same data format present at one location

[#6]

Hi,

you can leave out the part of naming the table:

LOAD
value1,
value2,
value3
FROM \*.xls (biff, embedded labels);

For more comfort with subfolders you should have a look to following wiki:

http://community.qlik.com/wikis/qlikview-wiki/loading-data-from-multiple-xls-files-from-folder-with-subfolder.aspx

Regards.

[#7 · Accepted solution]

Something that I forgot that could be useful is that if you're XLS files' columns are added in time, you can use * instead of the fields' names.

load *
FROM \*.xls (biff, embedded labels);

save it in a QVD file and after sort the fields that you need.

Best Regards

Enrique