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.
Auto-tag worksheets with their name
Enable Read Worksheet Names (off by default) and the connector appends each worksheet's name to the matching file name as |worksheet. The tagged names show up in Explorer and the Mapping editor, which is useful when one connection and one format are reused across many workbooks.
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:
- Destination must be a Server Storage connection. Cloud-storage destinations ignore this flag.
- Format must be Excel XLSX. The legacy XLS format does not support in-place updates.
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.