When to use this format
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 Formats tab, click Add Format button, type in excel in the search field and select either Excel XLS or Excel XLSX.
- 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, Integrator will use the first row in the worksheet for the header.
- End row - 1-based number of row after which 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, 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.