Our example Excel worksheet contains expenses for multiple months. Each worksheet represents a month. However, there are also Excel worksheets with information that is not relevant to QlikView. These worksheets need to be skipped. We will be able to identify which worksheets to read by looking for key information in certain columns. We are also going to show how to read all the Excel files in a directory. This will allow our users to simply copy their Excel files to a single directory.
Please note that this example is developed using Excel 2007. The ODBC Driver is specific for Excel 2007 and later versions of Excel. This logic will probably not work in versions of Excel prior to Excel 2007.
This example is developed using QlikView 10 but should work fine in previous versions.
Excel File Layout
Our Excel file layout is relatively simple. We require our users to enter their expenses in the format below. We specifically look for the work ‘Employee:’ in cell A1. We then look for the name in the next cell.
Worksheets that do not contain the word ‘Employee:’ in cell A1 are skipped.
Auto Travel to Boston
Auto Travel from Boston
Read the Excel Files and Worksheets in a Directory
The first step is to read in the Excel worksheets in the directory specified in our variable vExpenseDirectory. This variable is set by the user in the Setup sheet.
The script below reads in all of the Excel files in the directory and then loads the worksheet names into the ‘Tables’ table.
Let vDir = '$(vExpenseDirectory)' & '*.xlsx';
for each vFile in filelist (vDir)
// Read in the worksheet names of the Excel file.
$(vFile); Extended Properties="Excel 12.0
Extended Properties="Excel 12.0
// Make sure to disconnect from the ODBC drive
drop table Tables;
The script below iterates through the worksheets looking for the text ‘Employee’. One note is the worksheets are internally stored with a trailing ‘$’ character. We need to remove this character for QlikView.
For nIndex = 0 to noofrows('Tables') – 1
Let vWorkSheet = peek ('TABLE_NAME', nIndex,'Tables');
// Remove the $
if right (vWorkSheet,1) = '$' Then
Let vEmployee = trim(upper(peek('A',0,'Temp')));
Let vName = trim(peek('B',0,'Temp'));
drop table Temp;
if upper(left(vEmployee,8)) = 'EMPLOYEE' Then
Read in the Data
After we have identified the worksheet, the last step is to simply load the data into the Expenses table. Note that we take advantage of the QlikView feature that will automatically concatenate data with the same field names.
// Read in the Excel File
LOAD '$(vName)' as Name,
Date(Date,'MM/DD/YYYY') as %Date,
(ooxml, embedded labels, header is 2 lines, table is
len(trim(Date)) > 0;
Display our results
While not the main focus of this article, an informative display is always the icing on the cake! Note the example of a pie chart displaying both values and percents.
I always welcome your feedback. Please write to me at firstname.lastname@example.org and visit our blog at http://www.qlikviewexamples.com.
Downloading the Example
Please click on the line below to download the example.
Download the file
Download the file