topic Re: Excel in Data Management Questions

Excel

DavidBreneau — Fri, 20 Jun 2014 14:24:05 GMT

What is the best format for an excel spreadsheet to join the data to a GIS layer? I have the home use license for desktops. I put data into an excel spreadsheet, imported that spreadsheet into my GIS on ArcMap, and joined the data into a layer that I had created. However, it did not seem to join properly judging by what I saw on the layer's table. Any suggestions?

Re: Excel

JoeBorgione — Mon, 23 Jun 2014 16:06:36 GMT

What is the best format for an excel spreadsheet to join the data to a GIS layer? I have the home use license for desktops. I put data into an excel spreadsheet, imported that spreadsheet into my GIS on ArcMap, and joined the data into a layer that I had created. However, it did not seem to join properly judging by what I saw on the layer's table. Any suggestions?

What's the best.... Always a tough question to answer.

What type of attribute are you joining on (text, numeric, etc?) What is the join functionality: 1 to 1 or Many to 1? Personally if it's anything but 1:1, I use a relate instead. What is giving you the impression that the join isn't working properly?

Finally, and again just personally, I prefer to use excel for my invoicing and other book keeping procedures, but I use real-live database tables for my GIS work. YMMV....

Re: Excel

DavidBreneau — Mon, 23 Jun 2014 17:49:48 GMT

Thanks for the reply. I am joining both text and numeric attributes. I am new to this level of GIS, so I don't know what "join functionality" is; I am joining the many attributes in my Excel to the many different attributes in my layer using a common attribute: jurisdiction name. The first time I did the join I saw my data, but not the attribute names (eg "population"). When I tried it again I got the attribute names, but the excel data was all converted to "null" in the layer table. I figured perhaps there is an excel file extension (xls, xml) that "talks" to GIS the best. When you say you like to use "real-live database tables", what do you mean by that?

David

What's the best.... Always a tough question to answer.

What type of attribute are you joining on (text, numeric, etc?) What is the join functionality: 1 to 1 or Many to 1? Personally if it's anything but 1:1, I use a relate instead. What is giving you the impression that the join isn't working properly?

Finally, and again just personally, I prefer to use excel for my invoicing and other book keeping procedures, but I use real-live database tables for my GIS work. YMMV....

Re: Excel

JoeBorgione — Mon, 23 Jun 2014 18:34:08 GMT

Relationships are typically viewed as 1 record to 1 record, or Many records to 1 record, or 1 record to Many records or Many records to Many records. Examples:

1 to 1: 1 address point, 1 water bill
Many to 1: many owners, 1 parcel
1 to Many: 1 parcel, many owners
Many to Many: many owners, many parcels

While it may appear that 1 to Many and Many to 1 are the same, they are not. It is the order in which the relationship is constructed. In my parcel example, 1 to many might be a polygon feature class using the parcelID as the common attribute between it and a table of owners. If you are trying a 1 to many join, you will only join to the first of the many records: Probably not what you want.

Getting null values from an excel table is a common complaint (do a search and you'll see what I mean). The standard response is that ArcGIS does not like attribute (column) names with spaces or special characters. By real-live database table I mean a table in some sort of database: be it a File Geodatabase, an Access Table, SQl table, etc. Anything but an excel work sheet.

First thing is to check your column names in the excel table. You can also use the IMPORT utility to convert the excel works sheet into a database table in any flavor of geodatabase. You need to watch that process as the types of fields in excel can play games with you; like a number may be converted to a text or vice-versa.

This is bread and butter data management; you might want to search the help files for relationships and how to use them.

<edited moments later> Here are a couple of links to forum postings on this subject:

http://forums.arcgis.com/threads/69527-Table-Join-Resulting-in-Null-values-%28Picture-Included%29?highlight=join+excel+null+values

http://forums.arcgis.com/threads/101332-Table-Join-Resulted-in-NULL-values?highlight=join+excel+null+values

http://forums.arcgis.com/threads/51663-Help!-Trouble-Joining-Tables?highlight=join+excel+null+values

http://forums.arcgis.com/threads/39200-Weird-problem-with-joining-tables?highlight=join+excel+null+values

http://forums.arcgis.com/threads/36228-Convert-Field-from-Double-to-String?highlight=join+excel+null+values

Seeing a pattern here?

Re: Excel

MatthiasAbele — Tue, 29 Jul 2014 10:42:38 GMT

Dear David,

try the GISconnector for Excel. It will solve all your problems.

Best,

Matthias