How to Update Spreadsheet Table by ShapeFile Data

« Go Back   Next Chapter »

The second function from the GIS to Excel group enables you to enrich your existing Excel tables with new data from the Map Layer. An example is shown in the following Excel table. The table has a list of springs with their coordinates; you want to determine the geological unit for all the listed springs and you have stored all the information on the geological units in the evaluated area in your polygon Map Layer.

As in previous cases, first you select a table in the Excel spreadsheet with a column where you want to insert your geological information. Then you click on the GIS to Excel – Update Table ribbon button, and this displays the dialog. Its character is the same as in the previous cases. The top of the window has the names of the selected columns from the Excel spreadsheet and their data format; and you define all the parameters for function in the bottom of the window.

In the first combo-box you choose the Map Layer with all your data. Then, you set the type of connection between the Excel table and Map layer, and their parameters. [Update Existing GIS Layer tells you all about connection options]. In my example, I connect through the spatial coordinates of the spring positions. Now, I define the Excel table column where I want to insert data. In this case, I can insert data from the map layer attribute table or from the spatial data. For example, I could insert the geological unit ID from the attribute table, or the polygon area, the line length or whatever I want. Finally, I define the function for data processing; if I define 1:1 link between the Excel table and map layer, it is sufficient to use function First. If I define link 1:N, select the most appropriate function for your work (Sum, Average, Maximum).

When you define all settings, click Apply; and add-in connects the data and updates the Excel table with your information. In my example, I inserted the geological units where each spring was located in the Geology ID column.

You can understand the Update Excel Table function by this example. Here, you have a table with a list of country districts and a map layer of polygons that spatiallybind the districts. Now you can update the Area column in your Excel table with area values for individual districts, as follows;.

First, select Excel table columns in which you can operate. Then click on the GIS to Excel – Update Table ribbon button for the dialog in the next figure; below. You set the individual parameters in this window as you did in the previous example. In the first combo-box, Select the Map Layer with district polygons. Your Excel table connects with the Map Layer via columns in the Excel table and the Map Layer attribute table. You specify that your data will be entered in the Area Excel table column. In the By GIS Column combo-box, you select the Polygon$Area spatial function at the bottom of the list [Do not select a column from the map layer attribute table; because if add-in finds a line with the same district name in the Excel table as in the Map Layer attribute table, add-in automatically calculates the district area and this value will be used.]

Since this is a 1:1 connection – one unique district occurs in the Excel table and Map Layer only once; you select "First" in the With Function combo-box. Then just click Apply and the algorithm starts. The settings program determines the necessary values and automatically inserts them in the table. As in previous cases, if you only want to update part of the Excel table, use the Filter Excel function to hide unnecessary rows; and only the visible rows will be updated.