User Guide : Map Connectors : Source and Target Map Connectors : Excel 2007, Excel 2010, and Excel (xlsx)
 
Share this page             
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 Properties
You can set the following source (S) and target (T) properties.
Property
S/T
Description
HeaderRecordRow
ST
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 records the connector caches before processing them.
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.
Operations
The Excel 2007, Excel 2010, and Excel (xlsx) connectors supports the following operations:
Insert
Update
Delete
Upsert
Data Types
The Excel 2007, Excel 2010, and Excel (xlsx) connectors supports the following data types:
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