User Guide > Map Connectors > Source and Target Map Connectors > Excel 365
Was this helpful?
Excel 365
The Excel 365 connector can read/write Excel 2007+ spreadsheets, but provides (as much as possible) the same behavior as the older native connectors, while remaining mostly compatible with the existing 2007+ connector. On the target side, the map can only write to one sheet, with one record type, and the record operation must be Insert.
For information about using Excel 365 on AIX, see Dependencies on AIX.
Connector Properties
You can set the following source (S) and target (T) properties.
Property
S/T
Description
HeaderRecordRow
ST
Number that indicates a header row:
• 0 indicates no header row
• Positive value indicates the header row (example, 37 is a valid value).
When reading, it indicates the row that contains the field names. When writing, the header will be written at this row number, assuming this row number is actually in the range of records being written. Therefore, if the sheet is cleared or does not exist, the header is set to 50, and there are only 40 records written, the header will never be written.
Similarly, if appending, the header record is set to 50, and the sheet already contains 100 records, then the header will never be written.
Flush Frequency
T
The number of records buffered in memory before being written to the Excel file. Default is 0 (all records are written at once). If you are inserting many records, then changing the default to a higher value may improve performance.
Batch Response
T
The file to which CRUD operation results are written. The format is one entry per record, indicating success or failure. If operation fails, information about cause of failure is returned.
Emulated Output Mode
T
Allows to use this connector as a single mode connector. Select any of the following options:
• None
• Replace
• Append
• Delete and Append
If you select None, then the default output mode is Insert.
Supports single record types and cannot connect to multiple sheets at the same time.
Note:  You can set this option instead of adding table operation actions.
This property is not available when Compatibility mode is On.
StyleSampleSize
S/T
Reads until the specified row number in the sheet for the purpose of determining a field length and data type. Also, AutoStyling must be set to True.
0 indicates to read all rows in the sheet.
Default is 5000.
AutomaticStyling
S/T
If set to True, attempts to determine both data type and the appropriate column width of each column based on reading until the row number specified in the StyleSampleSize property.
Default is False.
Note:  To view and write the data in the required format to the target, set the AutomaticStyling property to TRUE.
Operations
The Excel 365 connector supports the following operation:
• Insert
Data Types
The Excel 365 connector supports the following data types:
• Boolean
• Date
• DateTime
• Number
• String
• Time
Debugging Tips
The system log contains information about exceptions.
Additional Information
If you try to access the same workbook from different connector instances for writing, it will not work. You can read from multiple sheets in the same workbook. A worksheet in Microsoft Excel can contain maximum number of 1,048,576 rows and 16,384 columns. However, if you insert more than the maximum range, then the following error message is displayed:
Sending updates to DMS: Unknown exception 'java.lang.IllegalArgumentException' Invalid row number (1048576) outside allowable range (0..1048575)
Note:  The target field size must be set to 255 or lower, then the map runs successfully and the complete data is written. The maximum column width for an individual cell is 255 characters

After connecting to the Excel 365 connector, if your map’s schema does not match with your data schema, then the schema mismatch error is displayed. However, if you set the Schema Mismatch property to Error, an message is displayed to change your schema mismatch property to Use Connection, Match By Name, Connect and then click Refresh to get the new schema.
Last modified date: 08/02/2023