ArcGis reads straight through the Excel tables. You can add a table to ArcMap, import a table into a geodatabase, or create a join or relationship between an Arcgis layer and an Excel table. Yes but … If you do not know how to pre-format your table, you open a Pandora’s box!
Here, in detail, all you need to know to work in peace and quiet.
We will discuss the constraints concerned with:
- the columns of the table
- the rows of the table
- the table cells
Constraints on Excel table columns
1- All columns must have values
Depending on the history of your Excel spreadsheet, you may end up with empty columns to the right of your table. While this has no consequence in Excel, ArcGis will generate as many attribute fields as empty columns exist. Therefore you can end up with dozens, hundreds of useless fields. How to face the problem? Simple, position the column cursor (bottom right) at the limit of your working area. If the horizontal elevator is not at the end of the cursor, you have empty columns
If you open this table under ArcMap you will have
Empty columns are converted to generic Fxx fields and filled with null values.
Solutions:
Before loading the table in ArcMap: delete the empty columns. After loading the table in ArcMap: right-click on the column heading of the table (for each column) and select the option not to display this column:
2- The first line must contain the names of the attribute fields
Well, you already know this, but it’s still worth remembering. If the first line does not contain the names of the fields, either you end up with generic Fxx fields (at best), or with an empty table.
Solution:
Before opening an Excel table with ArcMap, be sure to name the fields on the first line of the table.
3-The first character of the name of a field must be a letter
FALSE! This was the case in the past, but if you have version 10.3 like me, this is no longer valid. Here is an example of an Excel table import with the Excel to Table tool :
You will notice fields beginning with numbers, # and &.
But if you look in the ESRI doc, it’s obvious that it cannot work …
This also applies to the rule that you should not put special characters in the field name. It’s supposed not to pass the join validation. In version 10.3.1, which I use, works without problems.
So? For extra security, avoid special characters in names, but if they are already there, do not waste your time editing them.
4-Do not exceed a certain length in the field names
The maximum lengths depend on the format of the resulting table. If you exceed the allowed length, the field name will be truncated to this length.
The allowed lengths are:
- 64 characters for file geodatabases
- 31 characters for SQL Server and SQL Server Express
- 30 characters for Oracle and DB2
- 10 characters for Dbase (shapefiles)
5-Avoid duplicating names
This is not a blocking factor. The second identical name will be assigned a 1 at the end, the third a 2, and so on.
6- Avoid the ArcMap reserved names
If you have names like OID in your Excel spreadsheet, it will be assigned a _ at the end. But in some cases, the table cannot be displayed. So, avoid the following words in your Excel field names:
- ObjectID
- OID
- IDF
- Shape_Length
- Shape_Area
- ADD
- ALTER
- AND
- BETWEEN
- BY
- COLUMN
- CREATE
- DELETE
- DROP
- EXISTS
- FOR
- FROM
- GROUP
- IN
- INSERT
- INTO
- IS
- LIKE
- NOT
- NULL
- GOLD
- ORDER
- SELECT
- SET
- TABLE
- UPDATE
- VALUES
- WHERE
7- Assign basic data types to your Excel cells
In Excel you have a whole series of possible types for your data. In ArcGis you will only have Text, Number, and Date.
These are the column base types specified in Excel that are used to
define the field type in ArcGIS. When the column type is not specified (that
is, Standard) the field type in ArcGIS is determined by an analysis of the
values in the first eight rows of that column. If the analysis detects mixed
data types in the first eight rows, this column will become a Text field in
ArcGIS and the values will be converted to strings.
The Number and Fraction types in Excel are converted to Double or Long Integer field types in ArcGIS, depending on whether the scan is in the first eight rows of numeric columns.
The Date and Time types in Excel are converted to the Date field type in ArcGIS.
Advanced field ypes in Excel (that is, Currency, Accounting, Percentage, Fraction, and Scientific) are converted to the Text field type in ArcGIS.
Remember that the search for data to determine the type of field to be created is limited to the first eight rows of the table. If you do not have values for a field in these first eight lines, you end up with Text fields.
Excel table rows constraints
All lines should have values
For each row in the Excel table, you will have a record in the ArcGis table. If the Excel line does not contain values, you will have a record filled with Null values.
It is not forcibly a blocking factor, but it can quickly become annoying.
Delete empty lines before loading the table in ArcMap or in a geodatabase.
Excel table cells constraints
1- The cells must not contain formulas
Cells containing formulas are ignored by ArcGis and will not be in the resulting table.
To solve this problem: copy and paste the column with the formula into a new column, copy and paste special / values this column on the original column.
2-The contents of a cell must not exceed 255 characters
According to the documentation, if a cell has more than 255 characters, the field is converted to BLOB and its contents are not displayed. Maybe if you import the Excel table into a personal Geodatabase (Access) this is true. But as far as the display in ArcMap is concerned, you will not be able to load the array (it will appear completely empty) in the ArcMap layers.
However, if you use the Excel to Table tool to a file geodatabase, you are not limited in the length of the text. Even texts longer than 255 characters will be loaded and available (visible) in the table of the geodatabase.
3-Numeric cells must not contain text
In your Excel table you should not use texts such as NoData, NA, Null or even spaces, to indicate the absence of data.
If you do, the field will be converted to a Text field.
Delete all the cells containing your texts. The field will be numeric in ArcGis and the cells will be <Null>.
4-Avoid spaces before and after character strings
If this aspect may not be blocking in most cases, if you are going to use a Text field to create labels or annotations or to create joins between tables, remove the spaces before and after the string by using the Excel’s DELETE function, before loading the table into ArcGis.
5- The cells must not have been merged.
If two cells are merged into Excel, the contents of the first cell disappear and the second cell is replaced by the value <Null>.
We went through everything you need to know to load Excel tables in ArcMap.
