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 any of three 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.
• Uploaded into a workspace, referenced with the workspace: prefix, so the connection reads a file that lives in a workspace's own file tree instead of on a disk path.

This page covers a server path and a repository-shipped workbook, plus the two ways to reach a workspace-uploaded workbook from this connector. If a workbook belongs to a single workspace and does not need to be a shared connection at all, see Build a data model from a workspace file instead - a data model can read it directly, with nothing added here. 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 three locations. All three use the same connector and the same form; only the value in URI differs.

Recommendation. When the workbook belongs to one workspace and does not need to be a shared connection, skip this page and use Build a data model from a workspace file - the model reads the file directly, with no connection to manage here. Use this page's workspace: option only when the workbook should be a shared, named connection other data models (in other workspaces, if you widen Availability) can also bind to. When the workbook is part of the solution but not tied to one workspace, ship it inside the repository instead: the connection then has no absolute path and no per-environment edit. Keep the workbook on the server only when something outside Reportworq owns and refreshes it.

Configure the connection#

1. Add the Excel connector and open its editor.
2. Keep Enable datasource connection selected.
3. Enter a Datasource Name. Report authors reference the connection by this name, for example in the connectionName argument of =RWSQL.
4. In URI, enter where the workbook is: an absolute path on the server, a repository: reference to a workbook inside the repository (see Reference a workbook inside the repository below), or select the Browse… button beside the field to pick a workbook already uploaded into a workspace (see Reference a workbook uploaded into a workspace below).
5. 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.
6. 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.
7. 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:

1. Find the repository folder, the one configured when Reportworq was set up.
2. Under it, open v6/fs/files/. Create the files folder if it does not exist yet.
3. 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.

Reference a workbook uploaded into a workspace#

The workspace: prefix points the connection at a workbook that was uploaded into a workspace's own file tree, rather than placed on the server's disk. It sits alongside repository: - the two prefixes reach different places: repository: reaches the loose-files folder at v6/fs/files, while workspace: reaches a specific workspace's uploaded files. You do not write workspace: references by hand; two actions build them for you.

Browse for the file from the connection editor#

1. In the URI field, select Browse….
2. The picker shows the files in every workspace this connection is currently available to, filtered to Excel and SQLite files - including a workspace you have just added to Availability but not yet saved.
3. Pick the workbook. Reportworq fills URI with the reference, in the form workspace:<workspace name>/<folder path>/<file name> - for example workspace:Finance/Reference Data/Budget.xlsx.
4. Save, then Test connection as usual.

Configure as data source, from the workspace file itself#

If you are starting from the file rather than from Integrations:

1. In the workspace browser, find the workbook, open its ⋮ menu, and choose Configure as data source. This action is available to system administrators only.
2. Reportworq creates a new Excel connection, named after the file, already enabled, with URI pre-filled to the workspace: reference and Availability limited to that file's own workspace - then opens it in the editor for you to review, adjust Advanced Options if needed, and save.

Because the connection is now a normal, named Integrations connection, widening its Availability afterward makes that workbook's data readable from the other workspaces you add - the same as widening Availability on any other datasource connection. Keep that in mind before adding workspaces to a workspace:-backed connection's Availability.

How the workspace segment resolves#

workspace: names its workspace by name - Browse… and Configure as data source both write the name, so the reference reads the way the workspace list shows it. A workspace id (the 16-character hex value) is still accepted and always resolves; it is what Reportworq falls back to writing when the name can't be carried faithfully - see below.

At use time, Reportworq resolves the first segment as follows:

1. If it matches an existing workspace id, that workspace wins.
2. Otherwise, it is matched against workspace names, case-insensitively. Exactly one match resolves to that workspace.
3. Two or more workspaces sharing that name is a configuration error: "More than one workspace is named '<name>' (reference '<reference>'). Rename one of them, or pick the file again with Browse…, which then writes a reference that can't be confused." Rename one of the workspaces, or pick the file again with Browse….
4. No match at all is a configuration error: "The data file reference '<reference>' names a workspace that does not exist ('<name>'). If the workspace was renamed, update the reference to the workspace's current name, or pick the file again with Browse…." Update the reference to the workspace's current name, or pick the file again with Browse….

Reportworq falls back to writing the id instead of the name only when the name can't be written faithfully: it is empty, has leading or trailing spaces, contains /, \, or ;, is not unique among workspace names, or happens to equal another workspace's id. You will not normally see the id form unless one of these applies.

Renaming a workspace breaks a reference written with its old name. The id form does not break on a rename, because it never carried the name in the first place. If a workspace: connection stops resolving after a workspace rename, either update the reference to the new name or switch it to the workspace id - Browse… or Configure as data source, run again, rewrites it for you.

If the old name is later reused by a different workspace, a reference written with that name silently switches to the new workspace's file - with no error. Avoid reusing a renamed workspace's name; if you must, check every workspace: reference that used it. Reportworq logs the resolved workspace for each name-based reference, so an administrator can trace which workspace was actually read.

The reference itself also follows rules similar to repository::

• A workspace file or folder whose name contains /, \, or ;, or that is named . or .., cannot be referenced - rename it.
• A reference to a workspace, or a file, that does not exist, or one that tries to leave the workspace's file tree with .., is rejected as a configuration error naming the reference, rather than resolved.
• A workspace:-backed connection is always read-only, whatever Readonly is set to and even with an explicit Mode= elsewhere in the settings - see Notes and limits.
• The reference names the file's folder path and file name, not an internal id, so it reads the way the workspace browser shows the file. If the workbook is later renamed or moved to a different folder, the reference breaks - exactly as moving a file breaks a plain disk path. Reopen the connection and Browse… again to repoint it.

How this connection is secured#

Only a system administrator can create or change a workspace: connection - Configure as data source and the Integrations editor both require that role. At run time, the only control over who can use it is the connection's Availability: the file's own workspace and folder permissions are not applied to it. Anyone with access to any workspace the connection is available to can read the file's data through the connection, even if Availability does not include the file's own workspace. Configure as data source starts the new connection's Availability limited to the file's own workspace, so widening it afterward is a deliberate choice you make, not something that happens on its own - see the note above on widening Availability.

This is a real difference from a workspace-file data model (Build a data model from a workspace file), which has no separate Availability setting and instead follows the model author's own folder permissions on the file, checked on every use.

Query the workbook#

Once the connection tests, a model author builds views over it on each view's Design tab, starting from a worksheet or writing SQL, exactly as for the SQL connectors; see Build a view with the designer. A workbook declares no relationships between its sheets, so to join sheets on the canvas, add manual relationships to the data model; see Table relationships. 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.

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.
• A workspace: connection is always read-only, whatever Readonly is set to. Reportworq works from a private, server-side copy of the uploaded file, refreshed automatically the next time the connection is used after the file is re-uploaded, so Recalculate writing a value back has nothing to write to - a workspace: connection behaves as if Readonly were forced to true. To change the data, upload a new version of the file into the workspace.
• A workspace:-backed connection is global: once created with Configure as data source, widening its Availability to more workspaces makes that same file's data readable from all of them. If the workbook should stay scoped to one workspace with no separate connection to manage, use Build a data model from a workspace file instead.
• 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 lookups 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.

Related pages#

• 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: and workspace: prefixes.
• Connect CData sources for the other connectors that share the CData property form.
• Build a data model from a workspace file for reading the same kind of workbook straight into a data model, with no connection here at all.
• 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.