Joining tables by attributes is a common task for an ArcGIS user. However, it can be frustrating for the novice and even the expert at times as there are many requirements that must be met exactly. Here are some tips to make your join by attributes successful. I have ordered the tips so the ones at the top of the list will solve most problems, while the tips further down can help resolve the less common but more stubborn problems.
- Save the table as a CSV. Excel files are convenient because they support multiple tabs, but they have a tendency to no not import correctly in ArcGIS. I have found that saving the file as a comma delimited (CSV) format results in the fewest problems.
- Make sure the join fields are the same format. When you join two tables make sure the fields with the common records are the same type (string or numeric). The records must match exactly in order to join, this includes spaces and is case sensitive (for example ‘New Brunswick’ would not join with either ‘New brunswick’ or ‘NewBrunswick’)
- Simple field names. Make sure that the field names (located on the top row) contain only letters and numbers. All other symbols will cause the table to import improperly or not at all. This includes spaces as well, make sure there is no space at the front or end of the field name. Also make sure field names do not start with a number.
- No empty rows. An empty row will signal that there is no data further below. Ensure there are no empty rows (including the top row) so all of your data is imported.
- Non-empty first cells of data. I always make sure that the first row of data (the second row in the table) is fully populated. I have had problems where fields are not imported or imported as the wrong type (see next note) when the first data cell is empty.
- Numeric fields. Occasionally a numeric field will import as a non-numeric string or date. Sometimes this can be fixed by formatting the entire column in Excel as a number. Sometimes this can be fixed by ensuring that the first row of data is not empty. Sometimes I need to convert the field after I join it in ArcGIS by creating a new numeric field and copy the data using the field calculator.
- Some field names are reserved. Field names such as ‘Date’, ‘Time’ and ‘User’ are reserved (link), and can not be added to a table. If your import table has such names, try renaming them slightly to something like: ‘Date_’, ‘Time_’ or ‘User_’.
- Reset the formatting. Copy your data into Notepad and back into a blank spreadsheet document. This will reset the formatting and help solve some stubborn problems.
- After joining the tables don’t forget to export the resulting layer to make the join permanent (see image below).
- Exporting to a Geodatabase will let you have longer field names. Shapefiles will truncate your field names to 10 characters if they are too long.