Microsoft Excel (XLSXR) Reader Parameters

Use this parameter to expose Format Attributes in FME Workbench when you create a workspace:

• In a dynamic scenario, it means these attributes can be passed to the output dataset at runtime.
• In a non-dynamic scenario, this parameter allows you to expose additional attributes on multiple feature types. Click the browse button to view the available format attributes (which are different for each format) for the reader.

Coordinate systems may be extracted from input feature data sources, may come predefined with FME, or may be user-defined. FME allows different output and input coordinate systems, and performs the required coordinate conversions when necessary.

For systems that know their coordinate system, the Coordinate System field will display Read from Source and FME will read the coordinate system from the source dataset. For most other input sources, the field will display Unknown (which simply means that FME will use default values). In most cases, the default value is all you'll need to perform the translation.

You can always choose to override the defaults and choose a new coordinate system. Select More Coordinate Systems from the drop-down menu to open the Coordinate System Gallery.

Changing a Reprojection

To perform a reprojection, FME typically uses the CS-MAP reprojection engine, which includes definitions for thousands of coordinate systems, with a large variety of projections, datums, ellipsoids, and units. However, GIS applications have slightly different algorithms for reprojecting data between different coordinate systems. To ensure that the data FME writes matches exactly to your existing data, you can use the reprojection engine from a different application.

To change the reprojection engine, Select Workspace Parameters > Spatial > Reprojection Engine. In the example shown, you can select Esri (but the selection here depends on your installed applications):

By default, the reader ignores all Excel filters and reads every row of data. Select this option if you only want to read in the rows that are not being filtered out. This parameter sets the feature-type level default.

If the field structure of the first several rows of a feature type is representative of the remainder of the feature type, this option can be set to prevent FME from unnecessarily reading further rows from a potentially large range when determining its schema.

If left blank, there will be no limit and all rows will be read.

If checked, the reader will trim all whitespace characters from the ends of all attribute names. For example, “ Category ” would become “Category”.

This parameter accepts a list of ASCII characters which it will trim from the ends of all attribute names. (This is similar to setting the AttributeTrimmer transformer Trim Type parameter to Both.)

For example, when entering "_ in the parameter field, the attribute ""___Category_"" becomes Category. It's also a useful option for removing quotation marks from column names ("region" becomes region).

This parameter defines how FME will read back blank cells.

• Null – Blank cells will be read back as null attributes on the output feature.
• Missing – Blank cells will be read back as missing attributes on the output feature.

Changing this parameter to Exclude will automatically remove all columns without any values or formulas from the schema. This is useful if you have a worksheet with many columns but only a few of them have any data.

• Include - Default and all columns in the specified range will be included.
• Exclude - All columns without values or formulas will be removed. This will also exclude columns with formatting that do not have any values or formulas.

This parameter tells the reader what to do when it encounters a merged cell range.

• If checked (the default), the value will be placed into every attribute contained within the merged cell.
• If unchecked, the value of the range will only be placed in the first attribute of the first attribute containing it.

For example, if there is a merged cell range of A1:B2 (which contains 4 cells) and it has the value testString:

• If you check Expand Merged Cells, you receive two features with attributes A=’testString’ and B=’testString’.
• If you do not check Expand Merged Cells, you receive one feature with attributes A=’testString’ and B=<missing> and one feature with A=<missing> and B=<missing>.

Sets how any images embedded in the worksheets will be handled. An image’s top left corner determines the cell the image is considered to be placed in. Images may be ignored on read, read as an additional value for that attribute (<attributeName>.raster_data), or as additional geometry features. If images are considered to be part of the row’s data, use the Attribute option. The Geometry option is useful if images on a worksheet are related to the dataset but not tied to any particular row.

• None – No images will be read. This is the default option.
• Attribute – If a row within the selected columns contains an image, the cell’s value will be read as normal, and additional attributes will be added that pertain to the image. Images that fall outside the Cell Range for the feature type will never be read.
  • .raster_data – Blob (binary-encoded string) containing the raw image file contents. Can be converted to a raster geometry via the RasterReplacer transformer.
  • .raster_format – The image format of the blob (PNG, GIF, JPEG, TIFF). Less common formats supported by Excel (WMF, EMF, DIB) may not be supported as attributes and will generate a warning.
  • .raster_height – The image’s displayed height in pixels.
  • .raster_width – The image’s displayed width in pixels.
• Geometry – Images in BMP, PNG, GIF, JPEG, and TIFF formats will be read as Raster features. Less common formats supported by Excel (WMF, EMF, DIB) may not be supported as geometries and will generate a warning. When reading embedded images as Geometry, all images on the sheet will be read as features, even if they fall outside the feature type read area.

It is possible to link images to an Excel worksheet without inserting the image into the workbook. These Linked Images cannot be read directly by FME and will generate a warning when one is encountered. When encountered in Attribute mode, in lieu of the attributes listed above, only .raster_location will be set to the file path where the image can be found. Note that the path is not guaranteed to be valid. In Geometry mode, the xlsx_raster_location format attribute will be set instead of a raster geometry.

Feature Types corresponding to Named Ranges will never produce features for images.

Check this option to enable the Additional Date/Time Format parameter.

By default, the Excel Reader will recognize date values in ISO 8601 and FME formats. When reading Excel files with date values that do not conform to this format, users can specify additional formats to check for when scanning for attribute types.

This option only affects the generated schema of the feature types. This can be useful when using automatically detected schemas. Values read are unaffected by this option.

The Excel Reader supports reading certain form controls as cell values. When enabled, the reader will check for form controls whose top left anchor is in an attribute cell and if present, will use its value to populate the attribute, overriding any other value that may reside in the cell. Only one form control per cell will be read – this will usually be the first control that was added to the sheet. Multiple form controls per cell are not supported.

Formatting, formulas, and hyperlinks may still be read from a cell with a form control if the appropriate option is selected.

Currently, only checkbox-type controls are supported. The possible values are:

• True (boolean) when the box is checked.
• False (boolean) when the box is empty.
• Mixed (string) when the box is filled with a pattern. This can happen when the form control is bound to a cell with the value #N/A or the formula =NA().

Example

In the input Excel file, cell checkboxes are shown checked, unchecked, and mixed:

After the file is read, the Reader Parameters Preview area shows the cells before and after Read Form Control as Cell Values is checked:

Note  The form control’s top left anchor may not correspond exactly to the visual part of the form control. This anchor visually appears to be in row 6, but its anchor is in row 5. Two controls exist in the same cell on row 5 and the one that gets read will depend on editing order.

This form control is correctly positioned in row 6 and can be read as an attribute value:

This option tells the reader how fields named with a common geometric label are handled.

The default value for this option is checked.

When this option is unchecked, features with common X/Y or Latitude/Longitude labels will not be created as point geometry. This is useful when the data is incomplete or missing.