Excel formats

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.

Creating format

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.

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, 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).