Read Excel Sheet

Read Excel Sheet

Type: excel-read-sheet  ·  Package: Excel Activities v1.0.6  ·  Output: DataTable

Reads an entire Excel sheet and returns it as a DataTable, using the first row as column headers. It is the most practical and common way to read Excel data: you get a table ready to loop through, filter, or send to another destination.

How it works

It opens the given sheet and turns its used range into a DataTable: the rectangle from the first to the last cell with content (cells that only have formatting don’t count). If you set range, it reads exactly that range. By default it takes the first row as column names (hasHeader = true), so you then read cells by name: str(row["Customer"]). Accepts session (session mode) or path (fast mode) — see the two modes.

Read-only: in fast mode it does not modify the file.

Required parameters

None — but you must set session or path (or be inside the body of an Excel Open).

Optional parameters

Parameter	Type	Default	Description
session	ZoanExcelSession	—	Excel Open session (instead of path).
path	string	—	Path to the .xlsx (fast mode: opens and closes on its own). The file must exist.
sheet	string | int	—	Sheet name or 1-based index. If omitted, the first sheet.
hasHeader	bool	true	The first row holds the headers. With false, columns are named Col1, Col2… and the first row is just another data row.
range	string	—	Limit to a range, e.g. "A1:D50". If omitted, the sheet’s used range.

Output

Returns a DataTable:

• Columns: with hasHeader = true, the text of each cell in the first row; an empty header is named Col<n> after its position (Col4). With hasHeader = false, Col1, Col2…
• Rows: one per row of the range (excluding the header row), including empty rows in between.
• Values: each cell keeps its type (since version 1.0.6): number (double), date (DateTime), boolean or text; formulas, as their result. A column with only text is a text column; if it holds numbers, dates or booleans it accepts any value. Empty cells are DBNull. When you write the table back with Write Excel Sheet, numbers and dates stay numbers and dates. See how the data arrives.
• Empty sheet: returns a DataTable with no columns and no rows (and logs a warning).

Common errors

Message	Cause and fix
'session' or 'path' is required.	Neither session nor path is set. Set one of them.
File not found.	path does not exist. Validate first with File Exists.
There isn't a worksheet named '…'.	No sheet has that name. List the sheets with Get Excel Sheet Names.
There isn't a worksheet associated with that position.	The index is greater than the number of sheets, or the sheet’s name is just a number (it is taken as an index). See sheets with numeric names.
'…' is not A1 address or named range.	range is not a valid address. Use the form "A1:D50".
A column named '…' already belongs to this DataTable.	Two headers have the same name (case-insensitive). Rename one in the file or read with hasHeader = false.
Extension 'xls' is not supported. Supported extensions are '.xlsx', '.xlsm', '.xltx' and '.xltm'.	The file is .xls or .csv. Convert it to .xlsx; for CSV use Read CSV.

Example

Read Excel Sheet   path = asset("customers.xlsx")   sheet = "Active"   → output: customers
Log   message = "Customers read: " + rowCount(customers)

For Each   items = customers   itemVariable = row
 └─ activities:
      Log   message = str(row["Name"]) + " — " + str(row["TaxId"])

No header row?

If your sheet has no title row, set hasHeader = false: columns are named automatically (Col1, Col2…) and the first row is treated as data.

Empty cells and numbers

In the resulting DataTable, empty cells are DBNull. Always read them with str(...) to avoid errors. For numbers use num(...): it returns the number as is and 0 when the cell is empty. See DataRow.

Related activities

• Read Excel Range — read an exact range.
• Read Excel Cell — read a single cell, with its type.
• Write Excel Sheet — write a DataTable back.