topic Re: Can you join Excel tables with an attribute table? in Roads and Highways Questions

Can you join Excel tables with an attribute table?

EllieHakari — Mon, 13 Jun 2016 20:36:51 GMT

Is it possible to join Excel tables with a layer’s attribute table? My department is collecting data for curb ramps (to see if they comply with regulations) and have to collect data such as slopes, lengths, distances, etc. Right now, the app that we created puts all that info into an excel file, but that makes it difficult for us to do anything with because most people want a visual. I was wondering, if I created a template in ArcMAP (such as points at each curb ramp), labeled them with a unique ID, and then matched that with a unique ID in the Excel table, would I be able to easily transfer the data over to an attribute table? That way, clicking on a point would provide its information, plus we could isolate the layer to, say, show only the ramps that don’t comply. Or we could color-code. Something like that.

Re: Can you join Excel tables with an attribute table?

AdrianWelsh — Mon, 13 Jun 2016 20:45:47 GMT

Ellie,

Yes you can join an ArcMap feature class table to an Excel table. Using a unique ID would be best but be sure your Excel table is ready to go into ArcMap (like no special characters for column headers/field names, etc.).

I found this super old blog that discusses some of the Excel issues:

Formatting Excel Data for Use in ArcGIS Desktop | Esri Training Matters

Some of these issues no longer exist but it's still good to be aware.

Re: Can you join Excel tables with an attribute table?

EllieHakari — Mon, 13 Jun 2016 20:54:16 GMT

Thank you, I will take a look!

Re: Can you join Excel tables with an attribute table?

GaryWaters — Wed, 15 Jun 2016 12:25:41 GMT

Have you tried ArcGIS Maps for Office?

ArcGIS Maps for Office

That product allows you to use a map inside of Excel.

Re: Can you join Excel tables with an attribute table?

AdrianWelsh — Wed, 15 Jun 2016 12:37:32 GMT

ArcGIS Maps for Office is a good thought. Though, if the excel table only has attributes and not really a well-defined location (such as lat/long, etc.), then this might not be the best solution. I would somewhat imagine that most DOT's would use routes and mileposts for their location data and I would think that ArcGIS Maps for Office would not be able to calculate these locations (...yet...).

Re: Can you join Excel tables with an attribute table?

GaryWaters — Wed, 15 Jun 2016 12:45:43 GMT

I am not technical enough to describe the exact manner in which you can do this, but I know that if you create a service that has geography with a unique key value you can link the excel data to that feature. Tom Brenneman could explain how to do that. Tom has also written a sample script in Excel that takes an LRS (Route/Measure) reference for a point feature and generates the x,y coordinates by calling Roads and Highways functionality to translate LRS references to coordinates. That makes the data in Excel with LRS point reference locations usable directly in the map in Excel.

Re: Can you join Excel tables with an attribute table?

AdrianWelsh — Wed, 15 Jun 2016 12:51:11 GMT

Wow, that would be interesting to see. I cannot say I have used ArcGIS Maps for Office much but this is intriguing. In order to call LRS data, do you have to have Roads and Highways installed and licensed?

Re: Can you join Excel tables with an attribute table?

Anonymous User — Wed, 15 Jun 2016 12:51:38 GMT

Gary you are right. If you have a feature service in ArcGIS Online or Portal for ArcGIS you can use an identifier for those features like a foreign key for your data in Excel. So if you have projects as a feature service with a ProjectID, you could map your Excel data using the ProjectID. Check out Custom location types in the help: http://doc.arcgis.com/en/maps-for-office/design-and-use/location-types.htm#ESRI_SECTION1_E96FB9B9D0B7432F8F07378DD98A97D6

Re: Can you join Excel tables with an attribute table?

Anonymous User — Wed, 15 Jun 2016 12:55:50 GMT

Adrian,

Here is the sample that uses a VBA function inside of Excel to translate LRS locations to X,Y locations. It does use Roads and Highways web services. So Roads and Highways would need to be installed somewhere.

http://www.arcgis.com/home/item.html?id=9e3b86e5f0d6404baf7f5e25f52a545d

Re: Can you join Excel tables with an attribute table?

EllieHakari — Fri, 17 Jun 2016 19:29:43 GMT

I haven't. I'd install the trial, but our admin privileges were just taken away. I'd have to get supervisor approval and whatnot. I've been researching ever since you recommended it though. I think it looks interesting and could have a lot of uses in our office, so thank you for sharing!