topic Re: Some data is lost when I join an Excel file to a shapefile in ArcGIS Pro Questions

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

Institute_forPublic_Strategies — Mon, 30 Dec 2019 21:30:34 GMT

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?

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

DanPatterson_Retired — Mon, 30 Dec 2019 22:04:11 GMT

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

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

JoeBorgione — Mon, 30 Dec 2019 22:19:10 GMT

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

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

Institute_forPublic_Strategies — Mon, 30 Dec 2019 22:34:23 GMT

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?

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

JoeBorgione — Mon, 30 Dec 2019 22:47:03 GMT

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

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

Institute_forPublic_Strategies — Mon, 30 Dec 2019 22:56:25 GMT

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.

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

JoeBorgione — Mon, 30 Dec 2019 23:11:43 GMT

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?

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

Institute_forPublic_Strategies — Tue, 31 Dec 2019 18:36:03 GMT

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.

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

DanPatterson_Retired — Tue, 31 Dec 2019 21:13:00 GMT

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.

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

JoeBorgione — Wed, 01 Jan 2020 15:25:27 GMT

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.

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

Institute_forPublic_Strategies — Thu, 02 Jan 2020 18:34:34 GMT

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!