Table Join Resulting in Null values (Picture Included)

[SamanthaM]

I have a shapefile of subdivisions. There is a separate excel file that contains the average year built for the subdivision. I am attempting to join them based on their common attribute, the subdivision name.

The problem is once I do so, it results in null values. I thought it was perhaps because the shapefile had the string stored in caps, and in excel it was in proper form. I converted the excel column into caps lock as well and still no luck.

[ATTACH=CONFIG]18599[/ATTACH]

[T__WayneWhitley]

I think you mean to say you have a separate Excel table (not shapefile) to join containing year built values?

Your Excel table in the pic still shows proper case - additionally, joining Excel tables can be problematic and I would probably just convert the table and work with the result within ArcGIS.

If you want to work with Excel to 'massage' this into a successful join, it looks like it could work, try this:
- Insert a new column (you can hide it if you want).
- Use the UPPER function to convert to all upper case, e.g., to convert val in A1 use formula in B2:  =UPPER(A1)
- Paste this formula for the new column values.
- Try the join using the new column.

Tip:  You may need to get rid of leading/trailing spaces too, so use the TRIM function as well [=TRIM(UPPER(A1))]

Also:
http://support.esri.com/en/knowledgebase/techarticles/detail/31793

[SamanthaM]

The Upper function was utilized, although the original image does not reflect this. I verified there are no trailing/leading spaces as well. As shown here. I pasted the formula into the original column.
[ATTACH=CONFIG]18600[/ATTACH]
You stated I can convert the table and work with it in ArcGIS, how do I go about doing that? Also is there a way in excel to see precisely the type of data stored in a column (float, double, text etc)

Thanks

[T__WayneWhitley]

The Upper function was utilized, although the original image does not reflect this. I verified there are no trailing/leading spaces as well. As shown here. I pasted the formula into the original column.
[ATTACH=CONFIG]18600[/ATTACH]
You stated I can convert the table and work with it in ArcGIS? How do I go about doing that?

Thanks

Can you 'read' the Excel table okay in ArcMap?  If you can't open the table to view it within ArcMap, then there's probably something wrong with the sheet name or headers or illegal characters somewhere - see the previously provided link.  If you can read it OK, generally you can convert it to dbf or gdb, etc., with Copy Rows or Table to Table... see how that goes and try the join based on that.


EDIT:
uh, just one more thing probably worth brief mention - being able to access Office stuff from ArcGIS requires OLE DB connectivity drivers.  So if there's a problem (or omission) over on the Office side, say for example you're trying to use 2007 xlsx and you don't have that Office version installed) you can download/install the missing connectivity components, http://www.microsoft.com/en-us/download/details.aspx?id=23734.

Or, export from Excel to a more ArcGIS-compatible format like csv.  (incidentally, to csv is a good way to strip database-unfriendly formatting too)

[SamanthaM]

Seems to be reading just fine
[ATTACH=CONFIG]18601[/ATTACH]

[T__WayneWhitley]

I think I now see your problem - you will want to join by the new field, but it appears there is a space in it --- I don't think that is allowed in using Excel in the join.  Correct that in Excel and try the join again, or proceed directly with conversion.

Try conversion to gdb or dbf table, see if it looks okay in ArcMap, then try the join.
Table to Table is in the Conversion toolbox of Data Management...or, if it is already in ArcMap, should be able to simply right-click, export...

EDIT:  Check that- I meant 'Table to Table' is in Conversion; 'Copy Rows' is the one in Data Management.  Use either if going the Toolbox route...

(if scripting, the suffix aliases):
_conversion
_management

[T__WayneWhitley]

Actually, even to convert to another table format using ArcGIS, you will have to fix any field headers containing spaces or special characters.
This is according to the documentation, both for Copy Rows and Table to Table.


And since I am still writing here, I'd like to mention I came across something that does the reverse - convert to Excel.  I intend to test this later - could come in handy at my office:

Additional Conversion - Generic Tools
http://resources.arcgis.com/gallery/file/geoprocessing/details?entryID=95009B25-1422-2418-7FB5-B8638...

[SamanthaM]

The space in the excel file was indeed the problem, thanks!