Microsoft Excel Reader Parameters
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 workspace parameters (for example, feature type properties), or by scanning x features of the feature type, where x is defined by the Max Features to Read parameter in the Workbench Navigator (scan_max_features). The schema is useful for defining the schema which may be used by writers when generating a workspace, but it is ignored on read.
Therefore, features beyond the Max Features to Read 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 Max Features to Read or manually edit the attribute types in the Attributes section of the parameters dialog).
View the Attribute Types
|
char(<width>)
|
Character fields store fixed length strings. The width parameter controls the maximum number of characters that can be stored by the field. No padding is required for strings shorter than this width.
Notes:
Strings encountered that have length greater than width will still be returned; they will not be truncated.
The width parameter is only valuable if the features will be passed to another format that requires this information.
|
|
datetime
|
Datetime fields convert doubles or strings in Excel to FME datetime string format, depending on the formatting option in Excel.
YYYYMMDDHHmmSS.mmm
(Year, Month, Day, Hour, Minute, Second, Milliseconds)
|
|
number(<width>,<decimals>)
|
Number fields store single and double precision floating point values. The width parameter is the total number of characters allocated to the field, including the decimal point. The decimals parameter controls the precision of the data and is the number of digits to the right of the decimal.
Notes:
Incoming numbers of greater width or precision will still be returned and will not be modified to fit. Numbers will also not be padded to fit the specified decimal value.
The width and decimal parameters are only valuable if the features will be passed to another format that requires this information.
|
|
string
|
String fields store variable length character data up to a length of 32767 characters. Values larger than 32767 are truncated.
|
|
number
|
Number fields store single and double precision floating point values.
|
|
x_coordinate
y_coordinate
z_coordinate
|
Coordinate fields store double precision floating point values. There is no ability to specify the precision and width of the field. If an x and a y coordinate are specified on a feature type on read, features will use them to try to create Point geometries. The attributes are not consumed by the geometry and so will still appear on the features produced.
|
|
boolean
|
Bool fields store TRUE/FALSE data. Data read or written from and to such fields must always have a value of either true or false.
FME represents Booleans as Yes and No for TRUE and FALSE respectively, so any logging within FME will reflect this. Round tripped values will be written as TRUE/FALSE as expected.
|
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.
In this example, the selected area is a named range: the name of the named range is Second_Table, and it is on the sheet NoSheetInfo.
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 on the Field Names Row is blank due to being a merged cell, it will automatically be given the merged cell value.
Preview
This area shows a preview of the data (100 rows) of the selected feature type (either feature or named range). As shown in the example here, if you choose a header row, that row will be shown in boldface text.
Attributes
This area displays the attributes that were created for the feature type (schema scanned).
| The attribute name that will be placed on resulting features. |
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.
|
Note: This value is ignored for attributes produced from this reader.
|
Note: 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 enabled, an <attrName>.formula attribute will be added to the workspace for columns that contain a formula.
Advanced
Scan Schema in Dynamic Workflow
When reading the schema from a source Excel file in static workflows, which have fixed schemas, the source schema is determined solely by the cached schema created when adding the reader to the canvas. However, in dynamic workflows, the schema may be requested of the Excel reader by the writer when executing the workspace.
In this case, the default source for the schema is the same as in static workflows – the cached schema from the Excel reader parameter dialog. This is indicated by the default value which is unchecked (set to No). Selecting this option (setting it to Yes) will cause the reader to produce a schema from the source file instead. This allows workflows where the user adds columns to the source file over time without changing the dynamic workspace.
Additional File Handling
When reading multiple files, only one file will be shown in the parameter dialog.
Apply default settings to additional files: The choices in this parameter dialog will only apply to the file shown in the settings box. Additional files will use the reader’s default settings.
Apply current settings to additional files: Any additional files are assumed to have the same schemas as those defined in the GUI. Any additional feature types will not be picked up.
Expand Merged Cells
This attribute tells the reader what to do when it encounters a merged cell range.
- If selected (which is the default), the value will be placed into every attribute contained within the merged cell.
- If not selected, the value of the range will only be placed in the first attribute of the first attribute containing it.
For example, say there is a merged cell range of A1:B2 (which contains 4 cells) and it has the value testString.
- If you select Expand Merged Cells, you receive two features with attributes A=’testString’ and B=’testString’.
- If you do not select Expand Merged Cells, you receive one feature with attributes A=’testString’ and B=<missing> and one feature with A=<missing> and B=<missing>.
Additional Parameters (Workbench Navigator)
Some parameters (including most Advanced parameters) are accessible only from the Workbench Navigator after you add a reader or writer to a workspace. The general hierarchy is shown below; however, the list order may change depending on the format. These parameters are not visible in the parameters dialog.
Read Blank Cells As
This parameter defines how FME will read back blank cells.
- Missing: This is the default. Blank cells will be read back as missing attributes on the output feature.
- Null: Blank cells will be read back as null attributes on the output feature.