When to use this Format
The Etlworks Integrator 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
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 Integrator will use the first row in the worksheet for the header.
End row: 1-based number of row after which Etlworks Integrator 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, Integrator will populate column names from the template. Example:
first name,lastname, dob.
Treat 'null' as null: if this option is enabled, Etlworks Integrator 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.
Please sign in to leave a comment.