Excel Formats

Overview

Etlworks reads and writes Microsoft Excel files in both modern (XLSX) and legacy (XLS) formats. The two share the same configuration surface; XLSX has a few extra capabilities — in-place updates and row grouping — that XLS cannot support because the older binary format does not represent them.

Use an Excel Format on a source-to-destination transformation whenever the source or the destination is an Excel file.

Create an Excel format

Go to Connections, switch to the Formats tab, click Add Format, type excel in the search box, and pick Excel XLSX (recommended) or Excel XLS.

Selecting worksheets

Every Excel workbook has one or more worksheets. By default, the connector reads or writes the first worksheet. The options below cover everything else.

By name or 1-based index

Set the Worksheet Name or 1-based index field on the format. income targets the worksheet named "income"; 2 targets the second worksheet. If a worksheet happens to be literally named "1", that name wins over the index.

Per-transformation override (filename|worksheet)

If you want one format to handle many worksheets across many files, reference the worksheet directly in the FROM or TO field of the transformation using the filename|worksheet notation:

• matrix.xlsx|income — read the income worksheet from matrix.xlsx.
• matrix.xlsx|2 — read the second worksheet (1-based index).

All worksheets at once

To read every worksheet in a workbook in one pass, use a wildcard or a comma-separated list:

• * — every worksheet in the workbook.
• Pattern — Q* reads all worksheets whose name starts with "Q".
• Comma-separated list — Sheet1,Sheet3 reads only those two, in that order.

Each matched worksheet becomes a nested array column on the resulting dataset. Pair this with nested mapping to control what happens per worksheet, or use a file-to-staging flow (see Excel as a staging source below) which expands worksheets into staging tables automatically.

New in 9.5.x: reading every worksheet in a single pass.

Discover worksheets by name

Enable Read Worksheet Names (off by default) and the connector discovers each workbook's worksheets as selectable objects identified as filename|worksheet (for example, report.xlsx|Orders). The worksheet objects show up in Explorer and the Mapping editor, which is useful when one connection and one format are reused across many workbooks.

Since 9.9.12, Explorer groups the discovered worksheets under their workbook:

• The workbook appears as a file node with its size and date, using the Excel format icon.
• Each worksheet appears as a child of the workbook, with a table icon and the sheet name; expanding a worksheet shows its fields.
• Selecting a worksheet preserves the full filename|worksheet path for field lookup, View Data, and export.
• Physical file actions — rename, edit, delete, viewing raw data — belong to the workbook node. A worksheet is not a separate stored file.

Notes:

• With Read Worksheet Names disabled, ordinary file behavior remains: the workbook is listed as a plain file and the format's worksheet selection applies.
• Worksheet discovery reads each workbook, so folders with large remote workbooks incur an extra metadata read.
• Ordinary filename wildcards match the physical file name; a pattern containing |worksheet selects a sheet explicitly.
• When Explorer browses through an Integration Agent, the Agent runtime must also include the 9.9.12 update — an application upgrade alone does not add worksheet discovery to an older Agent.

Source query (SQL-style)

You can also target a specific worksheet with a source query. Enclose the worksheet name or index in square brackets:

select * from [income]
select * from [2] where region = 'EMEA'

Note: using a source query disables streaming, so the entire worksheet is loaded into memory. For large worksheets, prefer the filename|worksheet notation in FROM or TO.

Headers and row range

Where is the header row?

By default, the first row of the worksheet is treated as column headers and data starts on the second row.

For worksheets where the header is further down — banner rows, merged title cells, multi-row preamble — set Start row to the 1-based row number of the header. The connector reads from there onwards. Optionally set End row to stop at a specific row.

No header at all

Enable No row with column names when the worksheet has no header row at all. The connector auto-generates column names — column1, column2,..., columnN.

Writing into a specific column

Set Start column to the 1-based column index where writes should begin. Useful when you want to reserve leading columns for other content.

Data types

Auto-detection

By default, the connector detects cell types from the underlying Excel format codes: dates and date-times become DATE / TIMESTAMP, numbers become NUMERIC or INTEGER, booleans become BOOLEAN, and text cells become VARCHAR.

Force everything to text

Enable All fields are strings to skip auto-detection and read every cell as VARCHAR. Useful when the downstream system expects strings or when number-vs-text detection is unreliable for your data.

Date and time formats

Configure the cell-style formats used when writing:

• Date and Time Format — for date-time cells.
• Date Format — for date-only cells.
• Time Format — for time-only cells.

Treat 'null' as null

When this option is enabled, cells containing the literal string null are read as actual null values rather than the four-character string "null".

Column names

SQL-compatible names

Enable Column names compatible with SQL to strip characters that are not valid in SQL identifiers (anything other than alphanumerics and underscores). Useful when the destination is a database and your header row contains spaces, dashes, or other special characters.

Template (explicit column order)

Set Template to a comma-separated list of column names. The connector uses those names instead of reading the header row, and they define the order of fields in the resulting dataset. Example: first_name,last_name,dob.

Writing Excel files

By default, the connector creates a new workbook on every run. Two XLSX-only options change that.

Update an existing spreadsheet

Enable Update existing spreadsheet to update an existing XLSX file in place. The connector opens the workbook, finds the target worksheet (creating it if it does not exist), clears the data range, and writes the new rows.

Requirements:

• Format must be Excel XLSX. The legacy XLS format does not support in-place updates.
• Up to 9.x, the destination must be a Server Storage connection — cloud-storage destinations ignore this flag. Starting with 10.0.0, remote storage is supported too (see below).

Update an existing spreadsheet on remote storage (10.0.0+)

Starting with 10.0.0, Update existing spreadsheet also works when the destination is remote storage: FTP and FTPS, SFTP, SMB Share, WebDAV, Amazon S3, Azure Storage, Google Cloud Storage, Google Drive, SharePoint and OneDrive, Dropbox, and Box. Other worksheets, formulas, styles, and workbook content are preserved through a normal read/write cycle of the whole workbook.

What to know before enabling it on remote storage:

• The whole workbook is downloaded, loaded into memory, and written back, even when only one worksheet changes. Streaming the source records does not make updating an existing workbook a bounded-memory operation — plan for the workbook size, not the changed-rows size.
• The connection needs permissions to read and replace the file. A new workbook is created only when the file is unambiguously missing; permission errors, download failures, and corrupt workbooks fail the flow instead of replacing the file with an empty workbook. Disable the update option when you are explicitly creating a new file on a transport that cannot distinguish a missing file reliably.
• The replacement is written only after row processing completes, but there is no cross-flow locking and no atomic remote edit — on transports without atomic object writes, an upload failure can leave a partial replacement. Avoid concurrent writers and keep a backup or object versioning on the storage side.
• Native Google Sheets, HTTP APIs, email, message queues, Git, and output-only streams are not supported for remote updates, and ZIP/GZIP-wrapped workbooks are rejected. Encrypted or unreadable workbooks fail before anything is replaced.
• Flow inspection reports the full-download/full-upload cost of a remote update as a minor performance finding — it is the trade-off required to preserve the other worksheets.

This option also lets you build a multi-worksheet workbook by running several transformations that share the same file name and target different worksheets. Use the filename.xlsx|worksheet notation in the TO field of each transformation; the others are left untouched.

Group rows (XLSX only)

Set First row in the group and (optionally) Last row in the group to wrap a row range in an Excel outline group. The resulting worksheet has a collapsible "+" / "−" control alongside the grouped rows — see Microsoft's Outline (group) data in a worksheet guide for the underlying Excel feature.

Excel as a staging source

New in 9.5.x. File-to-staging flows now accept Excel workbooks directly as inputs. When the source is an Excel file, the connector expands it: each worksheet becomes its own staging table, named after the worksheet, with no extra configuration. You do not need to set the worksheet name on the format, run separate transformations per worksheet, or write any pre-processing.

Combine this with the all-worksheets-at-once selector when you want the same behavior in a regular ETL transformation rather than a file-to-staging flow.

Related connectors

The Excel Format treats an Excel file as a flat source or destination. Etlworks also offers:

• Excel as Database — query an Excel workbook with SQL as if it were a database. Best for read-heavy workflows where multiple worksheets relate to one another.
• Excel Online Connector — read and write spreadsheets hosted in Microsoft 365 rather than as files on storage.