Excel Formats

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.

Create Format

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 Excel XLSX.

Available parameters:

• 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.
• 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 group is also set, the connector will end grouping at this 1-based row number.