Microsoft Excel (XLSXR) Reader Parameters

Different feature types are read by default, depending on whether you make changes to the Reader Parameters (accessible from the Add Reader > Parameters button):

By default, all sheets and named ranges are selected. You can use the Select button to filter the display.

  • When you open the Reader Parameters, by default, all of the sheets in the workbook will be selected, and all named ranges will be deselected. This is so that all cell data is read into FME only once.
  • If you add a reader without opening the dialog, you will have to select which feature types to add.

Dynamic Schema

If you set Workflow Options to Dynamic Schema, then all sheets and all named ranges will be read into FME.

This option breaks the dependence on the source and destination schema. One merged feature type will be connected to one writer feature type that is configured for dynamic operation. The schema is not replicated on the workspace; therefore, if the source data changes, you will not have to update the workspace – FME will do this automatically.

This will result in duplicated data (since any information in a named range is also on the sheet that contains it), but it means that no feature types will be hidden by default.

Sheets to Read

This area shows all of the sheets and named ranges in the Excel file. A named range is of the form sheetName/NamedRangeName.

Note: Tables are treated the same as named ranges, and will appear with the same form.

Example

In this example, the selected area is a named range: the name of the named range is date_hatched, and it is on the sheet eggs.

The column Field Names Row allows you to select which row contains the header names, and the column Cell Range allows you to restrict the information to read. If a cell in the Field Names Row is blank (if, for example, it is a merged cell), it will automatically be given the merged cell value.

The Select menu allows you to choose which sheets/named ranges to read. By default, FME will select All.

If the column Field Names Row value is set to zero (0), then the column names will be set to the Excel column letters A, B, C, etc. This can be useful if there are no field names, or if they are not consistent.

Preview

This area shows a preview of the data (up to 100 rows) of the selected feature type (either feature or named range). As shown in the example here, if you select a header row from the Sheets to Read area, that row will be shown in boldface text.

Attributes

This area displays the attributes that were created for the feature type (schema scanned).

  • Name: The attribute name that will be placed on resulting features.
  • Type: The attribute type is listed in this column. You can change the attribute type by clicking on the field and selecting from the drop-down list. This only affects the resulting features if the selected attribute type is a coordinate type or a date type.
  • Width: This value is ignored for attributes produced from this reader.
  • Precision: This value is ignored for attributes produced from this reader.

Filter

Filter a list of attributes by typing a key sequence into this field.

Read Formulas (.formula)

If this parameter is selected, an attribute will be added to the workspace for columns that contain a formula. The reader reads formulas used to calculate the values of a cell and stores the formula in the attribute <attributeName>.formula.

Read Hyperlinks (.hyperlink)

If this parameter is selected, an <attributeName>.hyperlink attribute will be added to the workspace for columns that contain a hyperlink.

Read Formatting (.formatting)

If this parameter is selected, an <attributeName>.formatting attribute will be added to the workspace for columns that contain formatting.

Multiple File Parameters

Advanced

Schema Attributes