Microsoft Excel (XLSXR) Reader Parameters

Reader Overview

Different feature types are read by default, depending on whether you change the Reader Parameters. (To open the parameters, click the Parameters button in the Add Reader dialog.)

By default, all worksheets and named ranges are selected. You can use the Select button in the Sheets to Read section to filter the display:

Screenshot of Select button and menu options: Sheets, Named Ranges, All, None

  • When you open the Reader Parameters, by default, all of the worksheets in the spreadsheet 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 and there is more than one feature type, you will have to select the feature types to add.

Sheets to Read

This section shows all of the worksheets 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 Address, and it is on the sheet PublicTrees.

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.

Note  When using merged reader feature types (dynamic workflow), the reader will use the Field Names Row and Cell Range parameters of the first selected Sheet or Named Range for all other feature types, regardless of what is set in this table. Individual settings are only supported on individual feature types (static workflow). For alternative approaches to reading in dynamic workflows, see the Troubleshooting section.

The Select menu allows you to choose which worksheets/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. The Excel Reader only supports a single row of field names, and merged cells in the field name row will result in the value being duplicated for all columns it covers. To resolve attribute name duplication, additional numerals will be appended.

Preview

This section 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 section displays the attributes that were created for the feature type (schema scanned).

  • Exposed: Selected attributes will be added to the reader feature type. Unchecked attributes will not be added.
  • 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. For more information, see Usage Notes.

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 Comments (.comment)

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

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.

Schema Attributes