There is an increasing co-work between ArcGis and
Excel. Indeed, the information management is infinitely richer with
a spreadsheet such as Excel than with an ArcGis tabular view.
It
has always been possible to load an Excel table in ArcMap. But since
version 10.2 there are two tools that allow you to create an ArcGis table from
Excel and another that allows you to create an Excel table from an ArcGis table.
These tools are also present in ArcGis Pro.
We will discuss how this works.
How to convey an Excel table
to an ArcGis table
We will work with an Excel
file downloaded from the INSEE website, at http://www.insee.fr/en/ppp/databases/recensus/populations-legales/departement.asp
? dep = 29
What you have to
know…
Excel to Table supports the input of Excel Workbook (.xlsx)
and Microsoft Excel 5.0 / 95 (.xls) Workbook formats.
The tool assumes that the tabular data is sorted vertically.
The first line is used for the field names of the output
table. These field names can be renamed during the validation
process to avoid errors or duplication of names. The
empty columns between the data are kept and a generic field name is assigned to
them (for example, field_4). In our example, we have to start
by eliminating the title lines and keep only the data table with the field
names row.
Each field must have a uniform data type. Supported
data types include floating, text, date, and Boolean fields. However Boolean fields are stored as short paths.
The example table has
multiple sheets. In
this case, do not forget to eliminate the title lines in all the sheets you
want to use in ArcMap.
How to import the
Excel table
You will find the Excel import
and export commands in the Conversion Tools -> Excel toolbox
The
setting window opens:
You specify the input Excel file.
You specify the output table. There
you have two options. The ideal is a table in a
geodatabase. You will have comprehensive field
names as well as all the commands linked to the geodatabases SQL.
The worst is a dbf table. If
you choose this option, you will enter a table name in a directory (not in a
geodatabase) and, most importantly, you should not forget to add the .dbf
extension. If you don’t, it will not work. However,
remember that dbf tables have field name length limits and a limited version of
sql commands.
Tip: If you have not already done this, switch to geodatabase
mode …
You select the sheet you want to import from the drop-down
menu, and click OK
You find your table in ArcMap:
How to convey an
ArcGis table to an Excel table
Now, we will discuss the
opposite command: we will create an Excel table from our new ArcGis table.
To
perform this action, we click on the other tool of the toolbox Data
Management-> Excel-> Table to Excel
Easy peasy: input table and Excel workbook as output.
We find an Excel table quite right:
