The suffixes listed are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for the Microsoft Excel reader is XLSX.
These directives are exposed in Workbench as format settings through the Reader Parameters.
DATASET
Required/Optional: Required
This is the file name of the Microsoft Excel workbook.
Example:
XLSX_DATASET C:/data/citySource.xls
Workbench Parameter: Source Microsoft Excel File(s)
DEF
Required/Optional: Required
Each dataset must be defined before it can be read. The definition contains the worksheet/named range name, followed by the names and types of the attributes.
The schema is determined either through choices in the workspace, or by scanning x features of the feature type, where x is defined by scan_max_features. The schema is only useful for defining the schema which may be useful for writers, but it is ignored on read. Therefore, features beyond the scan_max_features might return values THAT do not comply with the schema. (If this is an issue, you can re-add a reader with a larger value for scan_max_features or manually edit the attribute types in the workspace).
The syntax of an XLSX DEF line is:
XLSX_DEF <baseName> \
<xlsx_start_row> \
<xlsx_end_row> \
[<attrName> <attrType>]+
IDs
Required/Optional: Optional
This optional specification is used to limit the available and defined database tables that will be read. If no IDs are specified, then all worksheets and named ranges are read.
XLSX_IDs <featureType1> \ <featureType2> … \ <featureTypeN>
The feature types must match those used in DEF lines.
Note that named ranges will be prepended by the worksheet name they exist on and a forward slash. For example, <sheet1>/<myNamedRange>.
The example below selects only the HISTORY worksheet for input during a translation:
XLSX_IDs HISTORY
Workbench Parameter: Feature Types to Read
OUTPUT_FORMULAS
Required/Optional: Optional
This directive controls whether the reader will attempt to read formulas from the sheet. It is also used to determine the schema.
If OUTPUT_FORMULAS is set to yes, and a formula is found, it will be added to the feature as <attrName>.formula.
If OUTPUT_FORMULAS is set to no, formulas will not be read.
Default: NO
Values: <yes | no>
OUTPUT_LINKS
Required/Optional: Optional
This directive controls whether the reader will attempt to read hyperlinks from the sheet. It is also used to determine the schema.
If OUTPUT_LINKS is set to yes, and a hyperlinks is found, it will be added to the feature as <attrName>.hyperlink.
If OUTPUT_LINKS is set to no, hyperlinks will not be read.
Default: NO
Values: <yes | no>
OUTPUT_COMMENTS
Required/Optional: Optional
This directive controls whether the reader will attempt to read comments from the sheet. It is also used to determine the schema.
If OUTPUT_COMMENTS is set to yes, and a comment is found, it will be added to the feature as <attrName>.comment.
If OUTPUT_COMMENTS is set to no, then comments will not be read.
Default: NO
Values: <yes | no>
Note: This option currently works for .xls files only.
Required/Optional
Optional
START_ROW
Required/Optional: Optional
This directive can be used to specify a start row to begin reading from or to read the schema from. Use this to skip rows in a worksheet, such as headers. This directive can be overruled at the feature type level. The row indices start at 1.
Values: <number>
END_ROW
Required/Optional: Optional
This directive can be used to specify the last row to read from or to read the schema from. Use it to cut off the features and prevent it from reading foot notes or additional worksheets that may be on the sheet. This directive can be overruled at the feature type level. The row indices start at 1.
Values: <number>
START_COL
Required/Optional: Optional
This directive can be used to specify a start column to begin reading from or to read the schema from. Use this to skip attributes in a worksheet. The column indices start at 1.
Values: <number>
END_COL
Required/Optional: Optional
This directive can be used to specify an end column for the read or read schema calls. The column indices start at 1.
Values: <number>
_SCHEMA
Required/Optional: Optional
This field is used by the GUI to force the reader to a schema. It specifies the feature types, their attributes and attribute types, and the range to read.
SCAN_MAX_FEATURES
Required/Optional: Optional
This field can be used to indicate the maximum number of rows to scan to obtain attribute types in the resultant source feature type. This does not affect the preview in the reader parameters which always scans a fixed amount of rows.
If set to a value of 0, the entire file will be read to determine the schema.
Values: <number of lines>
Default: 10000
Workbench Parameter: Maximum Lines to Scan (0 for no limit)
RETRIEVE_ALL_SCHEMAS
This parameter is applicable only when generating a mapping file, generating a workspace, or when retrieving schemas in an FME Objects application.
When set to yes, schemas for all of the tables and feature classes in the database are returned.
When set to no (or missing), and if RETRIEVE_ALL_TABLE_NAMES is similarly set to no (or missing), only schemas requested by the IDs parameter are returned.
Required/Optional
Optional
Values
YES | NO (default)
NO: The reader will return the schemas for the feature types specified in the IDs. If no features are specified in IDs, then:
- for Enterprise Geodatabases, FME will not return any schema features;
- for Personal and File Geodatabases, FME returns the schema features for all the tables.
If this value is not specified, then it is assumed to be No.
YES: Indicates to the reader to return all the schemas of the tables in the database.
Mapping File Syntax
Not applicable.
FME Objects applications would include RETRIEVE_ALL_SCHEMAS followed by “YES” in the parameters array passed to IFMEUniversalReader::open().
Workbench Parameter
Not applicable
RETRIEVE_ALL_TABLE_NAMES
This parameter is only applicable when generating a mapping file, generating a workspace or when retrieving schemas in an FME Objects application.
When set to yes, and if RETRIEVE_ALL_SCHEMAS is set to no (or missing), names for all of the tables and feature classes in the database are returned. When set to no (or missing), and if RETRIEVE_ALL_SCHEMAS is similarly set to no (or missing), the schemas requested by the IDs directive are returned.
Note: If RETRIEVE_ALL_SCHEMAS is also set to Yes, then RETRIEVE_ALL_SCHEMAS takes precedence.
Required/Optional
Optional
Values
YES | NO (default)
Mapping File Syntax
Not applicable.
FME Objects applications would include RETRIEVE_ALL_TABLE_NAMES followed by “YES” in the parameters array passed to IFMEUniversalReader::open().
Workbench Parameter
Not applicable (used when you browse a Table List)
READ_BLANK_AS
This specification is an advanced parameter, and instructs FME on how to read back blank cells.
If set to "Missing", blank cells will be read back as missing attributes on the output feature. If set to "Null", blank cells will be read back as null attributes on the output feature.
Required/Optional
Required
Values
Missing (default) | Null
EXPOSED_ATTRS
This directive allows the selection of format attributes to be explicitly added to the reader feature type.
This is similar to exposing format attributes on a reader feature type once it has been generated; however, it is even more powerful because it enables schema-driven applications other than Workbench to access and leverage these attributes as if they were explicitly on the schema as user attributes.
The result of picking a list of attributes is a comma-separated list of attribute names and types that will be added to the schema features. Currently all reader feature types will receive the same set of additional schema attributes for a given instance of the reader.
Required/Optional
Optional
Mapping File Syntax
Not applicable.
While it is possible for FME Objects applications to invoke this directive, the required format is not documented.
This directive is intended for use in our GUI applications (for example, Workbench) only.
Workbench Parameter
Additional Attributes to Expose