Merge Table
This import tool makes it possible to combine the data from a CSV file into an existing layer in your map. Unlike the other import tools, this tool does not create any new features on your map but rather it matches records in your CSV file to an existing feature and then replaces the field values in the feature with the field values from the CSV record.
CSV File To Import Select the CSV file containing the records that you want to merge into a layer
Destination Layer Select a map layer that you want the CSV data merged into.
Key Field Name Indicate here the name of the field found in both the CSV file and in your layer that contains a unique identifier that will be used to match the CSV records to your map features.
Use Strict Key Matching When checked, the key field values have to exactly match. When unchecked, formatting characters such as hyphens, spaces, and leading zeros will be ignored when determining a match.
Below are two examples
Example 1:
You have a layer of fire hydrants in which the only text you recorded for each hydrant is a Hydrant Number.
Additionally you have a CSV file that lists a lot of additional data about each hydrant
The first thing you have to do is make sure there is a field in both the CSV file and in the layer that has values that uniquely identifies each record. This is called the "Key Field" and in this case that field is "Hydrant#". It is also important that these fields have the same name in both places so you may need to change the name of your key field in the CSV file to match the key field name in the layer. In the above example the key field is the left most column of the excel file but it can be any column. Run the Merge Table tool in the map, select this CSV file, select the existing layer, and indicate that the key field is called Hydrant#.
After running your layer will look like the table below. There are four important observations to make here:
- Note that the existing Line Size field in your layer was overwritten with values from the CSV file because the CSV file had a field with the same name.
- Secondly note that new fields were added to the layer to accommodate those fields in the CSV file that didn't match any existing fields in the layer.
- Thirdly notice that hydrant feature "H-104" didn't get updated because there was no matching record in the CSV.
- And lastly, note that hydrant "H-102" in the CSV file did not get imported because there was no matching feature with the same key value in the existing layer.
Example 2
One thing users like to do is export a layer out to a CSV, manipulate the data using Excel, and then re-import the data back into the map. This was possible to do in the past using the Import CSV points tool but this only worked for point geometry and it had issues like loosing all your special fields like pick lists, dates, attachments, etc. This example shows how this can be better performed using "Merge Table". One limitation to understand up-front is that you cannot add any new records using Merge Table. You can only manipulate field values or add new fields.
First, export your layer out to a CSV file like you would normally. Then edit your CSV file in excel. You can change any field values and add new columns just don't alter the "Ftr ID" column since we will be using that as the key field for merging the data back into the map. Your CSV file may contain some extra statistical rows or columns generated by Diamond Maps that we don't want imported back into the map such as totals, centroids, lengths, etc. If you want those to be new columns in your layer it is fine to keep them but if you don't want them in your layer then you need to remove them now from your CSV file. Lastly, run the Merge table tool, select this modified CSV file, select the original layer that this data came from, and indicate that your key field is "Ftr ID."
Now your original layer has been updated with all the changes you made using Excel and all of your special field definitions and layer settings are still intact.