span8
span4
span8
span4
Intro|Previous Section|Next Section
FME can read Excel data from spreadsheets,and then filter,organize,and integrate it with other data formats to make the most of your data assets.In this exercise we'll introduce the basic steps for how to read from an Excel spreadsheet using FME.
We will make use of the WeatherData2009.xlsx spreadsheet,which contains data used by researchers interested in tracking weather patterns for Vancouver.If you have a copy of Microsoft Excel available,open and inspect the spreadsheet.You'll find that it contains a single worksheet titled "WeatherData2009".We will use this dataset to understand how a number of parameters can be set through FME's Microsoft Excel reader parameters dialog box.
1)Start FME Workbench with a new workspace.
2)Add a Microsoft Excel reader to the canvas,and select WeatherData2009.xlsx as the source dataset.
3)Open the reader parameters to set how the spreadsheet will be read into the workspace.From the Microsoft Excel Parameters dialog,you can interact with settings for the Sheets to Read,Preview the data as it will imported,and set how Attribute types will be formatted and displayed.
The Sheets to Read panel can be used to read a worksheet or a named range from a spreadsheet.Simply select the checkbox for the data that you wish to read.It is important to note that multiple worksheets/named ranges can be read simultaneously by checking more than one checkbox.Each checkbox selected will place a new reader on the canvas.
The same panel also lets you define the row containing the Field Names you wish to use,and the Cell Ranges from which to read the data.Just input the number for the Field Name (e.g.1),and input the Cell Range (e.g.B2:G9) to control how the data will be read.
4)Since there is only one worksheet (WeatherData2009) in the spreadsheet,it will be selected by default.Verify that the Field Names Row is set to "1",and the Cell Range is set to "2:".
The first few rows of the sheet (or named range) that have been selected will appear in the Preview panel.Any changes made to the Sheets to Read panel will be reflected here.Attribute names (Columns) are shown in bold text.
All changes to Attribute (Column) formatting can be made by interacting with the Attributes panel.FME will automatically assign types to the Attributes as the data is being read;however,each of the Attributes can also be set manually.
FME attempts to create point geometry for features (rows) that appear to reference a coordinate system.Attributes (columns) with names such as "X","Y",or "Lat","Long" are examples of instances where FME will attempt to build feature geometry.Coordinates can also be set manually through the use of the "x_coordinate" and "y_coordinate" data types.
FME will read Excel fields that are formatted as a date type and convert them to an FME date string.Be aware that this may produce unexpected formatting results.For example,as Excel formatted date of "8/19/2015",would be interpreted in FME as "20150819".
FME will preserve hyperlinks being read from the source Excel file.Just select the hyperlink check box in the reader parameters when adding the reader.Once selected,you'll get two attributes for that field,the field value (hyperlink text) and the hyperlink value.
FME will also preserve formulae being read from the source Excel file.Just select the formula check box in the reader parameters when adding the reader.
The FME Excel reader does not preserve cell formatting (fill color,custom formatting,etc.).However,the Excel writer supports options for applying column formatting.
Attributes can be hidden by un-checking the checkbox from the Exposed Attribute column.Note that the attributes are hidden from view,but not deleted from the reader.
5)Verify that the attribute types are set correctly,and that all the attributes exposed checkboxes are checked,and click OK.
For additional information regardingMicrosoft Excel Reader Parametersplease consult the software documentation.
Demonstration - Converting From Excel Format
For a demonstration of how an Excel Reader is used to transform data into a spatial format,please seeHow to Convert Microsoft Excel to ESRI SHAPE.
Data Attribution
The data used here originates from open data made available by theCity of Vancouver,British Columbia.It contains information licensed under the Open Government License - Vancouver.
Overview of Excel Writer Parameters
Tutorial: Getting Started with Excel
Using a Template File when Writing Excel Data
How to Convert CSV to Microsoft Excel
Viewing and Inspecting Excel Data
Example scripts for deleting Excel files prior to writing (FME 2013 SP1 and earlier)
Excel Provider error (-2147467259) Spreadsheet is full (FME 2013 and earlier)
© 2019 亚搏在线Safe Software Inc |Legal