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.
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:
In the Edit Script window, click on “Table Files…” in the Data from Files section:
Navigate to the Excel file you would like to import, select it and click Open:
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:
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” :
In the Labels drop-down, select “Embedded Labels” to specify that your Excel tables has column headers:
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:
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:
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:
The next step of the Wizard allows you to specify a WHERE clause for your data. There are three options:
- Simple – this is an easy way to specify selection criteria for your import, done through pre-formatted drop-downs in the Wizard
- Advanced – in this option, you have the ability to type in an actual WHERE clause of your own
- Empty Template – This option will insert “Where (1=1)” into the script and it is possible to edit it further there
There is also a section for Prefixes:
- 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
- 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)
- 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:
If you check the box for “Load All (*)”, your script will look like this:
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:
… and you will see your Excel Table has successfully loaded into the Qlikview app: