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.

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

This section describes two ways to set up a connection: opening a file from Cloud Storage or creating a connection using the wizard.

Note

The connection from Cloud Storage is temporary and will be removed when the session ends.

Open a file from Cloud Storage

You can open files directly from your Cloud Storage. Open Cloud Storage, find the file you need, and double-click it to create a connection. The connection will appear in the File databases folder in Database Navigator.

Tip

When using the folder path in the Database field, Team Edition scans the directory up to two levels deep for supported files. If the folder contains multiple files, Team Edition organizes them into schemas based on their directory structure. For more information, see folder 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.

Note

If you want to join data from different files, they must be opened in the same connection. To do this, use a folder path instead of a single file when creating the connection.

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.

This internal database stores data temporarily on the server during your session and is cleared when session ends.

Additional features

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

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

Info

For more details on driver properties, see File-based driver properties.