Extracting data from databases and data files

The data within an organization is usually stored in relational databases and data files. Extracting data is the first step toward creating a data model. This section demonstrates the steps to extract data from an MS Access database and a delimited (.CSV) file. The procedure to extract data from other relational databases is the same as the process for extracting data from MS Access.

The dataset that we will use is available publicly and covers information about routes and fares for various transport systems in Hong Kong. The original data files have been downloaded from the https://data.gov.hk/ website. This dataset can also be obtained from the Packt Publishing website.

The data connections in the Qlik Sense Data load editor save shortcuts leading to commonly used data sources, such as databases and data files. The following types of connections exist in Qlik Sense:

• ODBC database connection
• OLE DB database connection
• Folder connection
• Web file connection
• Qlik Essbase Connector
• Qlik ODBC Connector Package
• Qlik REST Connector
• Qlik Salesforce Connector
• Qlik GeoAnalytics Connector
• Web Storage Provider Connector

This recipe deals with the ODBC, OLE DB, and Folder connections. The web file connection and REST Connector will be dealt with in a separate recipe.

For the following connections, here is a short description, but we don't have recipes for them in this book:

• Qlik Essbase Connector allows data extraction from Hyperion Essbase cubes
• Qlik ODBC Connector Package allows data-extraction from several data sources, such as Google Big Query, Amazon Redshift, Hive, Cloudera Impala, IBM DB2, MS SQL Server, My SQL Enterprise Edition, Oracle, PostgreSQL, Sybase ASE, and Teradata

• Qlik Salesforce Connector allows data extraction from Salesforce reports with SOAP or the Bulk API
• Qlik GeoAnalytics Connector is a new service to make geo-analytics calculations, such as calculating the distance between points, clusters, and merging shapes
• Web Storage Provider Connector allows a connection to storage services, such as DropBox, to retrieve data from files, such as XlSX or CSV

With the exception of Qlik GeoAnalytics Connector, all connectors are free to use. GeoAnalytics Connector requires the purchase or subscription of a separate license from Qlik.

Getting ready

The dataset required for this recipe that is downloaded from the Packt Publishing website (https://www.packtpub.com/big-data-and-business-intelligence/qlik-sense-cookbook-second-edition) comes in a zipped folder called QlikSenseData. Extract all the files from this zipped folder and save them on your hard drive at the desired location.

If you are connecting to the database using Open Database Connectivity (ODBC):

1. Install the relevant ODBC drivers on your system.

For the sake of this exercise, we need the MS Access drivers. The system DSN connection can be set up through the ODBC administrator under Administrative Tools in Control Panel.

2. While setting up the ODBC connection, select the ROUTE_BUS.mdb file as the data source from the QlikSenseData folder.
3. Name the ODBC DSN connection as HongKong Buses.
4. Create a new Qlik Sense application and open the Data load editor.
5. Click on the Create New Connection and select ODBC.
6. Select HongKong Buses under System DSN.

7. Name the data connection as Qlik Sense CookBook ODBC.
8. The following screenshot shows the details we enter in the Create new connection (ODBC) window:

If you are connecting to the database using OLE DB connectivity, we can directly set this up through the editor:

1. Open the Data load editor in Qlik Sense.
2. Click on the Create new connection and select OLE DB.
3. Select the Microsoft Jet 4.0 OLE DB Provider (32-bit) driver from the provider
   drop-down list.
4. Insert the Data Source file path, which, in our case, will be the path for the
   ROUTE_BUS.mdb file in the QlikSenseData folder.
5. Name the data connection as QlikSense CookBook OLE DB.
6. The following screenshot shows the details we enter in the Create new connection (OLE DB) window:

If you are extracting the data from a data file, such as .CSV, perform the following steps:

1. Open the Data load editor in Qlik Sense.
2. Click on Create new connection and select Folder.
3. Select the location of the QlikSenseData folder, which contains our data files. Alternatively, you can enter the path of the source folder directly under Path.
4. Name the data connection as Qlik Sense CookBook Data.

5. The following screenshot shows the details we enter in the Create new connection (folder) window:

6. Once the connections are created in the Qlik Sense library, they will be seen
   as a list under Data connections in the Data load editor, as shown in the
   following screenshot:

How to do it...

If you are working with an ODBC or an OLE DB data connection, follow these steps:

1. Insert the relevant data connection string to the script by clicking on Insert connection string, as shown in the following screenshot:

2. Click on Select data under Data connections to view and extract data from the ROUTE table in the MS Access database, as shown:

3. The preview of the ROUTE_BUS.mdb table will look like the following. The fields in the table can be excluded or renamed while working in the Preview window:

4. Click on Insert script in the Preview window. This will insert the connection string as well as load the statement to the script. Make sure that you delete the duplicate LIB CONNECT TO 'Qlik Sense CookBook ODBC'; statement from your script.
5. Load the data in your application by clicking on the button.

Keep the Close when successfully finished option checked in the data load progress window. If the data is loaded successfully, the window automatically closes or else the error encountered is highlighted.

On a similar note, in order to test the Qlik Sense data files:

1. Click on the Select data option under the Qlik Sense CookBook Data connection.
2. Select the ROUTE_GMB.csv file from the QlikSenseData folder and load it in the application.
3. The preview of the ROUTE_GMB.csv table will look like the following screenshot. Make sure that you select Embedded field names under Field names. Note that the Delimiter, in this case, is automatically set to Comma:

4. Insert the script and then save and load it.

How it works...

The LIB CONNECT TO statement connects to a database using a stored data connection from the Qlik Sense library, thus acting as a bridge between our application and the data source.

There's more...

This recipe aimed to extract data from common data sources, such as RDBMSes and data files. Qlik Sense can also extract data from web files and web services. We will see this in the next section.

See also...

• The Creating a master library from the Data model viewer recipe