How to Update ShapeFile Layer by Excel Spreasheet Data

The second function from the Excel to GIS group will update the attribute table of your existing Map Layer – ShapeFile. The following example explains the feature functionality. I loaded a polygon layer of districts into the map panel, and I have a list of springs with their parameters, such as spring yield and temperature in the Excel table. This feature’s functionality is explained by the following diagram and the text below it.

First you select the data in the Excel table you want to use in the Map Layer update. As in the previous examples, you can use Excel features such as Filters. After your Excel data selection, click on the ribbon button Excel to GIS – Update Layer; and the following dialog is displayed.

This dialog is similar to the previous one. The upper part of the window has the table characterizing selected Excel data. The nature of the function is defined in the bottom of the dialog. Select the map layer you want to update in the first Map Layer combo-box.

Then you must define the connection between the Excel table and the Map Layer. There are three connection methods. The first is to connect via Coordinates. If you choose this option, the map layer will be updated on the basis of spatial connections. The individual records from your Excel table updates the map layer features if the Excel table record intersects the map feature. If this happens, the row from the table will be used further in the algorithm.

The second connection is through the values in the Excel table and values in the attribute table of the Map Layer. If the values from the selected columns in the tables are the same, the row from the Excel table will be used to update the feature in the Map Layer.

The last connection is called ROW ID. This is a specific link that has great importance during manual editing of the Map Layer attribute table. This connection assumes that a column exists in the Excel table that identifies the individual features in the Map Layer. This identifier is defined as an integer number that indicates the order of features in the Map Layer, and it begins by numbering the first feature 0 (zero). This connection can be used, for example, if you want to directly edit the whole Map Layer attribute table in the Excel spreadsheet. Here, you firstly export the whole attribute table to Excel through the GIS to Excel – Eport Layer function. You can easily export the Map Layer attribute table into the Excel spreadsheet, use powerful Excel features such as dynamic formulas or filters and then paste the calculated result back into the map layer attribute table. Exporting is explained in detail in chapter GIS to Excel.

When you define the connection, you can continue with function setup. If the selected connection is defined through spatial coordinates, you will also see information about the coordinate system used. You then define a column in the Map Layer attribute table you want to update. In addition to the existing column, you can also create a new column here that will be added to the table.

Then press the New button and the small dialog Create New Column appears where you define your new column. Its definition is based on data type and name. Click Apply and the new column will be added to your attribute table and you can use it for Map Layer updating.

My example above created a new column for numerical values labeled "Q_sum". When you select your column for update, you must then select the column from the Excel table that will update the attribute table. The above example used a column containing values for spring yields. Since one row in the attribute table can be linked with multiple rows in the Excel table, you have to determine the statistical function you want to use to pre-process the result for update. If the tables are linked 1:1, choose the First or Last item from the statistical functions. You can use also use these items if you want to update columns with string data type.

The following figure shows the entire dialog with all function parameters. Here, I am going to update a polygon map layer connected to my Excel table through coordinates. I update the newly created column Q_sum, where I want to insert the yields sum of all springs that intersect my polygons.

When you have set all parameters, click Apply and add-in automatically updates the Map Layer attribute table. This process can take longer, depending on the number of records in your Excel table and the number of features in the Map Layer. You are then advised that the update is completed in the next message box.

When the map layer updating is finished, you can use standard GIS interface features to visualize the results.

Changes you make in individual map layers are not automatically saved on computer disk. If you want to save, select the map layer in the Legend panel and then click the Save Layer button in the ribbon toolbar. This refers to both Excel and GIS functions.