Database driver XLSX

Overview

This guide provides instructions on how to set up and use XLSX files with Team Edition. The XLSX driver allows you to work with XLSX data as if it were in a database. You can retrieve data and apply filters, sorting, and other operations, even combining data from multiple files.

Before you start, you need to create a connection in Team Edition and select the XLSX driver. If you haven’t done this, see our Database Connection article.

Tip: You can open and work with XLSX files stored in your cloud storage directly in Team Edition. For setup instructions, see Cloud Storage.

Important: When using the XLSX driver, all connected XLSX files are read-only. To make changes, you need to update the original files outside Team Edition.

XLSX Files driver connection settings

This section describes how to set up a connection using the XLSX driver. The connection settings page requires the following fields:

Field	Description
Connect by (Host/URL)	Choose whether to connect using a local host path or a URL.
File paths	Specify the location of the XLSX file(s). You can:
	- File: Select a single XLSX file.
	- Folder: Choose a directory containing multiple XLSX files.
	- Remote: Access a remote folder via Cloud Storage. This feature is available only in Ultimate and Team Editions.
Driver name	This field will be auto-filled based on your selected driver type.
Driver Settings	If there are any specific driver settings, configure them here.

Tip: When using the Folder option, Team Edition scans the directory up to two levels deep for XLSX files. For more information, see folder structure. If you select a folder, Team Edition organizes files in schemas based on their directory structure.

Features and capabilities

Advanced SQL query capabilities

The XLSX driver supports the full range of SQL queries:

• Simple queries (e.g., SELECT * FROM table): Data is read directly from the XLSX file.

• Complex queries (e.g., using WHERE, JOIN, ORDER BY, GROUP BY): When a complex query is executed for the first time, the driver imports the entire XLSX file into an internal database to enable advanced SQL functions. Subsequent queries run faster because the data is already imported into internal database.

Structuring XLSX files with a schema

XLSX files don’t include metadata about their structure, such as column names or data types. To enhance how Team Edition interprets these files, you can define a schema using a DDL (Data Definition Language) file.

Why use a DDL file

A DDL file helps Team Edition interpret your data more accurately by defining:

• Column names
• Data types

• Optional indexes for better performance

How to create a DDL file

1. Create a .ddl file with the same name as your XLSX file, placing it in the same directory (e.g., employees.xlsx and employees.xlsx.ddl).
2. Write a schema using the CREATE TABLE statement:

CREATE TABLE employees (
  id INTEGER,
  name TEXT NOT NULL,
  age INTEGER,
  department TEXT
  );
  CREATE INDEX idx_employees_id ON employees (id);

If your XLSX file contains multiple sheets, you can define multiple CREATE TABLE statements in a single DDL file, one for each worksheet. The table names in the DDL file should match the worksheet names:

CREATE TABLE employees
(
    id         INTEGER,
    name       TEXT NOT NULL,
    age        INTEGER DEFAULT 30,
    department TEXT
);

CREATE TABLE sales
(
    sale_id  INTEGER,
    product  TEXT,
    quantity INTEGER,
    price    DECIMAL(10, 2)
);

Important: If the DDL file contains errors, Team Edition will ignore it. You can still query the file, but column types will be inferred from the data.

Folder structure

When working with a folder containing multiple XLSX files, Team Edition organizes them as follows:

Folder structure	Schema in Team Edition
Root files	Default schema
Subfolder files	Schema named after the subfolder
Files in deeper folders	Ignored

Note: Each file becomes a schema, and each sheet in a file becomes a table in that schema.

If your folder looks like this:

Data/
├── employees.xlsx
│   ├── Sheet1
│   ├── Sheet2
├── sales.xlsx
│   ├── SalesData
│   ├── Summary
└── Reports/
    ├── monthly.xlsx
    │   ├── January
    │   ├── February
    ├── yearly.xlsx
        ├── 2023
        ├── 2024

Team Edition will create:

• Schema: Default
  Tables:

  • employees.Sheet1
  • employees.Sheet2
  • sales.SalesData
  • sales.Summary

• Schema: Reports
  Tables:

  • monthly.January
  • monthly.February
  • yearly.2023
  • yearly.2024

Tip: To focus on specific files, consider selecting individual files or folders when configuring the connection.

Internal database

When you execute a complex query (such as WHERE, JOIN, GROUP BY, or ORDER BY.), on an XLSX file for the first time, the XLSX driver processes the data by importing it into a temporary internal SQLite database.

By default, this internal database stores data temporarily on the disk during your session and is cleared when Team Edition restarts. To speed up queries on the same file in future sessions, you can specify the internalDbFilePath option in the Driver properties tab (e.g., C:\User\database.db) to reuse the processed data.

Additional features

Team Edition provides additional features compatible with XLSX driver, but not exclusive to it:

Category	Feature
Data Transfer	Data Export
Data Visualization	Visual Query Builder
	Charts