What Can You Learn in 15 Minutes About Loading Data in Qlik?

After this guide you’ll be able to:

1. Load CSV files with special characters and wrong delimiters without errors (100% success rate).
2. Write database queries that are up to 50x faster than a blanket SELECT *.
3. Choose the right load strategy for your data volume (small vs. large datasets).

Time investment: 15 min reading + 2 hrs hands-on
Prerequisite: Qlik Sense Desktop or Cloud installed
Quick win: Load a CSV file successfully in 5 minutes

How Do I Load Data from All Sources in Qlik?

1. Quickstart: How do you load a CSV file in Qlik?
2. Which load method fits your project?
3. How do you load CSV and Excel files optimally?
4. How do you connect Qlik to a database?
5. How do you load data via REST APIs?
6. What is a Binary Load and when do you need it?
7. How do you solve common load problems?
8. How do you optimize load performance?

How Do You Load a CSV File in Qlik?

You load a CSV file in Qlik using the LOAD...FROM command, which reads the file, maps the columns, and transfers them into a Qlik table. In just 3 steps you’ll have your first data in the app.

Solution in 3 steps:

1. Open the Data editor and click «Add data».
2. Select your CSV file (e.g. sales_data.csv). Qlik will attempt to guess the settings.
3. Take the generated code and adjust it in the Script editor to have full control.

The core command for reading data is the Qlik LOAD statement reference. The following code is a robust standard script for CSV files:

SalesData:
LOAD
    OrderID,
    Customer,
    Amount,
    Date
FROM [lib://DataFiles/sales_data.csv]
(txt, utf8, embedded labels, delimiter is ';');

Parameter explanation:

• SalesData: The name your table will receive in Qlik.
• LOAD...FROM: The core command for loading data from a source.
• (txt, ...): The file configuration:
  • txt: Specifies that this is a text file (applies to CSV, TXT, TAB).
  • utf8: The character encoding. UTF-8 is critical for correctly displaying special characters and accented letters.
  • embedded labels: Tells Qlik that the first row contains the column headers and should be used as field names.
  • delimiter is ';': Defines the delimiter. Excel exports CSVs with a semicolon (;) in many European locales, while the international standard often uses a comma (,).

Checkpoint: Do you see your data correctly in the table preview? Perfect! If not, check whether the delimiter is correct and read the Troubleshooting section.

Which Load Method Fits Your Project?

The choice of load method depends on three factors: data volume, update frequency, and infrastructure. This table gives you a quick decision guide.

Note: The setup time for databases or APIs includes the initial configuration of the connection (drivers, credentials, firewall rules). If you run into issues with the Data Gateway, the Data Gateway Troubleshooting Guide will help. For a full overview of all available connection types and their configuration, see the Qlik data connections overview.

How Do You Load CSV and Excel Files Optimally?

CSV and Excel files are the fastest way to get started in Qlik — setup in 5 minutes, no drivers needed. The key to success lies in configuring encoding and delimiters correctly.

How Do You Configure Different CSV Formats?

The CSV format varies by region and source system. Here are the three most common variants:

A typical English format uses a comma as the delimiter and an older character encoding:

StandardCSV:
LOAD *
FROM [lib://DataFiles/sales_data.csv]
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);

A European Excel export typically uses a semicolon and UTF-8 for correct special characters:

EuropeanCSV:
LOAD *
FROM [lib://DataFiles/sales_data.csv]
(txt, utf8, embedded labels, delimiter is ';', msq);

A tab-delimited file is often exported from legacy systems and uses the tab character t as the delimiter:

TabFile:
LOAD *
FROM [lib://DataFiles/export.txt]
(txt, utf8, embedded labels, delimiter is 't', msq);

Key parameters explained:

• codepage is 1252 vs. utf8: 1252 is an older Windows encoding. utf8 is the modern standard. Always start with utf8. Only test 1252 if special characters are displaying incorrectly.
• msq: Stands for «Modern Style Quoting». This parameter helps Qlik correctly interpret quotation marks within your data and avoid data shifting. It is good practice to always include it.

How Do You Load Excel Files Efficiently?

Excel is flexible, but significantly slower than CSV or databases for large datasets (>100,000 rows). For large Excel files it is recommended to first export them as CSV or cache them as a QVD.

Loading a single sheet named ‚Sheet1‘:

ExcelData:
LOAD *
FROM [lib://DataFiles/report.xlsx]
(ooxml, embedded labels, table is Sheet1);

With a FOR Each loop you can load multiple sheets automatically. The variable vSheet takes the values ‚Sales‘, ‚Marketing‘, etc. in sequence. Each sheet is loaded as its own table and a ‚Department‘ field is added so you can distinguish the data later.

FOR Each vSheet in 'Sales', 'Marketing', 'Operations'
    $(vSheet)_Data:
    LOAD
        '$(vSheet)' as Department,
        *
    FROM [lib://DataFiles/company_report.xlsx]
    (ooxml, embedded labels, table is $(vSheet));
NEXT vSheet

Excel performance tips:

• ooxml vs. biff: ooxml is the standard for modern .xlsx files. biff is the older format for .xls files.
• Named ranges: If you define a named range in Excel (e.g. «Sales_Data»), you can load it directly. This is more robust than cell ranges like A1:E100 because it survives the addition of rows and columns.

How Do You Connect Qlik to a Database?

Databases are the highest-performance method for large, structured datasets — up to 50x faster than CSV when configured correctly. ODBC, OLE DB, and JDBC are different protocols for communicating with databases. Always start with the native connector Qlik provides for your database.

Which Connector Do You Need?

Start with the native connector for your database. If none is available, ODBC is the universal standard.

An ODBC connection to a Microsoft SQL Server (see the Qlik ODBC/OLEDB connectors documentation for driver setup):

LIB CONNECT TO 'SQL_Server_ODBC';

CustomerData:
SQL SELECT
    CustomerID,
    CustomerName,
    Region
FROM Customers
WHERE LastModified > '2024-01-01';

An OLE DB connection, which often provides better performance for SQL Server on Windows:

LIB CONNECT TO 'Provider=SQLOLEDB;Server=sql-server;Database=Sales;Trusted_Connection=yes';

Why Is SQL SELECT + LOAD the Best Approach?

The biggest performance gain comes from the hybrid approach: let the database server filter and aggregate, and transform in Qlik. Instead of having every book in a library brought to your desk (SELECT *), give the librarian a precise list.

Here a complex aggregation is executed directly on the server. Only the result is transferred to Qlik:

FastServerQuery:
SQL SELECT
    o.OrderID,
    o.CustomerID,
    SUM(od.UnitPrice * od.Quantity) as OrderTotal
FROM Orders o
    JOIN OrderDetails od ON o.OrderID = od.OrderID
WHERE o.OrderDate >= '2024-01-01'
GROUP BY o.OrderID, o.CustomerID;

In the hybrid approach, the SQL SELECT statement fetches pre-filtered raw data from the server. Qlik-specific transformations are then applied in the LOAD statement. More on data transformations in Qlik.

FlexibleQuery:
LOAD
    OrderID,
    CustomerID,
    Date(OrderDate) as OrderDate,
    If(OrderTotal > 1000, 'High', 'Standard') as OrderCategory;
SQL SELECT
    OrderID,
    CustomerID,
    OrderDate,
    OrderTotal
FROM Orders
WHERE OrderDate >= '2024-01-01';

If you want to run SQL JOINs in Qlik rather than on the server, Qlik provides its own JOIN and KEEP commands with specific advantages for the data model.

How Do You Load Data via REST APIs?

REST APIs are interfaces for querying data from web services — ideal for cloud services like Salesforce, HubSpot, or Google Analytics. Authentication is usually done via a «Bearer Token» sent in the HTTP header.

A basic query to an API endpoint that returns a list of users:

LIB CONNECT TO 'REST_Connector';

RestParameters:
SQL SELECT
    "id",
    "name",
    "email"
FROM CSV (header on, delimiter ",", quote """") "root"
WITH CONNECTION (
    URL "https://api.example.com/users",
    HTTPHEADER "Authorization" "Bearer your-api-token"
);

How Do You Implement Pagination for Large API Datasets?

Many APIs send large datasets in «pages» (pagination). You need to retrieve the data in a loop until no new records come back. The following code queries the API page by page until fewer than 1,000 records are returned:

LET vPage = 1;
LET vHasMore = 1;

DO WHILE vHasMore = 1
    RestData_Page_$(vPage):
    SQL SELECT "id", "name"
    FROM JSON (wrap on) "root" PK "__KEY_root"
    WITH CONNECTION (
        URL "https://api.example.com/data?page=$(vPage)&limit=1000"
    );

    IF NoOfRows('RestData_Page_$(vPage)') < 1000 THEN
        LET vHasMore = 0;
    ELSE
        LET vPage = vPage + 1;
    END IF
LOOP

For robust error handling with API calls, it is recommended to add error checks after each request. And if you want to refresh API data regularly, it is worth looking at Incremental Loading.

What Is a Binary Load and When Do You Need It?

BINARY copies the entire data model of another Qlik app into your current app — instantly, without reloading the sources. It is like «cloning» the complete data model.

Important: The BINARY command must be the very first line in your script.

BINARY [lib://Apps/Master_Data_Model.qvf];

NewTable:
LOAD *
FROM [lib://DataFiles/additional_data.csv]
(txt, utf8, embedded labels, delimiter is ';');

Limitations: You cannot select which tables to load — it is always the entire model. After the Binary Load you can, however, add further tables or extend existing ones with JOIN or CONCATENATE.

In a professional environment, Binary Load is often replaced by a Three-Stage Architecture, which offers more flexibility and control.

How Do You Solve Common Load Problems?

Most load problems in Qlik come down to three causes: wrong encoding, wrong delimiters, or timeout settings. Here are the solutions.

How Do You Fix CSV Encoding Problems (Special Characters)?

Problem: Special characters like ‚ä‘, ‚ö‘, ‚ü‘ are displayed as ‚ä‘ or ‚??‘.

Solution: This is almost always a character encoding problem. Test these three formats in sequence — in 99% of cases one of them will solve the issue:

LOAD * FROM file.csv (txt, utf8, embedded labels, delimiter is ';');
LOAD * FROM file.csv (txt, codepage is 1252, embedded labels, delimiter is ';');
LOAD * FROM file.csv (txt, Unicode, embedded labels, delimiter is ';');

For a systematic approach to such issues, we recommend the article on Data Quality in Qlik.

How Do You Resolve Database Connection Timeouts?

Problem: Loading from the database stops after a short time with a timeout error message.

Solution: Your query is taking longer than the driver’s default timeout. You can increase this value directly in the connection string:

LIB CONNECT TO '...;Connection Timeout=60;Command Timeout=300';

Connection Timeout is the time to establish the connection; Command Timeout is the maximum allowed runtime for your SQL query. More tips on memory and performance problems can be found in the separate troubleshooting article.

How Do You Optimize Load Performance?

The biggest performance gains when loading come from QVD files and correct data types — together these can speed up the load process by a factor of 100 and reduce RAM usage by up to 80%.

What Is an Optimized QVD Load?

QVDs are Qlik’s native, column-optimized storage format and the fastest way to load data. An «Optimized Load» is up to 100x faster because Qlik can read the data directly into RAM without processing it row by row. More in the QVD Optimization article.

This load is optimized because only fields are selected and renamed:

FastLoad:
LOAD
    CustomerID,
    CustomerName as Customer,
    Region
FROM [lib://QVDs/Customers.qvd] (qvd);

This load is not optimized because a transformation (Upper()) is applied. Qlik must now unpack and modify each record individually:

SlowLoad:
LOAD
    CustomerID,
    Upper(CustomerName) as CustomerName,
    Region
FROM [lib://QVDs/Customers.qvd] (qvd);

Rules for an Optimized Load: No transformations (except renaming), no WHERE clause (except a simple WHERE EXISTS(Field)). If you need transformations, use the LOAD strategies with RESIDENT or Preceding LOAD.

How Do You Reduce RAM Usage with Optimized Data Types?

Correctly interpreting data types can reduce your app’s RAM usage by up to 80%. The three most important functions:

• AutoNumber(): Replaces long, repeating text keys (e.g. ‚US-2025-ABCD-1234‘) with small, unique integers. This has the biggest impact on app size.
• Date(): Converts text like ‚2025-10-05‘ into Qlik’s internal date format, which saves memory and enables date calculations.
• Num(): Ensures that numbers are stored as numbers and not as text.

OptimizedData:
LOAD
    AutoNumber(CustomerID) as CustomerKey,
    CustomerName,
    Date(Date#(OrderDate, 'YYYY-MM-DD')) as OrderDate,
    Num(Amount, '#.##') as Amount
FROM source;

Further performance strategies — from Incremental Loading to optimizing the entire data model — are covered in depth in the following course articles. An overview of all optimization options can be found in the Performance Best Practices article.