nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Need help joining spreadsheet to attribute table

JeffreyWells

I'm trying to join a spreadsheet of 4,000 rows of property data to the county parcel shapefile using the ALTKEY field as the join field. When I convert the spreadsheet to a table it creates an OBJECTID field which is mismatched from the FID field in the county parcel attribute table. So the data from my spreadsheet is being appended to the attribute table matching the OBJECTID field but the wrong ALTKEY and parcel.

Find more posts tagged with

Analysis

Content Management

Desktop

Accepted answers

All comments

JeffreyWells

So it seems that I fixed the issue I was having. I removed the filtering/sorting from my spreadsheet and while experimenting with creating the Join I unchecked "Keep all target features" and now my join is successful. I then created a layer file from the selection and proceeded to export the layer as a new feature class and now my join fields are blank in the resulting attribute table. The parcels from the join do display on the map, but the data from my join is missing.

JoeBorgione

me: ...Without knowing I can only speculate the Altkey field in your ‘shape file’ is numeric while the Altkey in your table is text. Emphasis on speculate...

you: All fields in the imported table are Text except for currency values which are Double.

Push yourself away from the desk, and take a breath. This is learning experience. I've said this a thousand times before and @DanPatterson knows where I stand when it comes to excel; it's great to do taxes with, but as a data management tool, it's not so great.

All that said, take a few moments to examine your data in both the shape file and table/excel worksheet. As Dan mentions, empty fields are often the the wrench that gets tossed into the cog.

danretired

If there were any blanks in the ALTKEY column, then that may be an issue. You have to set the column properties explicitly in excel to the format type and I suggest using a proper null value for any cells in excel that contain blanks. For instance -999.99 for numeric and "no_value" for text/string fields

JeffreyWells

Even if I import and convert the Excel to a table, and use ALTKEY as both Input Join Field and Join Table Field, the appended/joined fields appear all <null>. All fields in the imported table are Text except for currency values which are Double. Every time I try I get the same result. There must be something about my table that causes the attributes to display as <null> but I can't imagine what it is.

JoeBorgione

ObjectID is internally assigned as a system unique and sequential identifier. It has nothing to do with any other user defined attributes.

If you follow @DanPatterson ‘s suggestion you should find my earlier speculations are basically true.

danretired

avoid drag and drop shortcuts, use

Excel To Table (Conversion)—ArcGIS Pro | Documentation

from ArcToolbox to fully control the process of conversion of a spreadsheet to a useable table, especially for join operations

JeffreyWells

ALTKEY in my spreadsheet is formatted as text but when I save it as CSV for importing into ArcGIS it converts to General so I just drag and drop the .xlsx into the map to avoid the .csv conversion. ALTKEY in the Parcels shapefile is formatted as text in the attribute table. When I base the Join on ALTKEY, all of the appended fields are <Null>. When I base the join on OBJECTID, all the Join fields appear but are mismatched with the rows in the shapefile table they are joined with. I've attached some Snipits of my Join table. The first and second shows the OBJECTID, ALTKEY, and PROPERTY ADDR and OWNER from the shapefile Parcel data. The third and fourth shows the same data from my spreadsheet. The OBJECTID is assigned starting at 1 based on the sort order of the spreadsheet. If the OBJECTID field matched the ALTKEY field in both tables it would work as intended. So how is this supposed to work normally when you start with a spreadsheet/table that doesn't have an OBJECTID already assigned?

Snip1.jpg

Snip3.jpg

Snip2.jpg

Snip4.jpg

JoeBorgione

When you convert you spreadsheet to a table, what is the Altkey data type? Numeric, text?

Without knowing I can only speculate the Altkey field in your ‘shape file’ is numeric while the Altkey in your table is text. Emphasis on speculate.

It could be that when mapping fields it picks the objectid as the first numeric field encountered. See if you can map to the table’s Altkey field manually. That would indicate the field type is numeric.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.