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

5185
9
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 Esteemed 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

9 Replies
jcarlson
MVP Esteemed 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.

JessicaJThompson
Occasional Contributor II

Hello All! 

I am also looking into this. Survey123 Connect builds in Excel. As you build a survey you are creating the fields, as well as selecting their lengths and their fieldtypes. It would be amazing if it could be imported in some way into Pro so that all of the configurations you set do not have to be transcribed. If a mistake is made and the field/type/length do not match in the survey and the feature class the survey will not work. 

ESRI wants you to publish the survey to the AGOL and  have a hosted feature service created there (so apparently taking an excel spreadsheet and converting it to a feature class can be done by ESRI?? At least the S123/AGOL folx have it down). We have our own federated server and depending on the data the fc and related tables/attachements are stored in a specified database.

Anyway I would love to know if this becomes available. I have been converting dozens of paper inspections/reviews for a variety of department into dynamic surveys. It would be significantly easier if this was an option in

alex_friant
Occasional Contributor II

There is an extension offered by Esri, the "Topographic Production Tools" toolbox, which allows you to do such things: https://pro.arcgis.com/en/pro-app/latest/tool-reference/topographic-production/generate-geodatabase-...