Edit Layer Using Excel Layer (Video)
How to export a layer, edit it in Excel, and merge your changes back into the map.
Video Transcript
In this video, we are going to show you how to export the contents of a layer to Excel, make changes to that data in Excel, and merge that data back into your map. As you hopefully already know, you can make changes to features on your map one at a time by clicking on them and editing the data in the pop-up form. If you are an experienced Microsoft Excel user, then you might have a desire to make changes to multiple features all at once using the powerful editing features of a spreadsheet tool. If that's the case, then this video is for you. If you are not a regular Excel user, then we don't recommend trying this. In our scenario, we are going to use Excel to clean up this fire hydrant layer. You will notice that the size field has inconsistent entries that we want to clean up, and we want to add a condition field. The first thing I recommend doing before performing a batch operation like this is to make a backup copy of your layer. To do this, select the fire hydrants layer, go over to more backup manager, and then select Backup. The map automatically makes a backup copy of your layer every night whenever you make changes, but we want to tell it to make a backup copy right now so that if I really mess something up I can roll back to this point. Next, we need to export the fire hydrant layer. To do that, select the hydrant layer from your list of layers. Select the Browse tab, and then on the action menu choose export with totals this will download a CSV file to your computer's downloads folder I’m using the chrome browser which shows my list of downloads right here but your browser might have a different way of opening a downloaded file click on the downloaded file to open it in Excel. Using Excel, I can quickly remove the inch marks from these features and I can scroll through the list to make sure I got them all. While you are editing your data in excel, you can modify any cell. You can add additional columns, but you cannot change the name of the column or add additional rows to that column and you should never modify the contents of the feature ID column because this is the key that will be used to apply these updates back to your map. I’m going to add an additional column called Condition. Now, I’m going to assign all hydrants a default condition of good. When we import this back into the map, it will recognize this as a new column and automatically add it to the layer. Before we import this back into the map, I like to delete any columns that I didn't make changes to so that I am sure that I only update the certain columns that I did change. I strongly recommend this. You especially want to delete these statistical columns at the end of the table that were automatically added to the spreadsheet by the export process. Otherwise, it will permanently add these columns to our layer when we merge it into the map. When I am all done, I save the Excel file back in the map. I click Menu import a layer, and merge table. Now, I’m going to choose that file that I was just editing, and I’m going to choose the destination layer as the hydrants layer. The key field name I want to enter is FDR ID. This will tell it to reference that field then select import. Note this process of merging a table based on feature. It will only work if you are merging the data back into the same layer that it was exported from. Also note that this merge process will never create any new features or delete any features from your layer. It will only update the text on of the layer. To see the results immediately, refresh the page. If we come into our layers here, go back to hydrants. We can see now it's all uniform. There are no inch marks on them, and if we scroll to the end, we now have a condition column marking all of our hydrants as good.