Importing domains from Excel

7244
12
07-10-2017 08:04 AM
MagnusPersson
New Contributor

Hi,

I am building a geodatabase which is supposed to contain A LOT of domains to be used in ArcGIS online and Collector.

Soo, i want to import all domains, 30-40 of them, to ArcMap instead of typing them in which would take forever.

Do you guys have an indea of how to do that?

12 Replies
PanGIS
by
Regular Contributor
RebeccaStrauch__GISP
MVP Esteemed Contributor

I use the table-to-domain command that pan_gis  mentions, in a python script frequently (monthly or more) to create or update my ~90 domains.  Although Excel will work and is easier sometimes to edit/modify on the user end, if you will need to be modifying the domains often, I highly recommend converting you Excel files and maintaining in a file GDB.

DanaNolan
Regular Contributor

Agreed; I have a file gdb called something like domainHelp, but I add/update some domains to this with Domain to table tool, and never use my Excel tables after bringing in domains. Although, occasionally we export domains to Excel tables so users can fill in attribute data with the domain as an Excel dropdown/validation list. Having domains in a database makes it easier to view, report, compare, and join to domains.

DanaNolan
Regular Contributor

Some tips from someone who has done a lot of this for SDSFIE. Make the code and description fields have the same names for all the tables; then you can use Geoprocessing Results to re-do each import with minimal changes. In Excel, you can make each domain a named range, with the name you will be using for the domain. This will make viewing and adding domains easier. 

When you have the domains in, don't export feature classes into the database; import or copy/paste may avoid the domain getting a "_1" appended to the end of the domain, which creates 2 duplicates in your database. If the fields in the feature classes don't have domains assigned to them yet, this should not be a problem. Also, if you are using user names to access your database, choose a generic owner of the domains so they don't end up belonging to someone who leaves.

RebeccaStrauch__GISP
MVP Esteemed Contributor

Nice tips. 

Additional tip re: the _1.  If you are archiving a copy into a different GDB where the domains differ, pay attention to the popup that show which domains will get the _1 added....then exit before the copy and change rename the current domains in the archive gdb first. 

For example, when archiving, it says domain1 will be domain1_1, domain2 will be domain2_1 for those domains that changed.  So I go into the archive gdb and rename the domains to domain1_2016, domain2_2017. (an archive before the new changes).  This will adjust all the FCs that use those domains.  Then the copy will not add the _1.  Yes, you will have more domains but you also have a record of modifications thru the years, if necessary.

I hope that makes sense. 

RandyBurton
MVP Regular Contributor

When I create domains, I start with an Excel workbook with one domain per tab.  I then run a python script to create a table in a file geodatabase for each domain and import the data from the spreadsheet.  A second python script converts the table to a domain. I will use a third script, similar to the first, to create my feature layers.

To create the tables:

# Description:  Add fields and datato a table

import xlrd
import arcpy
from arcpy import env

# name of geodatabase
geoDB = r"C:\Path\To\filedb.gdb"

# name of excel workbook
excelWB = r"C:\Path\To\workbook.xlsx"

# set environment settings
env.workspace = geoDB

# work happens here
def new_table(dbTable, dbFields, geoDB, excelWB):

    # create the table
    print "\nCreating table: " + dbTable

    # set local variables
    template = ""
    config_keyword = ""

    # Execute CreateTable
    arcpy.CreateTable_management(geoDB, dbTable, template, config_keyword)

    # add the fields
    print "Adding fields: "

    for new_field in dbFields:
        # add new field
        print "\t" + new_field[0]
        # all fields using domains are non-nullable
        if (new_field[4] == "#"):
            nullable = "NULLABLE"
        else:
            nullable = "NON_NULLABLE"
        # AddField_management (in_table, field_name, field_type, {field_precision}, {field_scale},
        #           {field_length}, {field_alias}, {field_is_nullable}, {field_is_required}, {field_domain})
        arcpy.AddField_management(dbTable,new_field[0],new_field[1],"#","#",
                                  new_field[2],new_field[3],nullable,"NON_REQUIRED",new_field[4])

    # read Excel workbook
    print "Adding data from Excel"
    workbook = xlrd.open_workbook(excelWB)
    worksheet = workbook.sheet_by_name(dbTable)
    num_rows = worksheet.nrows - 1

    fields = [t.encode('utf8') for t in worksheet.row_values(0)]

    # Open an InsertCursor
    # Since workspace defined as geoDB, should only need table name
    cursor = arcpy.da.InsertCursor(dbTable, fields)

    curr_row = 0
    while curr_row < num_rows:
        curr_row += 1
        # use standard encoding, not Unicode
        if (dbTable == "Domain2" ) :
            # For issues with null fields in Excel, this seems to work
            encoded = worksheet.row_values(curr_row)
        else :
            encoded = [t.encode('utf8') for t in worksheet.row_values(curr_row)]
        cursor.insertRow(encoded)

    # Delete cursor object
    del cursor


if __name__ == "__main__":
    # fields: [ 0:Name, 1:Type, 2:Size, 3:Alias, 4:Domain ]  use "#" for blanks

    # Use this for a simple domain
    dbTable = "Domain1"
    dbFields = [
     ["CODE", "TEXT",  "1", "CODE", "#"],
     ["DESCRIPTION", "TEXT",  "4", "DESCRIPTION", "#"]
        ]
    new_table(dbTable,dbFields,geoDB,excelWB)

    # Use this format to add other fields to table
    dbTable = "Domain2"
    dbFields = [
     ["CODE", "TEXT",  "4", "CODE", "#"],
     ["DESCRIPTION", "TEXT",  "8", "DESCRIPTION", "#"],
     ["MyInt", "SMALLINTEGER",  "#", "My Int", "#"],
     ["MyDate", "DATE",  "#", "My Date", "#"],
     ["MyLong", "INTEGER",  "#", "My Long", "#"],
        ]
    new_table(dbTable,dbFields,geoDB,excelWB)

    # ISSUES with MyInt and MyDate fields being null in Excel
    #   [u'?', u'?', u'', u'', '', u'', '']
    #   [u'?', u'?', u'', u'', 0.0, u'', 36892.0]

    print
    print "Processing complete."‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

And to convert the tables to domains:

# Description: Convert tables to domains

import arcpy
from arcpy import env

# Set the current workspace
geoDB = r"C:\Path\To\filedb.gdb"

env.workspace = geoDB

domains = [ 'Domain1',
            'Domain2' ]

# ONLY IF ALL TABLES ARE DOMAINS
# domains = arcpy.ListTables()

print "Processing Domains:"

for domain in domains:
    print "\t" + domain
    # create domain
    # TableToDomain_management (in_table, code_field, description_field, in_workspace,
    #                           domain_name, {domain_description}, {update_option REPLACE/APPEND})
    arcpy.TableToDomain_management(domain,"CODE","DESCRIPTION",geoDB,domain,domain,"REPLACE")
    # resort domain list by CODE not DESCRIPTION
    # SortCodedValueDomain_management (in_workspace, domain_name, sort_by, sort_order)
    arcpy.SortCodedValueDomain_management(geoDB,domain,"CODE","ASCENDING")

print "Done."‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Just another way among many, I suppose.

HannahHutchins1
New Contributor II

Hi Randy,

I came across your two scripts here and was wondering if you could help me out a bit. I'm fairly new to working with python and am having a hard time understanding exactly what you have here. 

I understand that I need to put in my own geoDB and excelWB, but do I need to name the individual sheets in the workbook inside the script somewhere?

RandyBurton
MVP Regular Contributor

These scripts are a couple from a series that I use to create features /tables and maintain a geodatabase. I like MySQL’s SHOW CREATE TABLE and wanted to do a similar workflow with arcpy.  The scripts above use arcpy’s  CreateTable and TableToDomain tools with the xlrd module to create and populate a domain.

I use a list of lists for my field definitions which makes it easy to change field types, aliases, etc. I maintain an Excel workbook for my domains.  By naming the worksheet tabs with the domain name, additional data can be stored in the workbook and not cause problems with the process.

The worksheets for domains should have at least two columns (CODE and DESCRIPTION), but other related columns could be added. If the domain is being used to track employee activities, the domain code can link to the employee table and provide access to related information.  I’ve attached a sample workbook.

The first script uses the field name lists to create a table. Then it reads the workbook tab with the domain name and populates the table.

The second script reads specific tables (named in a list) and converts them into a coded value domain.

Hope this helps.

HannahHutchins1
New Contributor II

Hey Randy,

Thank you for getting back to me!

Your descriptions help but unfortunately due to my lack in Python skills, I can't seem to produce anything. All I did to your first script was change the geoDB and excelWB environments to my working documents. 

When I run your first script, I get this error (see graphic below) and all it produces is one table in my geodatabase called "Domain1", which isn't correct.

I know I'm missing something - I'm guessing I have to change something more in your script but I can't figure out where.

I have my Excel document with domains attached.

Any insight would be helpful!

Thanks

0 Kudos