Joining SHP with XLS sheet using Excel

233
4
10-17-2021 12:52 AM
Labels (3)
StanislavaV
Occasional Contributor

Hi, guys,

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

ID NAME CONCATENATE
122765122765
345/1765345/1765
987/3345987/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?

0 Kudos
4 Replies
JayantaPoddar
MVP Esteemed Contributor

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! )

 

  



Think Location
StanislavaV
Occasional Contributor

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.

0 Kudos
JayantaPoddar
MVP Esteemed Contributor

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.



Think Location
0 Kudos
DanPatterson
MVP Esteemed Contributor

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


... sort of retired...
0 Kudos