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.
• 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

• Update existing spreadsheet: 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 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  column1, column2,...columnn.
• 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.