Google wave wordpress plugin test

QlikView – load all sheets from excel file

Hi,

today i article about loading all excel files from specific directory (Loading All of the Files from a Folder). And i remember that i have experience(problems :-) ) with loading all sheets from excel file(s).

So the situation was that i wanted to load all sheets from all excel files in specific folder. The problem was that file names of files which will be placed in directory can be various but struvture of sheets will be equal to all files but the number of sheets will not be equeal (some files will have 2 sheets other may have 5 etc.) So the script that is handling this may be writen by two ways(!!!!!) – by naming sheets in qv script or naming them in external file.

Naming in script:

// Set ErrorMode =0 QlikView will simply ignore the failure and continue script execution
// This is done because in some file some sheet may be missing
set ErrorMode=0;

// Naming all posible sheets
for each sheet in 'Sheet1','Sheet2','Sheet3','Sheet4','Sheet5','Sheet6'
LOAD
Year,
Month,
SalesSum
// Load from all xls files
FROM [*.xls] (biff, embedded labels, table is $(sheet)$);
next

// Return error mode to default value of 1 - script execution will halt and the user will be prompted for action
set ErrorMode=1;

Naming in external file:

External file (txt) will hold names of loaded sheets. For example:

SheetNames.txt

‘Sheet1$’,'Sheet2$’,'Sheet3$’,'Sheet4$’,'Sheet5$’,'Sheet6$’

So the qv scrit will look like this:

// Set ErrorMode =0 QlikView will simply ignore the failure and continue script execution
// This is done because in some file some sheet may be missing
set ErrorMode=0;

// For each sheet described in SheetNames.txt load:
For each sheet in $(Include=SheetNames.txt)
Sales:
LOAD
Year,
Month,
SalesSum
// Load from all xls files
FROM [*.xls] (biff, embedded labels, table is $(sheet));
Next

// Return error mode to default value of 1 - script execution will halt and the user will be prompted for action
set ErrorMode=1;