User Guide > Map Connectors > Source and Target Map Connectors > Excel 2007, Excel 2010, and Excel (xlsx)
Was this helpful?
Excel 2007, Excel 2010, and Excel (xlsx)
The Excel 2007, Excel 2010, and Excel (xlsx) connectors allows users to retrieve, insert, update, and delete data from excel files that follow the OpenXML format. The Excel (xslx) connector supports the xlsx format of Microsoft Excel.
The Excel 2007, Excel 2010, and Excel (xlsx) are the same connectors with different aliases.
Dependencies on AIX
The Java-based Excel connectors use Apache POI to create and update Excel spreadsheets. Apache POI has an Abstract Window Toolkit (AWT) dependency. It uses the AWT font library for determining cell sizes based on font size and decorations. The AWT font classes use a native library in the JRE called libfontmanager.so. This library is built with a dependency on the open source FreeType library (for more information, see https://www.freetype.org/).
Most AIX installations do not include the FreeType library by default. Hence, it must be installed manually by an administrator before using the Java-based Excel connectors (Excel 2007 and later). Once installed, update the LIBPATH environment variable to include /opt/freeware/lib AND /opt/freeware/lib64.
Connector Parts
Connector parts are the fields you configure to connect with a data source or target.
The settings that are available depend on the connector you select.
For a list of all parts for source connectors, see Specifying Connector, Parts, and Properties.
For a list of all parts for target connectors, see Specifying Connector, Parts, and Properties.
Connector Properties
You can specify the following source (S) and target (T) properties:
Property
S/T
Description
HeaderRecordRow
S/T
(Optional) Row number used to read the column header names. Default is 0. The row number of the first row is 1.
Batch Size
S
(Optional) Number of source records that the connector caches before processing them in a map.
Flush Frequency
T
(Optional) 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
(Optional) 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.
Supported Operations
The following operations are supported:
• Insert
• Update
• Delete
• Upsert
Supported Data Types
The following data types are supported:
• Boolean
• Date
• Datetime
• Decimal
• Double
• Float
• Int
• Long
• Short
• String
• Time
Debugging Tips
The system log contains information about exceptions.
Additional Information
• Query Statement and Query File: Query Statement and Query File source options are not supported.
• Temporary Files: The connectors uses the System temp directory to save temporary files. The temp directory must have read and write permissions.
• Header Record: The connectors cannot read a source file if a column name of the header record contains any of the following special characters:
– &
– ,
– Single quotation mark
– Double quotation mark
• Optimal Performance Settings: Optimal performance settings for using this connector with large data sets are as follows (determined against data sets of 100,000 records):
– Flush Frequency: 10,000
– Target Batch Size: 25,000
– Source Batch Size: 10,000
Last modified date: 02/09/2024