Select to view content in your preferred language

Add data from Excel sheet to attribute table based on same name

1242
5
08-22-2023 02:22 AM
Lars_H
by
New Contributor II

Hello everyone,

I have an extensive shapefile with locations of wastewater treatment plants. For some of these plants I have more precise operating values in an Excel table. One attribute both the shapefile and the Excel table have in common are the names of the plants. They are exactly the same. Is it possible to transfer the data from the Excel table to the Shapefile (i.e. to the attribute table) without having to type in the values by hand? This should not result in a new attribute table, but the data should appear for the plants for which the data is available, and the column should simply remain empty for the others.

I hope my question has become clear. Thank you very much for your help 🙂

Best Regards
Lars

5 Replies
JakeSkinner
Esri Esteemed Contributor

Hi @Lars_H,

First convert the Excel table to a geodatabase table using the Excel to Table tool.  Then, use the Join Field tool to join the fields from the geodatabase table to the shapefile.

DavidSolari
Occasional Contributor III

Note that this will add new fields to the shapefile. If you have existing fields ready to go, try Add Join followed by Calculate Field (one field at once) or Calculate Fields (many fields at once, trickier to use).

0 Kudos
Lars_H
by
New Contributor II

Hey,

Thank you for answering my question so fast. Basically what you suggested worked. I worked with Join Field, because I needed to add a new fild to my shapefile. In my layer (shapefile) the new field is added. The problem is that i get only <Null> values for every single row, regardless of whether the name of the plant is the same or not.

What I checked:
- The transfer field that should be added is of the same type, both in the shapefile, as it is in the table (float, because the values are numbers)
- The type of the join field (in this case the names of the plants)  is "Text" in both the shapefile and the table
- no spaces in field names 
- Excel table was successfully converted into a File Geodatabase Table

I want to mention that the File Geodatabase Table has less rows than the layer, because it only includes the plants with additional infomation. Is that a problem?
Also some field calues of the join field have a space included (e.g. "WWTP Munich"). But as long as the field name has no space in it, there shouldn't be  a problem, right?

Do you have any solutions for this problem?

Thanks in advance!
Lars

0 Kudos
RhettZufelt
MVP Notable Contributor

If converting to FGDB feature class doesn't work, make sure the plant names ARE exactly the same.

Often when coming from Excel or other sources, there can be trailing spaces at the end of the text.  If so, they will not match.

R_

0 Kudos
JakeSkinner
Esri Esteemed Contributor

Can you upload a sample of your data?  Also, I try to avoid shapefiles when possible.  Can you convert your shapefile to a File Geodatabase feature class and see if exhibit the same behavior?

0 Kudos