Can I create/design my fields in Excel and import them to ArcGIS Pro while creating feature classes?

1395
7
Jump to solution
04-03-2021 04:07 AM
Labels (3)
AlexKweya
New Contributor III

First off, I’m aware that one can import tables or fields from other feature classes when creating fields for a new feature class. However, so far unless I have an exact schema from another feature class, I have to additionally go through the list I import from an Excel table to set certain parameters like Field Aliases and Data Types.

 

My intention is to have some sort of template in Excel whereby I will design all my fields and specify the Field Name, Alias, Data Type, “Nullable Status”, Number Format, Domains and other parameters; after which, when creating fields for feature classes, on importing the Excel table, I can have them automatically populate as per the “attributes” set in Excel without having to tweak say the Alias or “Nullable Status”.

 

Is that possible? It would really be a better and faster way to create fields without moving around in the Create Feature Class pane to populate/adjust fields and values

 

I’ll appreciate any insights.

 

Alex Kweya.

1 Solution

Accepted Solutions
jcarlson
MVP Notable Contributor

I don't believe this is possible, at least, not without some custom scripting. The conversion tool only lets you specify the row that has the column names and the range for the data. There's no way to tell the tool to look elsewhere in the sheet/workbook for metadata on the individual fields.

What's more, the conversion tool only makes a "best guess" at the data type based on a columns contents. A single cell with a typo can cast the whole column as another type.

This is one of the key downsides to spreadsheet-style data as opposed to a true database. Even if you use some of the built-in data validation tools in Excel, there's nothing about a given column that is actually defined on a deeper level that another program would understand.

All that said, with some scripting, I do think this might be doable, and not all that difficult. Suppose you had another sheet in your Excel file called "Field Metadata", in which the fields had settings defined for their alias, type, etc. in its own table. You could use python to convert that table to a list of lists, then submit that as a parameter on Add Fields (multiple). Here's the example from the docs:

import arcpy
arcpy.env.workspace = "C:/data/district.gdb"
arcpy.management.AddFields(
    'school', 
    [['school_name', 'TEXT', 'Name', 255, 'Hello world', ''], 
     ['street_number', 'LONG', 'Street Number', None, 35, 'StreetNumDomain'],
     ['year_start', 'DATE', 'Year Start', None, '2017-08-09 16:05:07', '']])

 

You'd need to create an empty feature class first, then use Add Fields. After that, you'd need to append the data from Excel → Feature Class rather than directly converting. But it's still using built-in tools, no custom functions or anything.

- Josh Carlson
Kendall County GIS

View solution in original post

7 Replies
jcarlson
MVP Notable Contributor

I don't believe this is possible, at least, not without some custom scripting. The conversion tool only lets you specify the row that has the column names and the range for the data. There's no way to tell the tool to look elsewhere in the sheet/workbook for metadata on the individual fields.

What's more, the conversion tool only makes a "best guess" at the data type based on a columns contents. A single cell with a typo can cast the whole column as another type.

This is one of the key downsides to spreadsheet-style data as opposed to a true database. Even if you use some of the built-in data validation tools in Excel, there's nothing about a given column that is actually defined on a deeper level that another program would understand.

All that said, with some scripting, I do think this might be doable, and not all that difficult. Suppose you had another sheet in your Excel file called "Field Metadata", in which the fields had settings defined for their alias, type, etc. in its own table. You could use python to convert that table to a list of lists, then submit that as a parameter on Add Fields (multiple). Here's the example from the docs:

import arcpy
arcpy.env.workspace = "C:/data/district.gdb"
arcpy.management.AddFields(
    'school', 
    [['school_name', 'TEXT', 'Name', 255, 'Hello world', ''], 
     ['street_number', 'LONG', 'Street Number', None, 35, 'StreetNumDomain'],
     ['year_start', 'DATE', 'Year Start', None, '2017-08-09 16:05:07', '']])

 

You'd need to create an empty feature class first, then use Add Fields. After that, you'd need to append the data from Excel → Feature Class rather than directly converting. But it's still using built-in tools, no custom functions or anything.

- Josh Carlson
Kendall County GIS
AlexKweya
New Contributor III

Thanks for that, Josh. I'll explore it and see how it works out.

0 Kudos
DanPatterson
MVP Esteemed Contributor

Create a template featureclass If there are certain fields that you will use all the time or create the code to produce one.  Leave Excel out of it, it isn't the vehicle for database construction or maintenance.  It has its limitations

Work with Microsoft Excel files in ArcGIS Pro—ArcGIS Pro | Documentation


... sort of retired...
AlexKweya
New Contributor III

I see. The issue however was mainly for when you have to use different fields as opposed to a certain type of repetitive fields which can be imported from an existing feature class.

0 Kudos
DanPatterson
MVP Esteemed Contributor

same advise... build fields in a geodatabase


... sort of retired...
0 Kudos
AlexKweya
New Contributor III

Finally got around to testing it and it works!

I however noticed that the integer fields couldn’t accept the Field Lengths I had set.

(I attached some screenshots—Concatenating fields to form the last part of the script in Excel, and the generated Fields in ArcGIS Pro.)

AtanasJuma
New Contributor

Am really looking for this solution.

0 Kudos