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; } } }

Some data is lost when I join an Excel file to a shapefile

Institute_forPublic_Strategies

I have a census tract shapefile and an Excel file in which one of the fields is census tract number (field = Site_Tract). I am trying to join the Excel file to the shapefile using the common census tract number field. I have verified that the two common fields have the same number type (double), number of decimal places, and significant digits. But when I do the join, some of the records are populated with 'null.' What am I doing wrong?

Find more posts tagged with

Accepted answers

All comments

Institute_forPublic_Strategies

Through a work-around, I finally got the join to work but not without complications. Just for the record, I encountered these problems when I played around with data types:

1) I created a new 'text' field in both tables but that still produced the null values when I did the join.

2) I then created a new numeric field, short integer, decimal places = 6 in both tables; again.....some null values, but not as many as before. One thing that I thought was odd was that I assigned it 'short integer' in the fields view but after I saved it, it converted the field to 'long integer.' Even though I pretty much repeated this same step over and over, I don't know why

'short' integer worked over 'long,'
it won't join tables in which the common fields were both 'double' numeric types
short integer was changed to long integer after I saved the addition of the new field in fields view.

Thanks for all your help!

JoeBorgione

Sorry, but I'm having a difficult time following what you are doing or trying to do. Can you post some of your 'pre-join' data, specifially those that are now missing data after the join?

Institute_forPublic_Strategies

Hi Joe,

The problem is that pre-join, all the data exists in both the stand alone table and the shapefile's attribute table. After the join, only some of the data in the attribute table is replaced with 'null.' There seems to be no rhyme or reason as to which records are replaced with a null either.

JoeBorgione

- it is actually a stand alone table that ArcGIS created to get a COUNT

Sounds like you are using a summary table then; I don't know of anyway to control whether or not a field allows null in that case. May I ask what the problem is with <Null> values? Personally I prefer them over a blank space.

Institute_forPublic_Strategies

My apologies for making a misstatement - it is actually a stand alone table that ArcGIS created to get a COUNT and not an Excel spreadsheet. One problem I do see is that the Site_Tract field in that stand alone table allows for Null values. Is there a way to change that to not allow for Null values?

JoeBorgione

To add to Dan's comments, are you sure the fields actually have data in them where you are seeing <Null> after the join?

DanPatterson_Retired

check the spreadsheet for little things like spaces following a number. Format your columns explicitly and don't allow blanks... put in an appropriate null value for the data type

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.