Excel Join Tables By Common Field

 admin
Excel Join Tables By Common FieldExcel Join Tables By Common Field

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 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 a cell in the first row 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.
FieldJoin

Excel Join Tables By Common Field In Excel

Other tips

Excel Join Tables By Common Fields

Excel Join Tables By Common Field

You can merge (combine) rows from one table into another simply by pasting the data in the first empty cells below the target table. The table will increase in size to include the new rows. If the rows in both tables match up, you can merge the columns of one table with another—by pasting them in the first empty cells to the right of the table. Both data sets contain column Item, this allows you to combine them using the column item as a condition. I will in this article use different Excel functions to merge these data sets. The two last examples show how to combine data sets if a cell contains a given value and how to match multiple columns in order to combine the data sets. Creating the Excel consolidated Pivot Table from multiple sheets. A Pivot Table is used to quickly analyze a large amount of data. It allows you to combine information from different tables and sheets and calculate the overall result.

Excel Join Two Sheets On Column

  • 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.