If you want to load Microsoft Excel files into QlikView, there are many functions you can use to transform and clean your data in the load script, but it may be more convenient to prepare the source data directly in the Microsoft Excel spreadsheet file. This section provides a few tips to help you prepare your spreadsheet for loading it into QlikView with minimal script coding required.
Use column headings
If you use column headings in Excel, they will automatically be used as field names if you select Embedded field names when selecting data in QlikView. It is also recommended that you avoid line breaks in the labels, and put the header as the first line of the sheet.
Formatting your data
It is easier to load an Excel file into QlikView if the content is arranged as raw data in a table. It is preferable to avoid the following:
- Aggregates, such as sums or counts. Aggregates can be defined and calculated in QlikView.
- Duplicate headers.
- Extra information that is not part of the data, such as comments. The best way is to have a column for comments, that you can easily skip when loading the file in QlikView.
- Cross-table data layout. If, for instance, you have one column per month, you should, instead, have a column called “Month” and write the same data in 12 rows, one row per month. Then you can always view it in cross-table format in QlikView.
- Intermediate headers, for example, a line saying “Department A” followed by the lines pertaining to Department A. Instead, you should create a column called “Department” and fill it with the appropriate department names.
- Merged cells. List the cell value in every cell, instead.
- Blank cells where the value is implied by the previous value above. You need to fill in blanks where there is a repeated value, to make every cell contain a data value.
Use named areas
If you only want to read a part of a sheet, you can select an area of columns and rows and define it as a named area in Excel. QlikView can load data from named areas, as well as from sheets.
Typically, you can define the raw data as a named area, and keeping all extra commentary and legends outside the named area. This will make it easier to load the data into QlikView.
Remove password protection
It is recommended to remove password protection of the Excel file prior to loading the data.
