topic Re: Joining SHP with XLS sheet using Excel in ArcMap Questions

Joining SHP with XLS sheet using Excel

StanislavaV — Sun, 17 Oct 2021 07:52:21 GMT

Hi, guys,

I am trying to join SHP by field CONCATENATE with my XLS sheet. SHP attributes looks like this:

ID NAME CONCATENATE

122	765	122765
345/1	765	345/1765
987/3	345	987/3345

Because of those slashes in ID fields, I choose to TEXT type for field CONCATENATE.

When I open DBF file (from that shp) in Excel, I add a field MATH, where I will calculate, then save it as XLS.

To problem is that when I try to join my SHP by field CONCATENATE with XLS sheet by field CONCATENATE ArcMap can´t offer it in drop-down menu.

Any ideas?

Re: Joining SHP with XLS sheet using Excel

JayantaPoddar — Sun, 17 Oct 2021 08:53:28 GMT

NEVER manipulate your GIS Files in non-GIS applications or Windows Explorer. They may corrupt your data as well.

Here is what you can do in ArcMap itself.

1. Add your shapefile to the map.

2. From Attribute table, add a new field (Datatype: Text). FieldName should be limited to 10 characters for a shapefile.

3. Use Field Calculator with following expression

Parser: Python

str( !ID! ) + str( !NAME! )

Re: Joining SHP with XLS sheet using Excel

StanislavaV — Sun, 17 Oct 2021 08:59:24 GMT

yes, that field CONCATENATE in SHP was created that way. but then I need to save attributes as XLS (I will try table to excel) but problem is JOINing xls sheet with shape by field concatenate.

I choose JOIN on that SHAPE and then - 1. field in layer that the join will be based on - CONCATENATE

2. table to join - XLS sheet

3. field in the table the base to join in - CONCATENATE field does not show up in dropdown menu.

Re: Joining SHP with XLS sheet using Excel

JayantaPoddar — Sun, 17 Oct 2021 09:01:02 GMT

Yes. Try a Table to Excel.

Let me know if you are still unable to Join (by Attribute) with Excel Sheet. If required, in the Excel file, change the cell format of CONCATENATE column to TEXT.

Re: Joining SHP with XLS sheet using Excel

DanPatterson — Sun, 17 Oct 2021 09:24:36 GMT

Another way out possibility is that CONCATENATE may be a reserved word... try CONCAT