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?

[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

[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?

[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]

- 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]

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]

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, thanks for sticking with me on this 🙂

1) Here is a screen shot of my stand alone summary table:

2) Screen shot of my census tract attribute table:

3) Screen shot of the attribute table when I join the stand alone table to it:

I have verified that the census tract fields in both tables have the same number type, number of decimal places, and number of significant digits. I just can't figure out why I am getting Null values when I do the join.

[DanPatterson_Retired]

IF you were using the OBJECTID_1 field for joining to OBJECTID_CensusTract THEN I would say you had an issue since the former is text and the latter is numbers.

However, you say you are using the common CT2010D field in both which is some numeric type and precision...

I always worry about using float/double fields for joins because of this help file warning.

Fields of different number formats can be joined as long as the values are equal. For example, a field of type float can be joined to a short integer field.

It is bad enough to trust joining a float to an integer... but I wouldn't even consider float to float (save the flames... it works, until it doesn't).

To expedite you moving forward, why not text by adding a "text" version of that field to both tables and use it for the join?

If it joins using text-to-text, then it illustrates my concern.  If it doesn't... Joe will take over

Even though you say they have the same number of decimal places (I presume no more than 2?), I never trust anything that originates from excel unless I have cleaned it up myself prior to making it into a table for use in Pro.

[JoeBorgione]

Good catch Dan.  Float to float, float to double sounds like nothing but heart burn. 

This is good advice and I plan to use it as a rally cry not only for th new year, but the new decade:

I never trust anything that originates from excel unless I have cleaned it up myself prior to making it into a table for use in Pro.