In Etlworks the name or index of the Excel worksheet is configured in the Excel format.
Specifying 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, when creating the source-to-destination transformation use the filename|worksheet
notation in the FROM or TO. Example: matrix.xlsx|income
tells the system to read data from the worksheet income
in the file matrix.xlsx
.
You can use the worksheet index instead of the name. Index starts from 1.
Selecting 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 Source Query.
Example: 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. For large worksheets, it could cause the Out of Memory error so we do recommend specifying the worksheet name or index in the FROM.
Selecting data from the specific worksheet in Explorer
Use select fields from [worksheet_name_or_index] optional_where_clause
to view the data in the specific worksheet when browsing data in Explorer.
Example: select * from [josb]
, where jobs is a worksheet name.
Rules
- Always enclose the worksheet name or index in [].
- You can use the worksheet index instead of the name. Index starts from 1.
Comments
0 comments
Please sign in to leave a comment.