Joining Tables to Maps


1.    Create a folder called Table_join. On DIA 322 computers, you might want to create this folder in your user Documents folder (e.g. C:\Users\jdoe\Documents\Table_join). On the DIA 222 computers, you might want to create this folder on the D: drive under D:\course number\user name\ (e.g. D:\ES212\jdoe\Table_join).

2.    Download the data for this exercise then uncompress the Table_join.zip file to your newly created Table_join directory.

 

Additional information on Joining Excel tables to GIS features can be found here. Note: If you are working with Excel 2010 data, you may need to install the 2007 Office System Driver (this driver is already installed on GIS lab PCs)


Contents

Step 1:         Create a new map document 1

Step 2:         Modify a table for ArcMap. 3

Step 3:         Joining a table to a layer 6

Step 4:         Exporting a join to a new shapefile or geodatabase feature class. 11

Step 5:         Performing another join. 12

 


Step 1:           Create a new map document

You will create a new document where you will load a Maine counties  layer.

From the Start menu, open All Programs >> ArcGIS >> ArcMap.

In the ArcMap – Getting Started window, select New Maps and Blank Map as the template.

Click OK.

Next, you will load a new layer. The following step assumes that you don’t have a folder connection to this project’s workplace.

Click on the Add Data button .

If a folder connection to your workplace does not yet exist, click on the Connect to Folder button  then select your Table_join folder.

Click OK to close the Connect to Folder window.

You should now see a list of data elements in the new folder connection.

Select Counties then click on the Add button.

Step 2:           Modify a table for ArcMap

Now that you have a new map document open, you will add a table to your map. The data file Alcohol_use_2008.csv is a comma delimited text file that can be viewed in any text editor. A CSV stores data that are separated by commas. The Alcohol_use_2008.csv file tabulates the percentage of students between grades 6 and 12 who have consumed alcohol at least once. More information about the data can be found here.

As of version 10.1, CSV tables are unstable in ArcGIS. The workaround solution is to convert the CSV file to an Excel file. In performing the conversion, we will also take the opportunity to make changes to the header names. ArcGIS has strict requirements when it comes to table headers as outlined in the following text box.

!!

ArcMap has strict guidelines when it comes to importing tables into ArcMap.  Table field names cannot contain non-alphanumeric characters such percent signs. It also cannot contain spaces, even at the beginning or end of the field name. ArcMap will accept underscores ‘_’ as a space substitute. A summary of field name requirements follow:

·         Field names need to start with a letter.

·         Field names should only include alphanumeric characters or underscores.

-       None of these: `~@#$%^&*()-+=|\\,<>?/{}.!'[]:;

-       No spaces (That includes before the field name, in the middle, or after it.

·         Field names will be cut off after 64 characters

·         There are certain reserved words that should be avoided. You can see the complete list here.

 

From the Start menu find and open Excel (All Programs >> Microsoft Office >> Microsoft Excel).

The following steps assume that you are working with Excel 2010. If you are using a different version, the interface may look somewhat different.

In Excel, click the File tab.

In the File tab, click on the Open Button.

In the Open dialog window, locate and open the Alcohol_use_2008.csv in your project directory (e.g. D:\login_name\Table_join). To view the .csv files in your directory, you might need to select Text Files from the pull-down menu in the lower right-hand corner of the window.

Next, you will clean up the column headers to meet the aforementioned requirements which include no percent characters and no spaces.

Change the headers “County name”, “% no use” and “% use” to “County_name”, “Percent_no_use” and “Percent_use”.

After making the edits, we will save the file as an Excel file.

Click on the File tab then click on Save As.

Select Excel Workbook as file type.

Name the output file Alcohol_use_2008.xlsx in the Table_join folder.

Save the file then Close Excel.

Step 3:           Joining a table to a layer

Go back to you ArcMap session and add the Alcohol_use_2008.xlsx file by clicking on the Add Data button .

You can only load a single worksheet from a spreadsheet, therefore ArcGIS requires that you explicitly select the worksheet to load.

Double-click on Alcohol_use_2008.xlsx.

There is only one worksheet in our Excel file.

Select the only worksheet and click Add.

At this point, ArcMap will have loaded the table in the TOC.

You may notice that the TOC environment has changed. Since tables cannot be viewed in the standard List By Drawing Order  environment ArcMap switched to the List By Source environment . Don’t forget that you can toggle back and forth between these TOC views by clicking on the appropriate button near the top of the TOC window pane.

Right-click on the Alcohol_use_2008 table and select Open.

The table should look just as it did in Excel.

Next, we will join this table to the existing data layer (Counties) in the ArcMap document. To join a table, to an existing layer, a field (attribute) common to both tables must exist. Let’s view the Counties layer’s attribute table.

In the TOC, right-click on Counties and select Open Attribute Table.

The Counties layer has an attribute called COUNTY that will be used to join the csv table to the Counties layer. A successful join requires that all records in both tables be identical--character for character. This means that a discrepancy as benign as a character’s case (upper case vs lower case) will result in an unsuccessful join.

In the TOC, right-click on the Counties layer and select Joins and Relates >> Join.

In the Join Data window select Join attributes from a table in the first pull-down menu, COUNTY in the first field, Alcohol_use_2008 in the second field and County_name in the third field (see graphic below).

Click OK to close the Join Data window.

In the Counties attribute table you should see the attribute columns from the Excel table (County_name, Percent_no_use and Percent_use) appended to the end of the Counties attribute table. At this point, you can work with the appended attributes as though they belonged to the original Counties layer.

In the TOC, right-click on the Counties layer and select Properties.

In the Layer Properties window, select the Symbology tab.

Select Quantities in the Show window on the left side of the Layer Properties window.

Select Percent_use for the Value field.

Click OK to close the Layer Properties window.

It’s always good practice to save your map document on a regular basis. Now is a good time to do so.

From the File pull-down menu, select File >> Save As.

In the Save As window, navigate to the D:\login_name\Table_join folder and save the document as Join_exercise.mxd.

Step 4:           Exporting a join to a new shapefile or geodatabase feature class

The join that you have created so far is only temporary and exists “virtually” within the current ArcMap session. In other words, if you were to load the Counties layer into a new MXD document, the join would be gone. To make the join permanent, you need to export the layer (and its joined table) to a new feature.

In the TOC, right-click on the Counties layer and select Data >> Export Data.

In the Export Data window select All features from the Export pull-down menu and name the output Alcohol_by_county.shp.

Click OK to save the file.

When prompted to add the exported data to the map, click Yes.

Go ahead and symbolize the new layer as you did with the original one near the end of step 3. You will use the Percent_use attribute for the field value.

Note: when exporting to a shapefile, field names longer than 10 characters will be truncated to the first ten characters. If you want to save a GIS layer with attribute field names longer than 19 characters, you need to export the layer to a geodatabase.

Next, you will remove the join from the original Counties layer.

In  the TOC, right-click on Counties and select Joins and Relates >> Remove Join(s) >> Remove All Joins.

In the next step you will join another table that will have additional challenges.

Save the document by clicking on the Save button .

Step 5:           Performing another join

Next, you will join another table to the Counties layer. The table to be joined contains estimated median income data for 2009 (src: US Census). The table is also in a CSV file however this time, therefore you will save it as an Excel file then join that file to the Counties layer.

In the Windows file management window, double-click on the Estimated_median_income_2009 file located in the Table_join folder.

The file should open in an Excel application. Notice that the table does not have county names listed, instead, the table identifies the county by ID. This is OK since the Counties layer in the ArcMap document has a county code column called CNTYCODE.

Before we save this CSV file to an Excel file, do you see anything that needs to be changed? Remember the field name requirements covered in Step 2? You will clean up the header names to meet ArcMap’s table requirements.

Replace all spaces in the header names with underscore ‘_’ and move the ‘2009’ text to the end of the header name in the second column.

Your table header should look like the following:

Save the CSV file as an Excel file.

In the Excel application, click on the File tab then Save As.

In the Save As window, select .xlsx as the file type and name the output Estimated_median_income_2009.xlsx. Make sure that the file is saved in the Table_join folder.

Click Save to save the file and exit Excel.

NOTE: always make sure to close an Excel file that is open inside an Excel application before opening it in ArcMap. If you don’t, ArcMap will not open the file because of a file lock placed on the file by the Excel application.

In ArcMap, click on the Add Data button .

In the Add Data window, you will see two Estimated_median_income_2009 files. Select the Excel version.

Click Add to add the data.

You will notice that the Add Data window did not close, nor did the Excel file get added to the ArcMap document. Instead The Add Data window is displaying the Excel file’s sheet(s). The reason for this is that ArcMap can only add a single Excel sheet at the time. This is not a problem for us since our Excel file only has a single sheet.

In the Add Data window, select Estimated_median_income_2009$ then click Add.

You now should see the median income table in the table of content. At this point, it’s always good practice to open the contents of the table to make sure that all data have been properly read.

In the TOC, right-click on the Estimated_median_income_2009 table and select Open.

It seems that all data and headers were properly imported. Now let’s try the join. Remember that this time around, we will join the Excel table to the Counties table using the County IDs instead of the County names.

In the TOC, right-click on the Counties layer and select Joins and Relates >> Joins.

In the Join Data window, select Join attributes from a table from the first pull-down option, then CNTYCODE from the second pull-down menu (this is the column from the Counties layer that is used to join the table with) and select Estimated_median_income_2009$ in the third pull-down menu.

Now try selecting a field from the Choose the fields in the table to base the join on pull-down menu. The options should be blank.

So what happened? Why is ArcMap not letting us select any columns from the Excel table? Well not only do the records between both tables need to match character for character, but they also need to match by data type. So we need to figure out the data types for each table.

If the spreadsheet’s attribute table is not opened, go ahead and open it. (e.g. in the TOC, right-click on the table layer and select open).

In the attribute table, right-click on the County_ID header and select Properties.

In the Field Properties window, note the data type. The Excel file saved the column as a Double data type (which is a numeric data type). In other words, Excel (and ArcMap by extension) is treating the column as a number field.

 

Click OK to close the Field Properties window.

In the TOC, right-click on the Counties layer and select Open Attribute Table.

In the Table window, right-click on the CNTYCODE column header and select Properties.

In this table, the county ID is treated as a String (which is a text or character format) and not a numeric field. Because the data types between both tables do not match, we cannot join the tables. There are several solutions to this problem; one that involves changing the data type in the Excel table.

Click OK to close the Field Properties window.

Because ArcMap places a lock on the table, it cannot be opened for editing in Excel. We will need to close ArcMap before opening the Excel file for editing.

In the TOC, right-click on the Estimated_median_income_2009 layer and select Remove.

Save the ArcMap project and close ArcMap.

In a Windows file management window, open the Estimated_median_income_2009.xlsx file by double-clicking on it (the file should be in your Table_join folder).

In the Excel window, right-click on the County_ID column (i.e. right-click on the A column header) and select Format Cells.                                                                                                                                                                                 

Under the Number tab select Text then click OK to close the Format Cells Window.

You may recall that the CNTYCODE column from the Counties layer had single digits padded with 0s (e.g. 01, 03, etc…). We will need to rectify this in the Excel table since the values will need to match character for character.

Add the number 0 in front of all single digits in the County_ID column.

Save the Excel file then close Excel.

Using the Windows file management navigate to your Table_join folder and double-click on the Join_exercise.mxd file to open the ArcMap document.

Click on the Add Data button and add the Estimated_median_income_2009$ sheet from the Excel file (remember that you can only load one Excel sheet at a time).

Again, it’s always good practice to check the data.

Right-click on the Estimated_median_income_2009$ layer and select Open.

Feel free to check the County_ID properties to ensure that it is now a String data type. You can also tell whether or not an attribute is numeric  by the data value’s left/right justification. Left justified data indicate a string (non-numeric) data type and right-justified data indicate numeric data type.

Now that we have matching county ID values between the Counties layer and the Excel table we can proceed with the join.

Right-click on the Counties layer and select Joins and Relates >> Joins.

In the Join Data window select the values for each field as shown below:

Click OK to close the Join data window.

The median income data is now joined to the Counties layer. Remember that this join is temporary. If you want it to be permanent, you must export it to a new data file. But first let’s make sure that the join was successful.

In the TOC, right-click on the Counties layer and select Open Attribute table.

Make sure that the Excel columns are properly appended to the table

Before exporting the joined layer to a new data file we will remove unnecessary/redundant attributes.

In the TOC, right-click the Counties layer and select Properties.

In the Layer Properties window, select Fields.

This option lets you control which attributes will be useable within your map session. It also defines which attributes will be exported in an export process.

You will uncheck the Shape, CountyCode and County_ID attributes.

Click OK to close the Layer Properties window.

If you view the Counties layer attribute table, you should now only see three columns.

Now we are ready to export the layer to a new data file.

In the TOC, right-click on the Counties layer and select Data >> Export Data.

Name the new data file Income_by_county.shp.

Click OK to proceed with the export.

When asked if the exported data should be added to the map as a layer, click Yes.

Go ahead and symbolize the newly added layer using the Estimated_ (this is the estimated median income attribute; remember that Shapefile formats will truncate attribute names longer than 10 characters). If you forgot how to symbolize a layer, see Step 3.

Save the MXD file.

This completes the steps for this exercise.


 Manuel Gimond, last modified on 8/21/2013