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.
