Connect Excel#

Excel is a built-in connector that reads an Excel workbook as a tabular data source. It appears in the datasource catalog as Excel and is backed by the CData Excel driver that Reportworq ships, so there is nothing to install: the driver is managed code and works on Windows and Linux servers alike. Each worksheet in the workbook becomes a table, and you query it through the same table-or-SQL data-model modes and the same =RWSQL worksheet function as any relational source. Use it for reference data, mapping tables, a staged extract, or demo data that must travel with the repository.

The workbook can live in either of two places, and the choice matters more than any other setting on the form:

On the server, at an absolute path the Reportworq service account can read.
Inside the repository, referenced with the repository: prefix, so a copy of the repository folder carries the workbook along and the connection stays valid wherever the repository is extracted.

This page covers both. It stands on its own for a local or repository-stored workbook; for the driver's cloud storage modes and the long tail of its properties, the authoritative reference is CData's ADO.NET documentation for Excel.

Before you begin#

You are an administrator, working in Settings > Integrations > DATASOURCES. See Connect a data source for the general add-and-test flow.
The workbook is in the Office Open XML format that Excel 2007 and later save (.xlsx). CData documents that format as the supported one.
The workbook is on the Reportworq server, or inside its repository as described below. The path resolves on the server, never on your workstation.
Each worksheet you intend to query is laid out as a table: a header row of column names in the first row, one record per row beneath it. The driver reads the first row as column names by default.
The service account that runs Reportworq can read the file. See Notes and limits for why it may also need to write to it.

Choose where the workbook lives#

The table below compares the two locations. Both use the same connector and the same form; only the value in URI differs.

	On the server	Inside the repository
URI value	An absolute path, for example `C:\Reportworq\data\Budget.xlsx` or `/var/lib/reportworq/data/Budget.xlsx`	`repository:Budget.xlsx`, or `repository:data/Budget.xlsx` for a subfolder
Where the file is	Anywhere on the server's file system, or a share the service account can reach	`<repository>/v6/fs/files/`, a folder inside the repository configured at setup
Backup and restore	The workbook is backed up separately from the repository	A file-level copy of the repository folder includes the workbook. The built-in Backup feature does not: it zips the content store only, so add `v6/fs/files/` to your own backup routine
Moving to another environment	Edit the path if the folder layout differs	Nothing to edit: the same connection settings work wherever the repository is extracted
Best for	A workbook another process refreshes, such as an export dropped into a folder on a schedule	A workbook that is part of the solution: reference data, mappings, demo content

Recommendation. When the workbook is part of the solution, ship it inside the repository. The connection then has no absolute path and no per-environment edit, and the workbook cannot drift away from the content that depends on it. Keep the workbook on the server only when something outside Reportworq owns and refreshes it.

Configure the connection#

Add the Excel connector and open its editor.
Keep Enable datasource connection selected.
Enter a Datasource Name. Report authors reference the connection by this name, for example in the connectionName argument of =RWSQL.
In URI, enter where the workbook is: an absolute path on the server, or a repository: reference to a workbook inside the repository. See Reference a workbook inside the repository below.
Leave Connection Type at Local. Local covers both a file on the server and a file inside the repository, and it needs no credentials, so the Auth Scheme field it reveals stays at None. The other connection types (Amazon S3, Azure Blob Storage, SharePoint, OneDrive, SFTP, and the rest) read the workbook from that store and need its credentials; they are documented in CData's connection guide.
Leave Show Advanced Options cleared unless you need one of the properties in Adjust how the workbook is read. Selecting it swaps the short form for the driver's full property sections.
Save, then select Test connection. The test opens the workbook with the driver's own connection test and returns "Success." when the file exists and parses as a valid workbook. A missing file or a file that is not a valid workbook fails the test; a wrong path is the most common cause. With the default Test Connection Behavior (LIST_OR_READ_FILES), a URI that points at an existing folder also passes, even when the folder holds no workbook; set Test Connection Behavior to READ_FILE under Show Advanced Options to make the test insist on the file itself. The Excel form has no Validation Query row, because the driver tests the file directly rather than running a query.

A property left at its default is not written into the connection string, so the saved connection carries only the values you changed.

Reference a workbook inside the repository#

The repository: prefix makes the workbook part of the repository. When you test or query the connection, Reportworq resolves the prefix to the repository's files folder, so repository:Budget.xlsx reads <repository>/v6/fs/files/Budget.xlsx. The stored setting keeps the prefix; only the connection string that is built at run time carries the absolute path. That is what keeps the connection portable.

Put the workbook in the files folder#

There is no upload screen for this folder. An administrator places the workbook on the server:

Find the repository folder, the one configured when Reportworq was set up.
Under it, open v6/fs/files/. Create the files folder if it does not exist yet.
Copy the workbook into that folder, or into a subfolder of it. The folder holds loose files that belong to the repository rather than to the content store: a spreadsheet or a SQLite database that a data connection reads from. Because it is a real folder inside the repository, a plain copy of the repository carries it.

Write the reference#

Enter the workbook's path relative to the files folder, after the prefix:

repository:Budget.xlsx

repository:data/Budget.xlsx

The rules the reference follows:

The path is relative to v6/fs/files/ and may include subfolders. Either slash works; a backslash is normalized to a forward slash.
The reference must stay inside the files folder. A path that tries to leave it, with .. or a path rooted for the server's operating system (/etc/passwd on Linux, C:\... on Windows), is rejected as a configuration error when you test or query the connection rather than resolved. The error reads "A 'repository:' path must stay inside the repository files folder."
On a Linux server, match the file name's case exactly. The file system there is case-sensitive.
The prefix is not case-sensitive. Repository:Budget.xlsx resolves the same way.

What happens on backup, restore, and copy#

A copy of the whole repository folder carries the workbook: a file-level copy, a VM or disk snapshot, or your own file backup that includes <repository>/v6/fs/files/. Extracting such a copy into another environment (a test instance, a second server, a container) brings the workbook with it, and the Excel connection works there unchanged.
The built-in Backup feature (the Backup action and Enable Daily Backup on the Web Server tab of Server configuration) covers the content store only, <repository>/v6/data. Its zip does not include the files folder, so restoring one of its backups does not restore the workbook. Include v6/fs/files/ in your own backup routine, and copy it alongside a restored backup.
To update the data, replace the file in the files folder. Reportworq keeps the driver connection pooled for a short time after the last query so that repeated refreshes do not re-parse a large workbook, so a replaced file is picked up by the next query once that pool idles out, within about a minute.

Query the workbook#

Once the connection tests, a model author builds views over it on the model's Connection tab, choosing Table or View or SQL Query, exactly as for the SQL connectors; see Query the source in a data model. What the driver exposes:

Each worksheet is a table named after the sheet: a sheet called Data is the table Data. There is no schema, so a table pick stands alone, and there is no $ suffix on the name as there is with the Windows ACE OLE DB provider.
Named ranges are tables too. A workbook-level defined name such as SALES can be queried as SELECT * FROM SALES.
A cell range can be addressed in the table name for data that is not top-left aligned: SELECT * FROM Data#A5:F* reads from A5 through column F to the last row.
Column names come from the first row. With Header at its default of true, SELECT Region, Sales FROM Data uses the header cells. Set Header to false and the columns are A, B, C, and so on.

In SQL Query mode, write ordinary SQL against those tables:

SELECT Region, Sales FROM Data WHERE Sales >= 300

From a worksheet, read the workbook with =RWSQL, which re-runs the query on every job run; the middle argument is the Datasource Name you entered:

=RWSQL(B5, "Budget", "SELECT Region FROM Data WHERE Sales >= 300")

Reportworq caps row-limited previews with SELECT TOP n for this connector on its own, so there is no Limit Query Type field on the Excel form.

Adjust how the workbook is read#

Select Show Advanced Options to reach the driver's property sections. The form's field labels are the driver's own property names, spaced for readability, so the same names appear in CData's documentation. The properties below are the ones that most often matter for a local or repository-stored workbook; each links to its CData reference page.

Section	Field	Default	What it does
Connection	Header	`true`	Whether the first row supplies the column names. `false` gives generic names (`A`, `B`, `C`). A per-sheet override is allowed: `True,Sheet1=False`.
Connection	Define Tables	empty	Models fixed cell ranges as named tables, for example `Budget=Data!A1:N25,Targets=Data!P1:R40`. Needs URI to point at a single workbook.
Connection	Orientation	`Vertical`	`Horizontal` reads a sheet whose records run across columns, with the names down the first column.
Connection	Test Connection Behavior	`LIST_OR_READ_FILES`	What Test connection checks. `READ_FILE` insists that URI is an existing, valid workbook file.
Data	Recalculate	`true`	Whether the driver's formula engine recalculates formula cells. See the formulas note below.
Data	Has Cross Sheet References	`false`	Set to `true` when formulas reference other sheets and you need their current values; the driver then loads every sheet.
Data	Ignore Calc Error	`false`	A cell with a calculation error (`#NAME?`, `#DIV/0!`) is returned as null; `true` returns the error text instead.
Data	Null Values	empty	A comma-separated list of cell values to read as null, for example `N/A,NaN`.
Data	Type Detection Scheme	`ColumnFormat`	How column types are inferred: from the cell format (default), by scanning rows (`RowScan`), or none (every column as text).
Data	Show Empty Rows	`false`	Empty rows are dropped from results unless this is `true`.
Miscellaneous	Row Scan Depth	`15`	How many rows are scanned to discover columns and, under `RowScan`, their types. Raise it when the first rows have blanks.
Miscellaneous	Readonly	`false`	`true` allows only `SELECT`, so a write-back function such as `RWSQLUPDATE` cannot change the workbook. Recommended for a reference workbook.

The full list, including the Schema, Caching, and Logging sections, is in the connection-string properties reference of the CData documentation.

Notes and limits#

The workbook path resolves on the Reportworq server. A path that works on your workstation fails on the server unless the file is there too, under the service account's read permission.
The repository: prefix reaches only the repository's files folder. Anything outside it must be given as an absolute server path.
Formulas. The driver has its own formula engine and, with Recalculate at its default, recalculates formula cells when it reads them. CData documents that a recalculated value is written back into the workbook file, so the service account may need write access to the file. If the workbook must stay untouched, set Recalculate to false and Readonly to true; the driver then returns the values Excel last saved. A formula that references another sheet needs Has Cross Sheet References set to true to reflect that sheet's current values.
Column types are inferred from the cell format by default. A column whose first rows are blank or mixed can be typed unexpectedly; switch Type Detection Scheme to RowScan and raise Row Scan Depth, or to None to read every column as text.
Use the workbook as a data file, not as a live document. Save it from Excel, then place it on the server or in the files folder. The driver reads whatever the file holds at query time.
Row-capped previews and field imports use SELECT TOP n; the connector emits it itself, and there is no dialect setting to change.
A file-backed source is re-read on each query. Reportworq pools the driver connection for about a minute after the last query so that a job with many refreshes against a large workbook does not re-parse it each time.

Connect SQL Server, OLE DB, and ODBC for the Windows-only alternative of reading a workbook through the Microsoft ACE OLE DB provider, and for the shared query modes.
Connect SQLite for a file-backed SQLite database, which supports the same repository: prefix.
Connect CData sources for the other connectors that share the CData property form.
Connector catalog for every connector Reportworq ships.
Relational database functions for =RWSQL and its write-back siblings, which can read an Excel connection like any other.

Going deeper. To curate columns into governed business fields, set filters, and publish a view for report authors, see Data models.

Feedback on this page

Comments, questions, requests, or something missing or unclear? Email us - the page you are on is filled in for you.

Email feedback on this page

Or write to support@reportworq.com directly.