QlikView Load Data From Files – 10 Types of QlikView Data Files
QlikView offers a wide range of files from which data can import and analysed making it a wholesome tool. In this QlikView Load Data tutorial, we will discuss the different types of files from which data can load in QlikView along with learning how to load data in QlikView.
So, let’s start QlikView Load Data from Files.
2. QlikView Data Files
In QlikView, there are a total of 10 types of QlikView data files from which data can load. The QlikView Data Files are:
i. Delimited Files
Delimited files are also known as CSV files which stands for Comma Separated Values. In CSV files the field names and field values separated by a comma or a semicolon or tab. These files also refer to as delimited text or just text files as it contains plain text. The file extension is ‘.csv’.
ii. Fixed Record Files
Fixed Record files have the data records or rows of equal length or byte size. In such files, the length of records is fixed and can specify in the script command by (fix n) expression, where n is the number of bytes or characters deciding the record length. The fixed files stores in the system with extension ‘.fix’.
iii. Dif Files
Dif files are called Data Interchange Format files. Such is said to be of predominant use in import/export tasks. They can only contain the data of a single spreadsheet. The extension ‘.dif’ use for Dif files.
iv. Excel (XLS) Files
Microsoft Excel files, also known as XLS files can load into QlikView. More precisely, XLS stands for Excel spreadsheet. As the name suggests XLS files store data create in MS Excel document. The extension use by XLS files is ‘.xls’.
v. Excel (XLSX) Files
XLSX files are the file extensions for XML spreadsheet format use to store data from MS Excel documents. For the XML part adds in the picture the extension use for these files are ‘.xlsx’.
vi. HTML Files
HTML or web files are the files containing information on a web page. As you must already know, HTML stands for HyperText Markup Language. Both ‘.htm’ or ‘.html’ is acceptable as extensions for web files.
vii. QVD Files
QVD files are the QlikView Data files that are unique to QlikView and can only read or write in QlikView. These files predominantly use as storage and as intermediate files by QlikView services. The extension used for such files is ‘.qvd’.
viii. QVX Files
QVX is a QlikView data exchange file. A QVX file is a file format that contains metadata about the data table along with the actual data to load in that table. QVX use in QlikView to ensure high performance while data loading as it will be faster to load data using QVX files by reading the instructions contain in the metadata about the structure of the table. The file extension is ‘.qvx’
ix. XML Files
XML is an extension, which uses for files called Extensible Markup Language. Such files contain information about a data file along with the file’s data. This file can share via the web. Much like HTML, XML format also uses tags to describe the data. The file extension is ‘.xml’.
x. KML Files
KML stands for Keyhole Markup Language. This file format uses for defining and containing geographical data which use by earth browsers. Similar to XML, KML use tag-based data describing the structure and used case-sensitive tags. There is a standard set of KML tags and only those must use to describe data in a KML file. The file extension is ‘.kml’.
3. QlikView Load Data from Files
Data from any of the above discussed ten files can load into QlikView from the Script Editor. You can invoke the script editor either by the icon given on the toolbar or by Ctrl+E keys. In the script editor, selection of the data files takes place in a File Wizard which can invoke by two ways; one way is from the Insert option given at the top bar of the script editor. You can go to Insert>Load Statement>Load from file.
The second way is by using the options given at the bottom pane of the script editor under the name of Data from files. You can open the File Wizard by selecting Table Files.. option.
Binary [C:\users\hp\Desktop\Dataflair\products.qvw]; SalesMonthold: INPUTFIELD Product_Id, Product_Line, Product_category, Product_quantity, Product_cost, [Store Location], City, Month; LOAD Product_Id, Product_Line, Product_category, Product_quantity, Product_cost, [Store Location], City, Month FROM [C:\Users\HP\Desktop\Dataflair\Salesold.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq); Concatenate SalesMonthNew: LOAD Product_Id, Product_Line, Product_category, Product_quantity, Product_cost, [Store Location], City, Month FROM
4. How to Load Data in QlikView?
Below are the few steps, which help us to load data in QlikView:
i. Step One
The first step in loading data from files is to browse and select a file from the Open Local Files dialog. Set the file type to All table files if you want to browse and select from all available file types. Select a file and click Open.
ii. Step Two
Next, you will see a File Wizard: Type dialog which has two panes; a file type pane showing the entire list of file types and a table preview pane along with some other options related to the table. The file type of the file selected by you will read by the tool and will indicate in this dialog with a radio bullet in front the name of that type. Also, the preview pane will display how QlikView has interpreted the data in the tabular structure. You can select Embedded Labels form the Labels option to display the field names in the first row of the table.
iii. Step Three
The File Wizard has several other tabs as well but usually, those properties are not needed to change and so you can click on Finish just after seeing the table preview and settings some options in this dialog. Upon clicking Finish, you will exit the wizard and the table statement will load in the script. Save this script and the QlikView document as well (if not saved already). In order to use this data in QlikView, reload (Ctrl+R) the script to fetch and use the loaded data.
LOAD [Order ID], [Custmer ID], [Place ID], [Product ID], [Sales ID], Sales, Quantity, Discount, Profit FROM [C:\Users\HP\Desktop\Dataflair\Sale details.csv] (txt, codepage is 1252, embedded labels, delimiter is ',', msq);
iv. Step Four
If, instead of clicking on Finish in the File Wizard: Type dialog, you choose to continue with exploring more options then click on Next>. You will see a File Wizard: Transformation dialog, continuing with which you can transform the newly loaded data in certain ways. We have a dedicated tutorial article on this topic on Data Transformation Wizard which you can refer to if you want to know about this step in detail.
v. Step Five
Upon clicking Next>, we come to the next dialog which is File Wizard: Options dialog. Here, you can see the results of the operations you did while transforming the data in the previous step. Along with this, you can edit the Label settings, add Where clauses, and set parameters for crosstables and hierarchy tables.
Vi. Step Six
On continuing in the file wizard, the next dialog is File Wizard: Script dialog. Here, you can set how the script should look in the script editor. You can set a Max Line Length for ease of interpretation and using the Load All(*) option, you can command it to load all the fields from the loaded file into the script.
So, this was all about QlikView Load Data. Hope you like our explanation.
So, this concludes our tutorial on QlikView Load Data. In this lesson, we saw what are the different file types form which data can load into QlikView. We also learned how to load files from the system into QlikView. We hope you can smoothly load any type of file into QlikView now, with the methods we just learned. Still, have a confusion regarding QlikView Load Data? Feel free to ask in the comment box.
Related Topic – QlikView Dollar Sign Expansion in QlikView