# SSURGO_MergeDatabasesbyMap.py
#
# Based directly upon SSURGO_MergeDatabases.py. Only change is parameter order.
#
# Purpose: allow batch importing of SSURGO data into a custom Template database
# The custom Template database will need to have the AutoExec macro removed and
# a BatchImport macro created so that it can be used to import multiple datasets.
#
# Naming convention of the SSURGO datasets must follow the NRCS Geospatial Standard
# where each survey has it's own folder 'soil_ne109' with spatial and tabular subfolders.
#
# 09-30-2013
# Beta version 10-31-2013
# 11-22-2013
# 01-08-2014
# 01-13-2014 Fixed bug in SetProgressorLabel. Need to better document the use of the output DB name
# for the tool parameter.
# 2014-09-27
# 2014-2016 Major rewrite. Removed MS Access Tabular Import and now use csv reader on text files
# 2014-10-18 Modified sdv table imports to only add unique values
# 2014-10-18 Modified SYSTEM table to only include cointerp records with ruledepth=0
#
## ===================================================================================
class MyError(Exception):
pass
## ===================================================================================
def errorMsg():
try:
tb = sys.exc_info()[2]
tbinfo = traceback.format_tb(tb)[0]
theMsg = tbinfo + " \n" + str(sys.exc_type)+ ": " + str(sys.exc_value) + " \n"
PrintMsg(theMsg, 2)
except:
PrintMsg("Unhandled error in errorMsg method", 2)
pass
## ===================================================================================
def PrintMsg(msg, severity=0):
# Adds tool message to the geoprocessor
#
#Split the message on \n first, so that if it's multiple lines, a GPMessage will be added for each line
try:
for string in msg.split('\n'):
#Add a geoprocessing message (in case this is run as a tool)
if severity == 0:
arcpy.AddMessage(string)
elif severity == 1:
arcpy.AddWarning(string)
elif severity == 2:
arcpy.AddError(" \n" + string)
except:
pass
## ===================================================================================
def Number_Format(num, places=0, bCommas=True):
try:
# Format a number according to locality and given places
#locale.setlocale(locale.LC_ALL, "")
if bCommas:
theNumber = locale.format("%.*f", (places, num), True)
else:
theNumber = locale.format("%.*f", (places, num), False)
return theNumber
except:
errorMsg()
#PrintMsg("Unhandled exception in Number_Format function (" + str(num) + ")", 2)
return "???"
## ===================================================================================
def GetTemplateDate(newDB, areaSym):
# Get SAVEREST date from previously existing Template database
# Use it to compare with the date from the WSS dataset
# If the existing database is same or newer, it will be kept and the WSS version skipped
try:
if not arcpy.Exists(newDB):
return 0
saCatalog = os.path.join(newDB, "SACATALOG")
dbDate = 0
if arcpy.Exists(saCatalog):
with arcpy.da.SearchCursor(saCatalog, ("SAVEREST"), "[AREASYMBOL] = '" + areaSym + "'") as srcCursor:
for rec in srcCursor:
dbDate = str(rec[0]).split(" ")[0]
del saCatalog
del newDB
return dbDate
else:
# unable to open SACATALOG table in existing dataset
# return 0 which will result in the existing dataset being overwritten by a new WSS download
return 0
except:
errorMsg()
return 0
## ===============================================================================================================
def GetTableInfo(newDB):
# Adolfo's function
#
# Retrieve physical and alias names from MDSTATTABS table and assigns them to a blank dictionary.
# Stores physical names (key) and aliases (value) in a Python dictionary i.e. {chasshto:'Horizon AASHTO,chaashto'}
# Fieldnames are Physical Name = AliasName,IEfilename
try:
tblInfo = dict()
# Open mdstattabs table containing information for other SSURGO tables
theMDTable = "mdstattabs"
env.workspace = newDB
# Establishes a cursor for searching through field rows. A search cursor can be used to retrieve rows.
# This method will return an enumeration object that will, in turn, hand out row objects
if arcpy.Exists(os.path.join(newDB, theMDTable)):
fldNames = ["tabphyname","tablabel","iefilename"]
with arcpy.da.SearchCursor(os.path.join(newDB, theMDTable), fldNames) as rows:
for row in rows:
# read each table record and assign 'tabphyname' and 'tablabel' to 2 variables
physicalName = row[0]
aliasName = row[1]
importFileName = row[2]
# i.e. {chaashto:'Horizon AASHTO',chaashto}; will create a one-to-many dictionary
# As long as the physical name doesn't exist in dict() add physical name
# as Key and alias as Value.
#if not physicalName in tblAliases:
if not importFileName in tblInfo:
#PrintMsg("\t" + importFileName + ": " + physicalName, 1)
tblInfo[importFileName] = physicalName, aliasName
del theMDTable
return tblInfo
else:
# The mdstattabs table was not found
raise MyError, "Missing mdstattabs table"
return tblInfo
except MyError, e:
# Example: raise MyError, "This is an error message"
PrintMsg(str(e), 2)
return False
except:
errorMsg()
return dict()
## ===================================================================================
def SSURGOVersion(newDB, tabularFolder):
# Get SSURGO version from the Template database "SYSTEM Template Database Information" table
#
# Ideally we want to compare with the value in version.txt with the version in
# the "SYSTEM - Template Database Information" table. If they are not the same
# the tabular import should be aborted. There are some more specifics about the
# SSURGO version.txt valu in one of the Import macros of the Template database.
# Need to follow up and research this more.
# At this time we are only checking the first 'digit' of the string value.
#
# Should be able to get this to work using wildcard for fields and then
# use the version.txt as an alternative or failover.
try:
# Valid SSURGO version for data model. Ensures
# compatibility between template database and SSURGO download.
versionTxt = os.path.join(tabularFolder, "version.txt")
if not arcpy.Exists(newDB):
raise MyError, "Missing input database (" + newDB + ")"
if arcpy.Exists(versionTxt):
# read just the first line of the version.txt file
fh = open(versionTxt, "r")
txtVersion = fh.readline().split(".")[0]
fh.close()
else:
# Unable to compare versions. Warn user but continue
PrintMsg("Unable to find file: version.txt", 1)
return True
systemInfo = os.path.join(newDB, "SYSTEM - Template Database Information")
if arcpy.Exists(systemInfo):
# Get SSURGO Version from template database
dbVersion = 0
with arcpy.da.SearchCursor(systemInfo, "*", "") as srcCursor:
for rec in srcCursor:
if rec[0] == "SSURGO Version":
dbVersion = str(rec[2]).split(".")[0]
#PrintMsg("\tSSURGO Version from DB: " + dbVersion, 1)
del systemInfo
del newDB
if txtVersion != dbVersion:
# SSURGO Versions do not match. Warn user but continue
PrintMsg("Discrepancy in SSURGO Version number for Template database and SSURGO download", 1)
else:
# Unable to open SYSTEM table in existing dataset
# Warn user but continue
PrintMsg("Unable to open 'SYSTEM - Template Database Information'", 1)
return True
except MyError, e:
# Example: raise MyError, "This is an error message"
PrintMsg(str(e), 2)
return False
except:
errorMsg()
return False
## ===================================================================================
def SortMapunits(newDB):
# Populate table 'SYSTEM - Mapunit Sort Specifications'. Required for Soil Data Viewer
#
# Populate table "SYSTEM - INTERP DEPTH SEQUENCE" from COINTERP using cointerpkey and seqnum
#
try:
# Make query table using MAPUNIT and LEGEND tables and use it to assemble all
# of the data elements required to create the "SYSTEM - Mapunit Sort Specification" table
inputTbls = ["legend", "mapunit"]
fldList = "legend.areasymbol areasymbol;legend.lkey lkey; mapunit.musym musym; mapunit.mukey mukey"
sqlJoin = "mapunit.lkey = legend.lkey"
queryTbl = "musorted"
# Cleanup
if arcpy.Exists(queryTbl):
arcpy.Delete_management(queryTbl)
# Find output SYSTEM table
sysFields = ["lseq", "museq", "lkey", "mukey"]
sysTbl = os.path.join(newDB, "SYSTEM - Mapunit Sort Specifications")
if not arcpy.Exists(sysTbl):
raise MyError, "Could not find " + sysTbl
# Clear the table
arcpy.TruncateTable_management(sysTbl)
arcpy.MakeQueryTable_management(inputTbls, queryTbl, "ADD_VIRTUAL_KEY_FIELD", "", fldList, sqlJoin)
# Open the query table, sorting on areasymbol
#sqlClause = [None, "order by legend_areasymbol asc"]
dMapunitSort = dict() # dictionary to contain list of musyms for each survey. Will be sorted
dMapunitData = dict() # dictionary for containing all neccessary data for SYSTEM -Map Unit Sort Specification
convert = lambda text: int(text) if text.isdigit() else text.lower()
alphanum_key = lambda key: [ convert(c) for c in re.split('([0-9]+)', key)]
with arcpy.da.SearchCursor(queryTbl, ["legend_areasymbol", "legend_lkey", "mapunit_musym", "mapunit_mukey"]) as cur:
for rec in cur:
areaSym = rec[0].encode('ascii')
lkey = rec[1].encode('ascii')
musym = rec[2].encode('ascii')
mukey = rec[3].encode('ascii')
# Append muysm values to dictionary by areasymbol key
if areaSym in dMapunitSort:
musymList = dMapunitSort[areaSym]
musymList.append(musym)
dMapunitSort[areaSym] = musymList
else:
dMapunitSort[areaSym] = [musym]
# store legend and map unit keys by areasymbol and map unit symbol
dMapunitData[(areaSym, musym)] = (lkey, mukey)
# Iterate through dMapunitSort dictionary, sorting muysm values
areaList = sorted(dMapunitSort.keys()) # sorted list of areasymbols
lseq = 0
mseq = 0
# Now read the dictionary back out in sorted order and populate the SYSTEM - Mapunit Sort Specifications table
#
with arcpy.da.InsertCursor(sysTbl, "*") as outCur:
for areaSym in areaList:
#PrintMsg(" \nProcessing survey: " + areaSym, 1)
lseq += 1
musymList = sorted(dMapunitSort[areaSym], key = alphanum_key)
for musym in musymList:
mseq += 1
mKey = (areaSym, musym)
lkey, mukey = dMapunitData[(areaSym, musym)]
outrec = lseq, mseq, lkey, mukey
outCur.insertRow(outrec)
# Populate "SYSTEM - INTERP DEPTH SEQUENCE" fields: cointerpkey and depthseq
# from COINTERP fields: cointerpkey and seqnum
# I am assuming that the cointerp table is already sorted. Is that safe??
#
#PrintMsg("\tUpdating SYSTEM - Interp Depth Sequence", 1)
inTbl = os.path.join(newDB, "cointerp")
inFlds = ["cointerpkey", "seqnum"]
outTbl = os.path.join(newDB, "SYSTEM - INTERP DEPTH SEQUENCE")
outFlds = ["cointerpkey", "depthseq"]
interpSQL = "ruledepth = 1"
with arcpy.da.SearchCursor(inTbl, inFlds, interpSQL) as sCur:
outCur = arcpy.da.InsertCursor(outTbl, outFlds)
for inRec in sCur:
outCur.insertRow(inRec)
del outCur
del inRec
return True
except MyError, e:
# Example: raise MyError, "This is an error message"
PrintMsg(str(e), 2)
return False
except:
errorMsg()
return False
## ===================================================================================
def ImportTabular(inputFolder, subFolder, newDB, areaSym, iCnt):
# This function is designed to import tabular data from the text files
# If the text files have been deleted this won't work!
try:
# move to tabular folder for the current SSURGO dataset
env.workspace = os.path.join(inputFolder, subFolder)
# import the tabular data from text files in the tabular folder
PrintMsg("\tImporting textfiles from tabular folder...", 0)
tabularFolder = os.path.join(env.workspace, "tabular")
# if the tabular directory is empty return False
if len(os.listdir(tabularFolder)) < 1:
raise MyError, "No text files found in the tabular folder"
# Make sure tha the tabular data (version.txt) matches the Access database version
if not SSURGOVersion(newDB, tabularFolder):
raise MyError, ""
# Create a dictionary with table information
tblInfo = GetTableInfo(newDB)
# Create a list of textfiles to be imported. The import process MUST follow the
# order in this list in order to maintain referential integrity. This list
# will need to be updated if the SSURGO data model is changed in the future.
# This list of tables and their schema is related to the SSURGO version.
txtFiles = ["distmd","legend","distimd","distlmd","lareao","ltext","mapunit", \
"comp","muaggatt","muareao","mucrpyd","mutext","chorizon","ccancov","ccrpyd", \
"cdfeat","cecoclas","ceplants","cerosnac","cfprod","cgeomord","chydcrit", \
"cinterp","cmonth", "cpmatgrp", "cpwndbrk","crstrcts","csfrags","ctxfmmin", \
"ctxmoicl","ctext","ctreestm","ctxfmoth","chaashto","chconsis","chdsuffx", \
"chfrags","chpores","chstrgrp","chtext","chtexgrp","chunifie","cfprodo","cpmat","csmoist", \
"cstemp","csmorgc","csmorhpp","csmormr","csmorss","chstr","chtextur", \
"chtexmod","sacatlog","sainterp","sdvalgorithm","sdvattribute","sdvfolder","sdvfolderattribute"]
# Need to add featdesc import as a separate item (ie. spatial\soilsf_t_al001.txt: featdesc)
# set progressor object which allows progress information to be passed for every merge complete
arcpy.SetProgressor("step", "Importing tabular data", 0, len(txtFiles) + 1, 1)
# Problem with length of some memo fields, need to allocate more memory
csv.field_size_limit(512000)
# Need to skip sdv tables if they already have data. Check to see.
sdvCnt = int(arcpy.GetCount_management(os.path.join(newDB, "sdvfolderattribute")).getOutput(0))
# Read existing sdvfolderattribute keys into a list
# Two fields: folderkey, attributekey
sdvTbl = os.path.join(newDB, "sdvfolderattribute")
fattKeys = list()
with arcpy.da.SearchCursor(sdvTbl, ("attributekey")) as sdvCur:
for rec in sdvCur:
fattKeys.append(rec[0])
# Read existing sdvattribute keys into a list
# Several fields: first one is attributekey
sdvTbl = os.path.join(newDB, "sdvattribute")
attKeys = list()
with arcpy.da.SearchCursor(sdvTbl, ("attributekey")) as sdvCur:
for rec in sdvCur:
attKeys.append(rec[0])
# Read existing sdvfolder keys into a list
# Six fields: foldersequence, foldenname, folderdescription, folderkey, parentfolderkey, wlupdated
# Fourth field is key
sdvTbl = os.path.join(newDB, "sdvfolder")
fKeys = list()
with arcpy.da.SearchCursor(sdvTbl, ("folderkey")) as sdvCur:
for rec in sdvCur:
fKeys.append(rec[0])
# Read existing sdvalgorithm keys into a list
#
# Several fields: treating first field algorithmsequence as key
sdvTbl = os.path.join(newDB, "sdvalgorithm")
alKeys = list()
with arcpy.da.SearchCursor(sdvTbl, ("algorithmsequence")) as sdvCur:
for rec in sdvCur:
alKeys.append(rec[0])
# End of sdv keys
for txtFile in txtFiles:
# Get table name and alias from dictionary
if txtFile in tblInfo:
# Get the table name from the dictionary
tbl, aliasName = tblInfo[txtFile]
else:
raise MyError, "Textfile reference '" + txtFile + "' not found in 'mdstattabs table'"
arcpy.SetProgressorLabel("Importing " + tbl + "...")
# Full path to SSURGO text file
txtPath = os.path.join(tabularFolder, txtFile + ".txt")
# Process existing table
if arcpy.Exists(tbl):
with arcpy.da.InsertCursor(os.path.join(newDB, tbl), "*") as cursor:
# Add new records for sdvfolderattribute tabl
if tbl == "sdvfolderattribute":
# Use csv reader to read each line in the sdv text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
fattKey = int(row[1])
if not fattKey in fattKeys:
newRow = [None if value == '' else value for value in row]
cursor.insertRow(newRow)
# Add new records for sdvattribute table
elif tbl == "sdvattribute":
# Use csv reader to read each line in the sdv text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
aKey = int(row[0])
if not aKey in attKeys:
newRow = [None if value == '' else value for value in row]
cursor.insertRow(newRow)
# Add new records for sdvfolder
elif tbl == "sdvfolder":
# Use csv reader to read each line in the sdv text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
fKey = int(row[3])
if not fKey in fKeys:
newRow = [None if value == '' else value for value in row]
cursor.insertRow(newRow)
# Add new records for sdvalgorithm
elif tbl == "sdvalgorithm":
# Use csv reader to read each line in the sdv text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
alKey = int(row[0])
if not alKey in alKeys:
cursor.insertRow(row)
else:
# Process non-sdv tables. These should all be unique records. If
# somehow the record is not unique and exception will be thrown.
try:
# Use csv reader to read each line in the text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
# replace all blank values with 'None' so that the values are properly inserted
# into integer values otherwise insertRow fails
newRow = [None if value == '' else value for value in row]
cursor.insertRow(newRow)
except:
PrintMsg("\t" + tbl + ": error reading line for " + txtFile + ".txt", 1)
else:
raise MyError, "Required table '" + tbl + "' not found in " + newDB
arcpy.SetProgressorPosition()
# Import feature description file located in spatial folder
#
# ex. soilsf_t_al001.txt
spatialFolder = os.path.join(os.path.dirname(tabularFolder), "spatial")
txtFile ="soilsf_t_" + areaSym
txtPath = os.path.join(spatialFolder, txtFile + ".txt")
tbl = "featdesc"
if arcpy.Exists(txtPath):
# Create cursor for all fields to populate the featdesc table
with arcpy.da.InsertCursor(tbl, "*") as cursor:
arcpy.SetProgressorLabel(tbl + "...")
try:
# Use csv reader to read each line in the text file
for rowInFile in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
# replace all blank values with 'None' so that the values are properly inserted
# into integer values otherwise insertRow fails
newRow = [None if value == '' else value for value in rowInFile]
cursor.insertRow(newRow)
except:
errorMsg()
raise MyError, "Error loading " + txtFile + ".txt"
arcpy.SetProgressorPosition() # for featdesc table
# Check the database to make sure that it completed properly, with at least the
# SAVEREST date populated in the SACATALOG table. Added this primarily to halt
# processing when the user forgets to set the Trusted Location in MS Access.
dbDate = GetTemplateDate(newDB, areaSym)
if dbDate == 0:
# With this error, it would be best to bailout and fix the problem before proceeding
raise MyError, "Failed to import tabular data"
else:
# Compact database (~30% reduction in mdb filesize)
try:
arcpy.SetProgressorLabel("Compacting database ...")
sleep(1)
arcpy.Compact_management(newDB)
PrintMsg("\tCompacted database", 0)
except:
# Sometimes ArcGIS is unable to compact (locked database?)
# Usually restarting the ArcGIS application fixes this problem
PrintMsg("\tUnable to compact database", 1)
# Set the Progressor to show completed status
arcpy.ResetProgressor()
arcpy.SetProgressorLabel("Tabular import complete")
return True
except MyError, e:
# Example: raise MyError, "This is an error message"
PrintMsg(str(e), 2)
return False
except:
errorMsg()
return False
## ===================================================================================
def ImportTabular2(inputFolder, subFolder, newDB, areaSym, iCnt):
# This function is designed to import tabular data from the Access databases
# Text files are not used.
try:
# move to tabular folder for the current SSURGO dataset
env.workspace = os.path.join(inputFolder, subFolder)
# get Access database name and path. Assuming that it is located in 'tabular' folder.
dbPath = os.path.join( inputFolder, os.path.join( subFolder, "tabular"))
dbName = "soil_d_" + areaSym + ".mdb"
dbFile = os.path.join(dbPath, dbName)
if not arcpy.Exists(dbFile):
raise MyError, "Missing database " + dbFile
# import the tabular data from text files in the tabular folder
PrintMsg("\tImporting tables from database...", 0)
tabularFolder = os.path.join(env.workspace, "tabular")
# Make sure tha the tabular data (version.txt) matches the Access database version
if not SSURGOVersion(newDB, tabularFolder):
raise MyError, ""
# Create a dictionary with table information
tblInfo = GetTableInfo(newDB)
# Create a list of textfiles to be imported. The import process MUST follow the
# order in this list in order to maintain referential integrity. This list
# will need to be updated if the SSURGO data model is changed in the future.
# This list of tables and their schema is related to the SSURGO version.
txtFiles = ["distmd","legend","distimd","distlmd","lareao","ltext","mapunit", \
"comp","muaggatt","muareao","mucrpyd","mutext","chorizon","ccancov","ccrpyd", \
"cdfeat","cecoclas","ceplants","cerosnac","cfprod","cgeomord","chydcrit", \
"cinterp","cmonth", "cpmatgrp", "cpwndbrk","crstrcts","csfrags","ctxfmmin", \
"ctxmoicl","ctext","ctreestm","ctxfmoth","chaashto","chconsis","chdsuffx", \
"chfrags","chpores","chstrgrp","chtext","chtexgrp","chunifie","cfprodo","cpmat","csmoist", \
"cstemp","csmorgc","csmorhpp","csmormr","csmorss","chstr","chtextur", \
"chtexmod","sacatlog","sainterp","sdvalgorithm","sdvattribute","sdvfolder","sdvfolderattribute"]
# Need to add featdesc import as a separate item (ie. spatial\soilsf_t_al001.txt: featdesc)
# set progressor object which allows progress information to be passed for every merge complete
arcpy.SetProgressor("step", "Importing tabular data", 0, len(txtFiles) + 1, 1)
# Problem with length of some memo fields, need to allocate more memory
csv.field_size_limit(512000)
# Need to skip sdv tables if they already have data. Check to see.
sdvCnt = int(arcpy.GetCount_management(os.path.join(newDB, "sdvfolderattribute")).getOutput(0))
# Read existing sdvfolderattribute keys into a list
# Two fields: folderkey, attributekey
sdvTbl = os.path.join(newDB, "sdvfolderattribute")
fattKeys = list()
with arcpy.da.SearchCursor(sdvTbl, ("attributekey")) as sdvCur:
for rec in sdvCur:
fattKeys.append(rec[0])
# Read existing sdvattribute keys into a list
# Several fields: first one is attributekey
sdvTbl = os.path.join(newDB, "sdvattribute")
attKeys = list()
with arcpy.da.SearchCursor(sdvTbl, ("attributekey")) as sdvCur:
for rec in sdvCur:
attKeys.append(rec[0])
# Read existing sdvfolder keys into a list
# Six fields: foldersequence, foldenname, folderdescription, folderkey, parentfolderkey, wlupdated
# Fourth field is key
sdvTbl = os.path.join(newDB, "sdvfolder")
fKeys = list()
with arcpy.da.SearchCursor(sdvTbl, ("folderkey")) as sdvCur:
for rec in sdvCur:
fKeys.append(rec[0])
# Read existing sdvalgorithm keys into a list
#
# Several fields: treating first field algorithmsequence as key
sdvTbl = os.path.join(newDB, "sdvalgorithm")
alKeys = list()
with arcpy.da.SearchCursor(sdvTbl, ("algorithmsequence")) as sdvCur:
for rec in sdvCur:
alKeys.append(rec[0])
# End of sdv keys
for txtFile in txtFiles:
# Get table name and alias from dictionary
if txtFile in tblInfo:
# Get the table name from the dictionary
tbl, aliasName = tblInfo[txtFile]
else:
raise MyError, "Textfile reference '" + txtFile + "' not found in 'mdstattabs table'"
arcpy.SetProgressorLabel("Importing " + tbl + "...")
# Full path to SSURGO text file
txtPath = os.path.join(tabularFolder, txtFile + ".txt")
# Process existing table
if arcpy.Exists(tbl):
with arcpy.da.InsertCursor(os.path.join(newDB, tbl), "*") as cursor:
# Add new records for sdvfolderattribute tabl
if tbl == "sdvfolderattribute":
# Use csv reader to read each line in the sdv text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
fattKey = int(row[1])
if not fattKey in fattKeys:
newRow = [None if value == '' else value for value in row]
cursor.insertRow(newRow)
# Add new records for sdvattribute table
elif tbl == "sdvattribute":
# Use csv reader to read each line in the sdv text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
aKey = int(row[0])
if not aKey in attKeys:
newRow = [None if value == '' else value for value in row]
cursor.insertRow(newRow)
# Add new records for sdvfolder
elif tbl == "sdvfolder":
# Use csv reader to read each line in the sdv text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
fKey = int(row[3])
if not fKey in fKeys:
newRow = [None if value == '' else value for value in row]
cursor.insertRow(newRow)
# Add new records for sdvalgorithm
elif tbl == "sdvalgorithm":
# Use csv reader to read each line in the sdv text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
alKey = int(row[0])
if not alKey in alKeys:
cursor.insertRow(row)
else:
# Process non-sdv tables. These should all be unique records. If
# somehow the record is not unique and exception will be thrown.
try:
# Use csv reader to read each line in the text file
for row in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
# replace all blank values with 'None' so that the values are properly inserted
# into integer values otherwise insertRow fails
newRow = [None if value == '' else value for value in row]
cursor.insertRow(newRow)
except:
PrintMsg("\t" + tbl + ": error reading line for " + txtFile + ".txt", 1)
else:
raise MyError, "Required table '" + tbl + "' not found in " + newDB
arcpy.SetProgressorPosition()
# Import feature description file located in spatial folder
#
# ex. soilsf_t_al001.txt
spatialFolder = os.path.join(os.path.dirname(tabularFolder), "spatial")
txtFile ="soilsf_t_" + areaSym
txtPath = os.path.join(spatialFolder, txtFile + ".txt")
tbl = "featdesc"
if arcpy.Exists(txtPath):
# Create cursor for all fields to populate the featdesc table
with arcpy.da.InsertCursor(tbl, "*") as cursor:
arcpy.SetProgressorLabel(tbl + "...")
try:
# Use csv reader to read each line in the text file
for rowInFile in csv.reader(open(txtPath, 'rb'), delimiter='|', quotechar='"'):
# replace all blank values with 'None' so that the values are properly inserted
# into integer values otherwise insertRow fails
newRow = [None if value == '' else value for value in rowInFile]
cursor.insertRow(newRow)
except:
errorMsg()
raise MyError, "Error loading " + txtFile + ".txt"
arcpy.SetProgressorPosition() # for featdesc table
# Check the database to make sure that it completed properly, with at least the
# SAVEREST date populated in the SACATALOG table. Added this primarily to halt
# processing when the user forgets to set the Trusted Location in MS Access.
dbDate = GetTemplateDate(newDB, areaSym)
if dbDate == 0:
# With this error, it would be best to bailout and fix the problem before proceeding
raise MyError, "Failed to import tabular data"
else:
# Compact database (~30% reduction in mdb filesize)
try:
arcpy.SetProgressorLabel("Compacting database ...")
sleep(1)
arcpy.Compact_management(newDB)
PrintMsg("\tCompacted database", 0)
except:
# Sometimes ArcGIS is unable to compact (locked database?)
# Usually restarting the ArcGIS application fixes this problem
PrintMsg("\tUnable to compact database", 1)
# Set the Progressor to show completed status
arcpy.ResetProgressor()
arcpy.SetProgressorLabel("Tabular import complete")
return True
except MyError, e:
# Example: raise MyError, "This is an error message"
PrintMsg(str(e), 2)
return False
except:
errorMsg()
return False
## ===================================================================================
# Import system modules
import arcpy, sys, string, os, traceback, locale, tempfile, time, shutil, subprocess, csv
# Create the Geoprocessor object
from arcpy import env
from time import sleep
#from _winreg import *
try:
inputFolder = arcpy.GetParameterAsText(0) # location of SSURGO datasets containing tabular folders
tabList = arcpy.GetParameter(2) # list of SSURGO folder names to be proccessed
inputDB = arcpy.GetParameterAsText(3) # custom Template SSURGO database (check version date?)
newDB = arcpy.GetParameterAsText(4) # new name for final ouput Template database
bImportTxt = arcpy.GetParameter(4) # boolean. If true, import textfiles. if false, import from Access db.
if newDB == inputDB:
raise MyError, "Place input Template database in a different folder"
if arcpy.Exists(newDB):
raise MyError, "Template database already exists in the output folder"
# copy archive version of custom Template DB to a local copy in the input folder
PrintMsg(" \nCreating new database...", 0)
shutil.copy2(inputDB, newDB)
#lastDB = newDB
iCnt = 0
# Keep track of any surveys that are skipped
skippedList = list()
# process each selected soil survey
arcpy.SetProgressor("default", "Merging SSURGO databases...")
PrintMsg(" \nMerging " + str(len(tabList)) + " soil survey datasets", 0)
for subFolder in tabList:
iCnt += 1
if not subFolder.encode('ascii').startswith("soil_"):
raise MyError, subFolder + " is not a valid SSURGO folder name"
# assume that last 5 characters represent the areasymbol
areaSym = subFolder[-5:]
# Need to make sure that this survey does not already exist in the new database
if GetTemplateDate(newDB, areaSym) == 0:
# OK. this survey does not yet exist in the new database
#arcpy.SetProgressorPosition(iCnt)
PrintMsg(" \nProcessing " + subFolder + "...", 0)
arcpy.SetProgressorLabel("Processing " + subFolder + " (" + str(iCnt) + " of " + str(len(tabList)) + ")")
time.sleep(1)
#bProcessed, lastDB = ImportFolder(msAccess, lastDB, inputFolder, subFolder)
bProcessed = ImportTabular(inputFolder, subFolder, newDB, areaSym, iCnt)
# cancel entire process if an import fails
if bProcessed == False:
raise MyError, ""
#time.sleep(15)
else:
PrintMsg(" \nSkipping survey " + areaSym + " because it already exists in the new database", 1)
skippedList.append(areaSym)
# move database from last folder processed back to original location
# Some how I need to put that SYSTEM table sort at the end
#
# Sort map units for Soil Data Viewer SYSTEM table
arcpy.SetProgressorLabel("Sorting SYSTEM tables...")
bSorted = SortMapunits(newDB)
if len(skippedList) > 0:
PrintMsg(" \nThe following surveys already existed in the new database: " + ", ".join(skippedList), 1)
PrintMsg(" \nCompleted database merge to " + newDB + " \n ", 0)
except MyError, e:
PrintMsg(str(e), 2)
except:
errorMsg()
