File Query

Query and change Excel and CSV files from a workflow, the way you would work on a database table, and get the result as a new file.

File Query works on spreadsheet files without loading them into your workflow's variables. Each Task reads the file from storage, works on it in an embedded analytical engine, and writes the result back to storage as a new file. The Session only carries the file's reference (its name, type and size) and a few counts, so a file with a hundred thousand rows takes about a second and does not slow the rest of the Process down.

The Module adds four Tasks: Query file, Update rows, Delete rows and Add rows. It has no configuration.

What you can build

• Clean up an upload: a visitor sends an Excel file through a form, Query file extracts a code from a column with a regular expression, and Form reply hands the new workbook back on the form page.
• Keep a shared spreadsheet up to date: Update rows with File and Save as both ref-storage:customers.xlsx changes one row of the saved file in place, safely even when two Sessions run at the same time.
• Look something up: Query file with Variables output finds the row whose email matches {email} and gives you {record.Name} and {record.Status} to use in the conversation.
• Summaries: group the orders of a month by city with totals, sorted by amount, and send the result as an email attachment.
• Join two files: enrich an uploaded order list with the prices of a reference file kept in storage.

How it works

1. Files are references. Every file property is a file field: ref-session:{form.fields.file} from a form field whose format is Stored file (uploads are kept 7 days; save one with File Storage's Save file to keep it), ref-session:{email.attachments.0}, ref-session:{files.0} from a chat upload, or a saved file ref-storage:customers.xlsx / ref-workspace:prices.xlsx. Excel files (.xlsx) and CSV files are read; old .xls files and password-protected workbooks are not.
2. Each file becomes a table. Column names come from the first row (turn Header row off to use the letters A, B, C…). Each column gets the type its cells hold: numbers stay numbers, dates stay dates, and text stays text, leading zeros included (00123 stays 00123). CSV files are detected automatically (separator, quotes, types); a file separated by ; uses decimal commas (12,5) unless you choose otherwise.
3. The Task runs. With the builder (conditions, columns, groups, sorting) or with your own SQL.
4. The result is a new file in storage (Excel or CSV), and/or rows in variables. The input file is never changed.

Naming columns

Everywhere a field asks for a column you can write its header name (Notes, any case) or its letter (B). A letter always means that column of the input file. A column the Task computes (a regex extract, an expression) is named by its Name.

Variables are always safe

{variables} in conditions, values and SQL are passed to the engine as parameters, never pasted into the query text. Whatever a visitor types into a form, it can only be compared, never executed.

A condition whose value is empty matches no row: a visitor who leaves a field blank cannot turn "contains" or "equals" into "every row". To find empty cells, use is empty.

Tasks

Query file

Reads one or more files and writes the result of a query.

Property	Description
Tables	Source tab. The files to query, one table row each, with a Table name (default sheet), the File (a file field), the Sheet (Excel; empty: the first sheet), Header row and Decimals (CSV).
Query with	Query tab. Builder or SQL.
From table	Builder: which table to read. Empty: the first one.
Keep every input column	Builder: start the result with all the input columns. Off: only the Columns below, in their order.
Columns	Builder: Copy column, Regex extract (a pattern and the group to keep), Regex replace (a pattern and the replacement; $1 inserts group 1), SQL expression. Each with a Name.
Keep rows that match / Where	Builder: conditions on the input columns (equals, contains, starts with, matches regex, is empty, greater than, is one of…), all or any of them. Ignore case for text.
Group by / Totals	Builder: group columns (type a name, Enter) and totals (count, count distinct, sum, average, minimum, maximum). Groups and totals can use the computed columns.
Sort / Limit	Builder: sort keys and the maximum number of rows.
SQL	SQL mode: one SELECT over the tables (DuckDB dialect).
Output	Output tab. A new file, Variables, or Both.
File format / CSV separator	Excel or CSV (; by default, what Excel expects with Italian settings).
Save as	Where the new file goes: ref-session:codes.xlsx (a file of this Session) or ref-storage:codes.xlsx (a saved file). Empty name: the first table's name with -query.
If the file exists	For ref-storage: only: replace it only if unchanged since it was read (default), always replace it, or keep it.
Variables get / Rows variable	First row (one record: {record.Email}) or All rows (a list). At most 1,000 rows and 256 KB.
Store result in	{result}: the new file plus {result.rows} and {result.columns}.

Example: a regex into column A, a copy of column B into column B. Keep every input column: off. Columns: Regex extract, Source column B, Pattern FT-(\d{6}), Name Code; then Copy column, Source column B. The new file has Code in A and the original B in B.

Example in SQL.

SELECT regexp_extract("Notes", 'FT-(\d{6})', 1) AS Code, "Notes"
FROM sheet
WHERE "Amount" > {minimum}
ORDER BY Code

Update rows

Changes the rows that match and writes the whole file as a new version.

Property	Description
File, Sheet, Header row, Decimals	File tab: the file to change. The new version holds this sheet only.
Rows to change	Rows tab: Conditions (the same builder as Query file) or SQL condition such as "Status" = 'open' AND "Amount" > {min}. No condition: every row.
Set	The changes: a column and a Value ({variables} allowed), a SQL expression ("Price" * 1.1) or a regex replace. A column that does not exist is added. Values typed into a number or date column are converted (12,5 works).
File format, CSV separator	Output tab. Same as input by default.
Save as	ref-session:name (a file of this Session, the default) or ref-storage:name. Use the same saved file as File to change it in place; it is replaced only if no other Session changed it since it was read (else error conflict).
Store result in	{result} with {result.affected}, the rows changed.

Delete rows

Removes the rows that match and writes the file as a new version. A condition is required, and a condition that matches every row is refused (a value from a form that matched more than intended must not empty a file): to empty a file on purpose, use the SQL condition true. {result.affected} is the number of rows removed.

Add rows

Appends rows and writes the file as a new version.

Property	Description
Rows from	A variable holding one record ({form.fields}) or a list of records ({record} from Query file with All rows), or another file whose first row holds the column names.
	Columns are matched by name, in any case; a column the file does not have is added. Values are converted to the column's type.

Changing a saved file safely

Read and save the same saved file: File ref-storage:customers.xlsx, Save as ref-storage:customers.xlsx. The Task remembers the version it read and replaces the file only if it is still that version; if another Session changed it meanwhile, the Task takes the error path with {fileQueryError.code} = conflict and nothing is overwritten. Retry by running the Task again.

SQL

The SQL is the DuckDB dialect. Tables are the names you gave them; quote column names that have spaces or capitals: "Ragione sociale". Useful functions: regexp_extract, regexp_replace, regexp_matches, lower, upper, trim, strftime, date_diff, coalesce, and every aggregate and window function.

Regular expressions use the RE2 syntax: they always run in linear time, so a pattern can never hang a Session, but look-around ((?=…), (?<=…)) and back-references (\1 inside the pattern) are not available. Named groups can be written (?<name>…) or (?P<name>…).

The engine only sees the tables of the Task: it cannot read other files, reach the network or change its own settings.

Limits

Limit	Value
Input file	20 MB
Rows per table	300,000
Time per Task	60 seconds, loading and writing included
Memory per Task	512 MB
New file	15 MB
Rows into variables	1,000 rows, 256 KB
Tasks at once per server	2 (others wait up to 30 seconds)

Errors

Every Task writes {fileQueryError} = { code, message } and takes the error path when it cannot finish.

Code	Meaning
invalid_input	A field is missing or wrong (an empty pattern, two tables with the same name, Delete without a condition).
invalid_file	The file is not a readable Excel or CSV file (an old .xls, a password, a damaged file).
unknown_sheet / unknown_column	The sheet or column does not exist; the message lists the available ones.
invalid_sql	The SQL (or an expression) is not valid, or tried to reach something outside its tables.
too_many_rows / too_large	A limit above was reached.
timeout / memory / busy	The Task took too long, needed too much memory, or the server was busy: filter or split the file, or retry.
storage_quota	The new file would exceed the storage of your Service Plan.
conflict / exists	Saving to ref-storage:: the saved file changed since it was read (or exists and was not read from), or exists and the Task was told to keep it.
unavailable	The query engine cannot start on this server.

Related pages

• File Storage: load and save the files File Query works on.
• Task Configuration: how {variables} work.
• Error Codes: the full error reference.