In Etlworks, the name or index of the Excel worksheet is configured in the Excel Format. It is possible to reuse the same format to read/write from/to different worksheets.
Specify worksheet name or index in the FROM or TO
If you want to use the same Connection and Format to read from or write into different Excel worksheets, use the
filename|worksheet notation in the
TO when creating the source-to-destination transformation.
matrix.xlsx|income tells the system to read data from the worksheet
income in the file
You can use the worksheet index instead of the name. Index starts from 1.
Configure Excel format to automatically add worksheet name to the file name
When creating an Excel format, enable
Read Worksheet Names. If this option is enabled (it is disabled by default), the connector will read worksheet names and add them as
|worksheetto the file name.
This works in Explorer:
And in the Mapping
Select data from the specific worksheet using Source query
The alternative to using the worksheet name or index in the
FROM is to select data from the specific worksheet using the
select * from [income], where
income is a worksheet name. Always enclose the worksheet name or index in
While it does look intuitive to use the SQL, it automatically disables the data streaming, so all the data in the worksheet will be stored in memory. Large worksheets could cause the Out of Memory error, so we recommend specifying the worksheet name or index in the
Select data from the specific worksheet in the Etlworks Explorer
select fields from [worksheet_name_or_index] optional_where_clause to view the data in the specific worksheet when browsing data in the Etlworks Explorer.
select * from [josb], where
jobs is a worksheet name.
The rules are:
- Always enclose the worksheet name or index in
- You can use the worksheet index instead of the name. Index starts from 1.