Qlikview Import Microsoft Excel Data

In most jobs, people work with Excel every day.  It’s no surprise that importing Excel data into Qlikview is frequently required, which generates a lot of questions for new Qlikview users.  I often get questions on this topic, so I figured it would be a good topic to cover.

Setup

Here are the steps to accomplish this task:

I am using Excel 2013 and Qlikview Personal Edition Version 11.20.12235.0 SR5 64-bit Edition (x64)

Create a Qlikview App and save the file.

Open the Edit Script Window:

Qlikview Edit Script

In the Edit Script window, click on “Table Files…” in the Data from Files section:

Qlikview Import Microsoft Excel Data

Navigate to the Excel file you would like to import, select it and click Open:

Qlikview Import Microsoft Excel Data

This brings you into the File Wizard, which takes you through the following steps:

First, make sure the File Type is correct.  In this example, it defaults to “.xlsx” since I chose to import this type of Excel file:

Qlikview Import Microsoft Excel Data

In the Tables drop-down, select the Excel table you would like to import.  In my Excel file, there are two tabs and a named range.  I am going to import the Excel tab “FullSchedule” :

Qlikview Import Microsoft Excel Data

In the Labels drop-down, select “Embedded Labels” to specify that your Excel tables has column headers:

Qlikview Import Microsoft Excel Data

Next, if you look at the preview of the Excel Table that I chose to import, you will notice there are two header rows at the top of the worksheet:

Qlikview Import Microsoft Excel Data

Since you only want the row with actual column headers, you will select “Lines” under the Header Size drop-down.  Then enter “1” in the lines box and that will ensure the import will skip the first header row in the Excel table, and properly select the second row for the Embedded Labels:

Qlikview Import Microsoft Excel Data

Click the “Next” button to move forward in the Wizard.

The next step in the Wizard is “Transform” step.  I personally have never used this functionality, so for this example, simply click the Next button to move forward in the Wizard:

Qlikview Import Microsoft Excel Data

The next step of the Wizard allows you to specify a WHERE clause for your data.  There are three options:

  1. Simple – this is an easy way to specify selection criteria for your import, done through pre-formatted drop-downs in the Wizard
  2. Advanced – in this option, you have the ability to type in an actual WHERE clause of your own
  3. Empty Template – This option will insert “Where (1=1)” into the script and it is possible to edit it further there

Qlikview Import Microsoft Excel Data

There is also a section for Prefixes:

  1. Crosstable – Opens the Crosstable dialog where it is possible to set the parameters for a transformation of a cross table into a three- (or more) column table
  2. Hierarchy – Opens the Hierarchy Parameters dialog, where it is possible to set the parameters for a hierarchy table (parameters as they appear in the script in parentheses)
  3. Clear –  Revokes the transformation of a cross table or a hierarchy table

In this example, I am not going to perform any WHERE clauses or Prefixes, so you can click on Next in the Wizard.

The last step of the Wizard shows you a preview of the actual script:

Qlikview Import Microsoft Excel Data

If you check the box for “Load All (*)”, your script will look like this:

Qlikview Import Microsoft Excel Data

The Max Line Length is an option to make the script easier to interpret, once it is setup in the Edit Script window.

Click on “Finish” to exit the Wizard and your import script is complete!

Now you can Reload the data:

Qlikview Import Microsoft Excel Data

… and you will see your Excel Table has successfully loaded into the Qlikview app:

Qlikview Import Microsoft Excel Data

 

Additional Content

Check out more examples by visiting my Home Page

Here you will find topics covering  Qlikview  SQL Server  Excel VBA

 

Leave a Comment