Create Excel from SQL query

Audience: Low-code Engineers

Skill Prerequisites: Actions, Tokens, SQL

Runs a SQL query and writes the result to an Excel file (.xlsx) in a portal folder. Each row of the result becomes a row in the sheet, and each column becomes a column. The file's URL, path, file ID and a download link can be saved as tokens.

The rows go straight from the database into the file, without creating a list first. That makes it the best choice for exporting a lot of data.

note

This action requires the Excel feature package (EXGEN) to be licensed.

Typical Use Cases

• Export a table or a report query to Excel for users to download
• Create a scheduled export in an Automation job and email it
• Export data from another database, using a Database connector or a connection string
• Share a query result in a password-protected workbook

Don't use it to

• Export a list that's already in the context. Use Create Excel from List instead.
• Create a CSV file. Use Create List from SQL and then Create CSV from List.
• Change data. Use Run SQL Query instead. This action doesn't use a transaction.
• Create a workbook with several sheets, formulas or formatting. The action writes one plain sheet.
• Send the file to the browser. This action only creates it. See Delivering the file.

Related Actions

Action Name	Description
Run SQL Query	Runs any SQL and saves the first row in tokens.
Create List from SQL	Runs a query and saves every row as a list.
Create Excel from List	Writes a list to an Excel file.
Create CSV from List	Writes a list to a CSV file or token.
Send Email	Sends the file as an attachment.
Redirect to URL	Sends the user to the file's download link.
Execute Actions	Groups actions, with its own On Error actions, to handle a failed export.

Input Parameter Reference

Parameter	Description	Supports Tokens	Default	Required
Database	A Database connector to run the query against. If it's empty, the application database is used. See Connection.	No	empty	No
Override Connection String	A connection string, or the name of a connection string from web.config. When it's filled in, it's used instead of Database. See Connection.	Yes	empty string	No
Query Timeout	How many seconds the query can run before it times out. Empty or 0 means 600 seconds (10 minutes). Values from 1 to 9 are raised to 10. Negative or non-numeric values make the action fail.	Yes	600	No
SQL Query	The query to run, for example SELECT Name, Email FROM Customers. Tokens are replaced with SQL-safe text, but you add the quotes. See Tokens in the query.	Yes	empty string	Yes
Bind Tokens	Parameters for the query. Each row has a Parameter Name, for example Status, and a Parameter Value, for example [Status]. Use @Status in the query. This is the safest way to pass values.	Yes (values)	empty	No
Add Column Names	Writes the column names of the result as the first row. Turn it off to start the data in row 1.	No	true	No
Folder	The portal folder to save the file in. Select a folder, or in expression mode enter a folder path relative to the portal root, for example Exports/Customers. Missing folders are created.	Yes	none selected	Yes
Pattern	The file name, without the extension, for example Customers-[DateTime:Now|yyyyMMdd]. .xlsx is always added. Characters that aren't allowed in file names are removed. Leave empty for a random name.	Yes	empty string	No
Handle Duplicates	What happens if a file with the same name already exists in the folder. Rename adds a unique suffix to the new file's name. Overwrite replaces the existing file.	No	Rename	No
File Password	A password needed to open the file. Leave empty for no password.	Yes	empty string	No
Worksheet Password	Protects the sheet, so users can see the data but not change it without the password. Leave empty for no protection.	Yes	empty string	No
Disable Force Download	By default, the LinkClick URL makes the browser download the file. Turn this on to let the browser open it instead, if it can. Only affects the Store LinkClick Url token.	No	false	No

Output Parameters Reference

Each output is optional. Enter a token name in the ones you need.

Parameter	Description	Supports Tokens	Default	Required
Store Absolute URL	Receives the file's absolute URL, for example https://example.com/Portals/0/Exports/Customers.xlsx.	No	empty string	No
Store Relative URL	Receives the file's URL relative to the site root, for example /Portals/0/Exports/Customers.xlsx.	No	empty string	No
Store Physical Path	Receives the file's physical path on the server.	No	empty string	No
Store LinkClick Url	Receives a LinkClick URL for the file, for example /LinkClick.aspx?fileticket=....	No	empty string	No
Store File ID	Receives the file ID, for example to attach the file to an email or delete it later.	No	empty string	No

Connection

The action picks the database in this order:

1. Override Connection String, if it's filled in
2. The connection string of the Database connector, if one is selected
3. The application database

Override Connection String can also be the name of a connection string in web.config. A named connection string uses its own provider, for example ODBC. A connection string entered directly, or taken from a connector, is opened as a SQL Server connection.

Tokens in the query

Tokens in SQL Query are replaced before the query runs. Single quotes in token values are doubled, so a value can't end a string early. The action doesn't add quotes, so put text values in quotes yourself:

SELECT * FROM Orders WHERE Status = '[Status]'

Bind Tokens are safer. The values are sent as real SQL parameters, as text, not pasted into the SQL:

SELECT * FROM Orders WHERE Status = @Status

The result in Excel

• The workbook has one sheet, named Sheet1. Only the first result set of the query is written.
• Values keep their database types. Numbers are written as numbers and text as text, so leading zeros in text columns are kept.
• Date and time values are written without a date format, so Excel may show them as numbers. To control how they look, convert them to text in the query, for example FORMAT(OrderDate, 'yyyy-MM-dd') AS OrderDate.
• The headers are the column names of the result. Give calculated columns a name with AS.

Delivering the file

The action saves the file but doesn't send it to the user. To deliver it:

• Redirect the user to the Store LinkClick Url token with Redirect to URL
• Attach it to an email with Send Email, using the file ID or URL in Attach from Token
• Show a link to the file in a message or a page

Errors

If anything fails, for example the connection, the query or saving the file, the action fails. Low-code engineers and administrators see the SQL and the error. Other users see: There was an error with the data. Please contact the administrator or check logs for more info.

The action has no On Error actions. To handle a failure, put it inside Execute Actions and use its On Error actions.

Considerations

• Allowed file extensions. xlsx must be in the site's list of allowed file extensions. Unlike Create Excel from List, this action doesn't add it for you.
• Large results. The rows aren't loaded into a list, but the whole workbook is still built in memory before it's saved. An Excel sheet holds up to 1,048,576 rows, including the header. Filter the query to what users need, and raise Query Timeout for slow queries.
• Don't add the extension to Pattern. Customers.xlsx becomes Customers.xlsx.xlsx.
• Folder access. The file is saved with the folder's permissions. Choose a folder that the users who download the file can read, and keep private data out of public folders.
• Old files. Each run creates a new file unless Handle Duplicates is Overwrite. Clean up old files, for example with Delete File.

Examples

tip

To understand how to use the below examples, please see Running Examples.

1. Export orders with a status filter

This action exports the orders with the status in the [Status] token to Exports/Orders-<date>.xlsx. The status is passed as a bind parameter. The LinkClick URL is saved in the OrdersFileUrl token, so a following Redirect to URL action can start the download.

{
    "Title": "Create Excel from SQL query",
    "ActionType": "ExcelFromSql",
    "Description": "Export orders by status",
    "Parameters": {
        "ConnectionString": "",
        "QueryTimeout": "",
        "SqlQuery": "SELECT OrderNumber AS [Order], CustomerName AS Customer, FORMAT(OrderDate, 'yyyy-MM-dd') AS [Date], Total\nFROM dbo.Orders\nWHERE Status = @Status\nORDER BY OrderDate DESC",
        "BindTokens": [
            {
                "name": "Status",
                "value": "[Status]"
            }
        ],
        "AddColumnNames": true,
        "Folder": {
            "Expression": "Exports",
            "Value": "",
            "IsExpression": true,
            "Parameters": {}
        },
        "Pattern": "Orders-[DateTime:Now|yyyyMMdd]",
        "Duplicates": "Overwrite",
        "StoreLinkClick": "OrdersFileUrl",
        "DisableForceDownload": false
    }
}

2. Nightly export from another database

This action runs in an automation job. It exports every product from the database named WarehouseDb in web.config, with a longer timeout, to a password-protected file in Reports/Warehouse. The file ID is saved in the ProductsFileId token, so a following Send Email action can attach it.

{
    "Title": "Create Excel from SQL query",
    "ActionType": "ExcelFromSql",
    "Description": "Nightly product export",
    "Parameters": {
        "ConnectionString": "WarehouseDb",
        "QueryTimeout": "1800",
        "SqlQuery": "SELECT Sku, Name, Quantity, Price FROM dbo.Products ORDER BY Sku",
        "BindTokens": [],
        "AddColumnNames": true,
        "Folder": {
            "Expression": "Reports/Warehouse",
            "Value": "",
            "IsExpression": true,
            "Parameters": {}
        },
        "Pattern": "Products-[DateTime:Now|yyyy-MM-dd]",
        "Duplicates": "Rename",
        "FilePassword": "[ReportPassword]",
        "WorksheetPassword": "",
        "StoreFileID": "ProductsFileId"
    }
}

Revised 09/26/2026