5

I have a point shapefile with a lot of data that needs to be cleaned up. I'd much prefer to work with the table in Excel. When I use the "Table to Excel" tool, I can get the data into Excel, but when I use the "Excel to Table" tool to bring it back into ArcMap, I lose the points. The "Shape*" field is gone!

Is there any way to edit shapefile data in Excel without losing the points? Or do I just have to make my changes in ArcMap?

Improve this question
4
  • 1
    Does your shapefile have a field containing a unique identifier for each point?
    – Dan C
    Commented Jun 6, 2018 at 17:09
  • Yes, each point has a unique identifier
    – Bob Dole
    Commented Jun 6, 2018 at 17:29
  • 2
    FYI it is possible to edit the shapefile's .DBF file (which contains the attribute data) directly in Excel. But you should NEVER, ever do that, you can render your shapefile unusable!
    – Dan C
    Commented Jun 6, 2018 at 18:17
  • I understand Dan C's concern about editing the dbf file directly but it is convenient and we do it occasionally for quick edits. We always have a backup shapefile and no sorting or filtering is allowed during the editing. We don't use Excel because it no longer can save to dbf, but Libre Office's Calc program works fine. For major edits we use a process similar to what Mattropolis has in his answer. We never add new text fields in Calc or Excel since they come in as 255 wide text fields; so these are always added if needed in the GIS program prior to editing.
    – John
    Commented Jun 7, 2018 at 12:45

4 Answers 4

Reset to default
3

Since you have a unique ID, you could do the attribute edits in Excel.

  • Export the attributes to Excel with "Table to Excel"
  • Drop the fields that you're editing in Excel from the shapefile in ArcMap with "Delete Field" tool.
  • Edit the attributes in Excel
  • Import the attribute data back to ArcMap using "Excel to Table"
  • Use the "Join Field" tool in ArcMap to join the attribute table fields back to the shapefile. Use your unique ID field for the Input Join Field and the Output Join Field. In the Join Fields box specify the fields you want to be copied to the shapefile.
Improve this answer
0
2

All Excel to table is doing is converting it essentially into a more readable format (text) so arc can read it. Once the table is added to arc, you can right click it and 'Display X Y Events.' provided you have coordinate columns, select the column(s) containing xy events. This will create an xy event layer that can be exported as a shapefile. Just right click the xy event layer and export data. If for some bonkers reason you don't have coordinate column, you'll need to calculate them by adding fields for x and y. Do this by adding 'double' field type and use calculate geometry to get the x and y values. Now you're ready to reimport it. However, it really makes more sense to get comfortable with manipulating tables in Arc if you're going to be using it heavily. Excel is great for making pretty financial reports, but doesn't have much place storing spatial data.

Improve this answer
1
  • 1
    There are also far more opportunities to corrupt the shapefile from Excel, since sorting, inserting, deleting, modification, or rewriting could render the shapefile unusable
    – Vince
    Commented Jun 6, 2018 at 17:51
0

If you were working in QGIS*, it'd be even simpler ...

  1. Select the features of interest, and Edit|Copy;
  2. Paste directly into an empty Excel sheet. You'll get a WKT_geom (geometry) column, and columns for all the other fields;
  3. Edit the data, leaving the WKT alone unless you want to manually change geometries;
  4. Back in QGIS, Edit|Paste features as and create a new layer, or paste over the original table.

You can even build WKT strings in Excel (with column name wkt_geom) and then paste directly into QGIS as a spatial layer.

*tested in QGIS 2.18 only.

Improve this answer
0

If you work with point you should add 2 column for X and Y coordinate and populate them with calculate geometry (right click on column header of the attribute table) before exporting to excel. When you import back your data you could use the add XY data to generate a new point layer.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.