When to use this Format
Etlworks can read and write Microsoft Excel documents in the XLS and XLSX Formats. Both Formats share the same properties.
Use Excel Formats when configuring a source-to-destination transformation that reads or writes Excel files.
To create a new EDI Format, go to
Connections, select the
Formats tab, click
Add Format, type in
excel in the
Search field and select either
Excel XLS or
Worksheet Name or 1-based index: the name of the Excel worksheet or 1-based index of the worksheet. Read how to read from and write into different Excel worksheets using the same Format.
Read Worksheet Names: if this option is enabled, the connector will read worksheet names and add them as |worksheet to the file name.
- This works in Explorer:
- And in the Mapping
: If this option is enabled and the spreadsheet already exists, the connector will update it instead of creating a new one. This option works only when the destination connection is server storage and is ignored if it is any other type of file or cloud storage. This option is not available for Excel XLS format. Read more.
No row with column names: if this option is enabled, the connector assumes that no row in Excel has column names. The columns are created as
Start row: 1-based number of row, which contains the header. If not set, Etlworks will use the first row in the worksheet for the header.
Start column: 1-based start column from which the connector starts writing the data into the worksheet.
End row: 1-based number of row after which Etlworks should stop reading the worksheet.
Column names compatible with SQL: convert column names to SQL compatible column names by removing all characters except alphanumeric and spaces.
Template: comma delimited list of columns in the file. If this field is not empty, the Excel connector will populate column names from the template. Example:
first name,lastname, dob.
Treat 'null' as null: if this option is enabled, Etlworks will parse string values equal to 'null' as actual nulls (no value).
All fields are strings: If this option is enabled (it is disabled by default) the system will create all fields with a string data type.
Date and Time Format: a Format for timestamps (date+time).
Date Format: a Format for date (date only, no time).
Time Format: a Format for time (time only, no date).
First row in the group (XLSX only): if this parameter is set, the connector will group all rows, starting from this 1-based row number onwards. Read more about outlining (grouping) data in a worksheet.
Last row in the group (XLSX only): if this parameter is set, and the
First row in the groupis also set, the connector will end grouping at this 1-based row number.