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
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
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
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.