topic Loading from multiple excel files using wilcards in QlikView

Loading from multiple excel files using wilcards

Wed, 14 Jul 2010 13:49:31 GMT

Hi,

I am trying to load annual data from an excel source. Each year of data is held in a seperate excel file 'Data_2009.xls' 'Data_2010.xls' etc

I have previously used the wildcard in a load statement from QVD data files, however it does not seem to work for these excel files.

Can anyone advise what I am doing wrong? I get the data from the first file through just fine, but none of the data contained in the second file is loaded (even though a description appears in the Script Execution box showing that '000s of rows are detected).


[Data]:
LOAD
 [Date],
[Data]
FROM
Data_*.xls (biff, embedded labels, table is Sheet1$)

Loading from multiple excel files using wilcards

syed_muzammil — Wed, 14 Jul 2010 14:07:24 GMT

Can you please attach the Xcel docs.

Loading from multiple excel files using wilcards

Wed, 14 Jul 2010 14:07:25 GMT

Hi Phil,

that´s the way it normaly works.

Load for example your second excel file with a separate Load and see if you get your data.

Or: Upload a short example of your excel files (3 or 4 in a zip file).

Good luck!

Rainer

Loading from multiple excel files using wilcards

Wed, 14 Jul 2010 14:14:18 GMT

Hi,

I have the same situation and this syntax works for me:

For each vFILE in filelist('yourpath/Data_*.xls');

Load

Date,

Data

From $(vFILE) (biff, embedded labels, header is 4 lines, table is Report$);

next vFILE;

You need to adjust the name of the xls sheet, number of header lines etc.

Cheers

Fredrik

PS. How do I paste code so that it looks good?