topic Importing domains from Excel in Data Management Questions

Importing domains from Excel

MagnusPersson — Mon, 10 Jul 2017 15:04:53 GMT

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?

Re: Importing domains from Excel

PanGIS — Mon, 10 Jul 2017 15:08:27 GMT

Hi,

have a look at this link

http://desktop.arcgis.com/en/arcmap/latest/tools/data-management-toolbox/table-to-domain.htm

Re: Importing domains from Excel

RebeccaStrauch__GISP — Mon, 10 Jul 2017 15:43:00 GMT

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.

Re: Importing domains from Excel

DanaNolan — Mon, 10 Jul 2017 15:48:37 GMT

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.

Re: Importing domains from Excel

RebeccaStrauch__GISP — Mon, 10 Jul 2017 15:58:29 GMT

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.

Re: Importing domains from Excel

DanaNolan — Mon, 10 Jul 2017 17:03:45 GMT

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.

Re: Importing domains from Excel

RandyBurton — Sat, 11 Dec 2021 17:41:49 GMT

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.

Re: Importing domains from Excel

HannahHutchins1 — Thu, 07 Mar 2019 15:04:45 GMT

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?

Re: Importing domains from Excel

RandyBurton — Sun, 10 Mar 2019 04:13:17 GMT

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.

Re: Importing domains from Excel

HannahHutchins1 — Tue, 12 Mar 2019 14:45:16 GMT

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

Re: Importing domains from Excel

RandyBurton — Sat, 11 Dec 2021 17:41:51 GMT

You would need to change the dbTable name to one of your tab names:

# line 76 and 84 from above -- change from
    dbTable = "Domain1"
# to
    dbTable = "AccessSts" # a tab name in your workbook that you want to become a table‍‍‍‍‍‍‍‍

The section of the code above after line 72 sets up the values used in the new_table function. In the example code, it creates 2 tables using 2 tabs in an Excel file.

Another way to read lots of tabs into domains without creating tables:

import xlrd
import arcpy
from arcpy import env

# name of geodatabase
geoDB = r"C:\Users\Randy\Documents\ArcGIS\PythonScripts\domains\domain_test.gdb"

# name of excel workbook
excelWB = r"C:\Users\Randy\Documents\ArcGIS\PythonScripts\domains\Parks_Domains.xlsx"

# lots of sheets in excel workbook, let's loop through them
xls = xlrd.open_workbook(excelWB, on_demand=True)
tabs = xls.sheet_names() # <- remeber: xlrd sheet_names is a function, not a property

isNum = ['ZipCode'] # list of tabs whose domain code is a number (not a number as text)

for tab in tabs:
    print "Processing tab: {}".format(tab)
    if tab in isNum: # if tab name is in number list, set type to long integer (or "SHORT")
        fldType = "LONG"
    else: # otherwise, set it to text
        fldType = "TEXT"

    print "\tCreating domain"
    arcpy.CreateDomain_management(in_workspace= geoDB, # geodatabase
                              domain_name= tab, # tab name will be the domain name
                              domain_description= tab, # tab name will be the description
                              field_type= fldType, # set field type
                              domain_type= "CODED", # this is a coded value domain
                              split_policy= "DEFAULT",
                              merge_policy= "DEFAULT")

    print "\tAdding codes and descriptions:"
    sheet = xls.sheet_by_name(tab)
    num_rows = sheet.nrows - 1
    
    curr_row = 0
    while curr_row < num_rows:
        curr_row += 1
        cd, desc = sheet.row_values(curr_row)
        if fldType == "LONG": # if field type is long integer
            cd = int(cd) # convert code to integer
        print "\t\t{}\t{}".format(cd, desc)
        arcpy.AddCodedValueToDomain_management(in_workspace= geoDB,
                                       domain_name= tab, # tab name is the domain name
                                       code= cd,
                                       code_description= desc)

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

This uses a couple of other arcpy functions: Create Domain and Add Coded Value To Domain. The basic flow of the program is to add a domain for every tab in the workbook. It assumes a setup similar to the one in your Excel file.

Hope this helps.

Re: Importing domains from Excel

AndresCastillo — Sat, 11 Dec 2021 17:41:54 GMT

"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. "

Hi Randy, I don't understand why someone would add more than just code and description fields to the excel, when the other columns are not used in the code, such as:

arcpy.CreateTable_management
arcpy.AddField_management

arcpy.TableToDomain_management
arcpy.SortCodedValueDomain_management

arcpy.CreateDomain_management
arcpy.AddCodedValueToDomain_management

How can the domain code link to the employees table and provide access to related information?

A join?

By the way, beautiful code.

Re: Importing domains from Excel

RandyBurton — Fri, 13 Dec 2019 18:33:20 GMT

The basic idea is that a join could be made on the employee domain code in a feature layer to the employee code in an employee table. For example, a study to evaluate access to tobacco products by underage youth could use something similar to an employee table. The feature point layer would have known tobacco vendors and contain a domain for an underage youth and a yes-no domain for a successful tobacco purchase. The table for the youth would also contain age, gender and other relevant information. An analysis could be done to answer a question like where are underage girls more likely to access tobacco products and if the attempts were sufficiently gender neutral. For this, you would need somewhere to store the information about the youth; a table in the database is an option. Then a domain can be created from this table. But the project could also be organized in many other ways. This is just one of them.

Re: Importing domains from Excel

BugPie — Fri, 29 Jul 2022 13:04:48 GMT

I've found the table to domain tool very useful!! saved me days of mis-typing all these entries.

For the Pro users: https://pro.arcgis.com/en/pro-app/latest/tool-reference/data-management/table-to-domain.htm