Files and data services

Files and data services#

Querona reads common file formats from a folder with a built-in File provider — plus Excel workbooks and JSON files — and connects to REST APIs. Some file types can also be read through a standard ODBC driver or a commercial driver; the full, filterable catalogue is in Data sources.

Two ways to read files#

First register a File connection that points at the folder holding your files — in the GUI’s CONNECTIONS section, or see Create a connection. Then read the files one of two ways; both work on the same connection.

As virtual tables#

Create a virtual database over the connection and import its metadata. The DATABASE ‣ ADD DATABASE wizard does both — it creates the database and imports the objects you select (see Create a virtual database). Once imported, the files are exposed as tables: the fixed-schema types (PDF, email, text, XML) each map to a single table in the builtin schema, and each delimited or Parquet file becomes its own table. Query them by name:

-- PDF metadata, extracted text and detected tables
SELECT Path, NumberOfPages, Text, Tables FROM [Files].[builtin].[PdfDocuments];

-- one CSV file, by its table name
SELECT * FROM [Files].[dbo].[customers_csv];

-- one Parquet file, by its table name
SELECT * FROM [Files].[dbo].[sales_parquet];

These reads use the connection’s default extraction options. See the per-type articles below for each table’s columns.

Ad-hoc with OPENROWSET (BULK …)#

Read one or more files directly, without creating a database — name the connection as DATA_SOURCE and the reader as FORMAT (PARQUET / CSV / PDF / TEXT / XML / EMAIL; a CSV read takes one file per statement):

-- one file
SELECT Path, Text
  FROM OPENROWSET(BULK 'report.pdf', DATA_SOURCE = 'my_files', FORMAT = 'PDF') AS docs;

-- every PDF under 2024/, with a per-query option
SELECT Path, Tables
  FROM OPENROWSET(BULK '2024/**/*.pdf', DATA_SOURCE = 'my_files', FORMAT = 'PDF',
                  FORMATFILE_DATA_SOURCE = 'options = { engine = "lattice" }') AS docs;

Unlike the virtual tables, OPENROWSET accepts per-query extraction options through FORMATFILE_DATA_SOURCE. See OPENROWSET for the full reference.

File types#

The built-in File provider scans the connection’s folders and exposes each supported file type as a table. Fixed-schema types (PDF, email, text, XML) map to a single built-in table per type; delimited and Parquet files map to one table each.

File type	Built-in table
CSV	one virtual table per file (schema detected)
EMAIL	`builtin.EmailMessages`
File listing	`builtin.AllFiles` (one row per readable file, no content)
Parquet	one virtual table per file (schema read from the file)
PDF	`builtin.PdfDocuments`
TEXT	`builtin.TextFiles`
XML	`builtin.XmlFiles`

Other file formats#

Excel workbooks and JSON files are also read from files:

Microsoft Excel — Excel workbooks (.xlsx / .xls / .xlsb)
JSON — JSON files

Data services#

Beyond files, Querona also reads from live data services:

REST — REST API endpoints
Google Sheets — Google Sheets spreadsheets, read-only
KSeF — Poland’s national e-invoice system, synchronized into files on a schedule and queried from there