How to Convert Microsoft Excel to Esri Shapefile

Introduction

In this tutorial, we will perform a very common transformation: taking non-spatial data in a Microsoft Excel file (*.xlsx) and converting it to an Esri Shapefile (*.shp). The Excel file contains x_coordinate and y_coordinate values for each row, which will be converted to points as the data is read. We will also merge data from multiple sheets, or feature types, into our output shapefile.

Video

This video was created with FME version 2016.0. Some of the steps might be slightly different, but the overall process is the same for newer versions of FME.

Step-By-Step Instructions

1. Add Microsoft Excel Reader

Open a blank FME Workspace and add a new reader. In the reader dialog, type in Microsoft Excel, then browse to the PublicArt.xlsx file. Since this is a non-spatial or tabular format, we will need to add a coordinate system, in the Coord. System box type LL84. Now to make sure that our attributes that contain the latitude and longitude of the points are read correctly, click on the Parameters button.

Under the Attributes section, ensure that Longitude is set to x_coordinate and Latitude is set to y_coordinate. Newer versions of FME will automatically do this, but it is always a good idea to check.

We also want to ensure that we are reading in all of the sheets within the Microsoft Excel workbook. At the top double-check to confirm that all of the sheets are enabled under Sheets to Read. Once all of the parameters have been confirmed, click OK twice to add the reader.

The six sheets from the Excel file have now been placed on the canvas.

2. Inspect Data

To ensure that the data was read correctly, let’s inspect one of the feature types. Click on the Downtown reader feature type on the canvas to open the popup menu. Then on the popup menu click the View Source Data button to view the data in the Visual Preview Window.

In the Visual Preview window (or FME Data Inspector), open the Graphics View and ensure that there are points. If you want to double-check the latitude and longitude is correct, you can add a background map and confirm that the points appear inside of the Vancouver, British Columbia city limits.

Note: This step can also be completed in FME Data Inspector for versions previous to FME 2019. Just add an Inspector transformer and then run the translation.

3. Set the Output (Writer) Format to Esri Shapefile

Next, we need to add a writer to the canvas. Click on the Add Writer and in the Add Writer dialog box, for the Format, select Esri Shapefile. Then for Dataset, browse to a folder to save the shapefile and then click OK to add the writer.

After clicking OK, a Select Feature Type dialog will appear. Select any one of the feature types, which one you choose doesn’t matter as they all have the same schema.

4. Update Writer Properties

Open up the writer feature type parameters (in this example we used West End). In the parameters, change the Shapefile Name to Public Art and then change the Geometry to shapefile_point. Click OK.

Connect the PublicArt writer feature type to all of the reader feature types. It should be six reader feature types going into one writer feature type.

5. Run the Workspace

The workspace is now ready to be run. Run the workspace by clicking on the green play button.

6. View the Output Dataset

To view the output dataset, click on the PublicArt writer feature type to open the popup menu, then click on the View Written Data button.

In Visual Preview, you should have 185 features containing all of the public art in Vancouver.

Advanced

Since we are writing all of the Excel sheets into one Esri shapefile and they all have the same schema, when we were adding the reader, we could have set the Workflow Options to Single Merged Feature Type. This would have resulted in only one reader feature type on the canvas with all of the sheets merged into one. By doing this, it will also enable the fme_feature_type attribute (which is found in the Format Attributes tab in the reader feature type parameters) that contains each of the neighborhood names.

Next, add an AttributeManager and rename fme_feature_type to NeighName. The final step will be to add NeighName to the writer feature type user attributes. See the Advanced-ExcelToShapefile.fmw workspace that is included in the downloads for more details.

Data Attribution

The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.