Importing Survey123 SQLite Database from Device
I built this tool to export Survey123 data from a SQLite database (downloaded off the device used to capture the data) to a file geodatabase. It creates one feature class for each type of survey in the SQLite database. The script requires that you run it from an ArcToolbox tool with 2 parameters: [0] the input .sqlite file, and 1 the output file geodatabase. Here is the code:
import csv, sqlite3, json, os, arcpy
arcpy.env.overwriteOutput = True
def readS123db(inDB):
conn = sqlite3.connect(inDB)
cur = conn.cursor()
#conn.text_factory = lambda x: x.decode("utf-16")
#Status indicates which 'box' teh Survey is in
#0 - Drafts
#1 - Outbox
#2 - Sent
#3 - Submission Error
#4 - Inbox
for row in cur.execute('SELECT name, feature, status, data, snippet from Surveys where status = 1 or status = 3 or status = 2'):
#arcpy.AddMessage(row)
dataField = json.loads(row[3])
dataFieldKeys = dataField.keys()
#arcpy.AddMessage(str(dataFieldKeys))
for key in dataField:
masterKey = key
#arcpy.AddMessage(str(masterKey))
fcPath = os.path.join(gdb, str(masterKey))
if arcpy.Exists(fcPath):
arcpy.AddMessage("\nDeleting prior datasets")
arcpy.Delete_management(fcPath) #Delete Existing feature classes with these survey names
if arcpy.Exists(fcPath + "_tbl"):
arcpy.Delete_management(fcPath + "_tbl")
rowNum = 0
alternateFcNameList = []
for row in cur.execute('SELECT name, feature, status, data, snippet from Surveys where status = 1 or status = 3 or status = 2'):
rowNum = rowNum + 1
dataField = json.loads(row[3])
snippetField = str(row[4])[:255]
dataFieldKeys = dataField.keys()
for masterKey in dataField:
#output = os.path.split(outFC)
arcpy.AddMessage("\n\nRow " + str(rowNum) +"\nSurvey: " + masterKey)
arcpy.AddMessage("Snippet: " + snippetField)
tier2keys = dataField[masterKey].keys()
#arcpy.AddMessage(str(tier2keys))
gpsField = None
for tier2key in tier2keys: #Get the spatial reference
#arcpy.AddWarning("TIER2KEY: " + tier2key)
if tier2key == "GPS":
#gpsField = tier2key
gpsKeys = dataField[masterKey][tier2key]
for gpsKey in gpsKeys:
arcpy.AddMessage("GPS Key: " + gpsKey)
if gpsKey == "spatialReference":
sr = dataField[masterKey][tier2key][gpsKey]["wkid"]
arcpy.AddMessage("Spatial Reference: WKID " + str(sr))
spatial_reference = arcpy.SpatialReference(sr)
gpsField = tier2key
elif tier2key == "site_point": #In one case the GPS field was mis-named as field_2
#gpsField = tier2key
gpsKeys = dataField[masterKey][tier2key]
if gpsKeys != None:
for gpsKey in gpsKeys:
arcpy.AddMessage("GPS Key: " + gpsKey)
if gpsKey == "spatialReference":
sr = dataField[masterKey][tier2key][gpsKey]["wkid"]
arcpy.AddMessage("Spatial Reference: WKID " + str(sr))
spatial_reference = arcpy.SpatialReference(sr)
gpsField = tier2key
arcpy.AddMessage("GPS Field: " + str(gpsField))
if gpsField != None and dataField[masterKey][gpsField]["x"] != None and dataField[masterKey][gpsField]["y"] != None and dataField[masterKey][gpsField]["z"] != None:
#Create feature class if necessary
fcPath = os.path.join(gdb, str(masterKey))
if not arcpy.Exists(fcPath):
arcpy.AddMessage("\nCreating output feature class")
try:
arcpy.CreateFeatureclass_management(gdb, str(masterKey), "POINT", "", "DISABLED", "ENABLED", spatial_reference)
except:
masterKeyString = str(masterKey)
alternateFcName = masterKeyString.translate(None, '!@#$-&*^+=`~?/;:[]{}.,<>') #remove characters not valid in FC name
fcPath = os.path.join(gdb, alternateFcName)
if not arcpy.Exists(os.path.join(gdb, alternateFcName)):
alternateFcNameList.append(alternateFcName)
arcpy.AddMessage(masterKey + " is not a valid feature class name.\nReplacing with " + alternateFcName + "\n")
arcpy.CreateFeatureclass_management(gdb, alternateFcName, "POINT", "", "DISABLED", "ENABLED",
spatial_reference)
#Get data per row
fieldList = []
rowValues = []
for tier2key in tier2keys:
if tier2key != "GPS":
if tier2key != "objectid" and tier2key != "ObjectId":
arcpy.AddMessage("Import Field: " + tier2key)
arcpy.AddField_management(fcPath, str(tier2key), "TEXT")
fieldList.append(tier2key)
rowValues.append(str(dataField[masterKey][tier2key]))
arcpy.AddField_management(fcPath, "Snippet", "TEXT") #Adds snippet field from original database
fieldList.extend(("Snippet", "SHAPE@X", "SHAPE@Y", "SHAPE@Z"))
xCoord = dataField[masterKey][gpsField]["x"]
yCoord = dataField[masterKey][gpsField]["y"]
zCoord = dataField[masterKey][gpsField]["z"]
rowValues.extend((snippetField, xCoord, yCoord, zCoord))
rowValuesTuple = tuple(rowValues) #Create tuple from list of row values
rowValues = [rowValuesTuple] #because insert cursor expects a list of tuples (one tuple per data row)
with arcpy.da.InsertCursor(fcPath, fieldList) as cursor:
for row in rowValues:
arcpy.AddMessage("\nAppending data from row " + str(rowNum) + ": \n" + str(row))
cursor.insertRow(row)
elif gpsField == None:
#break (only enable if you want to omit non-spatial tables)
# Create feature class if necessary
fcPath = os.path.join(gdb, str(masterKey) + "_tbl")
if not arcpy.Exists(fcPath):
arcpy.AddMessage("\nCreating output table")
try:
arcpy.CreateTable_management(gdb, str(masterKey) + "_tbl")
except:
masterKeyString = str(masterKey)
alternateFcName = masterKeyString.translate(None,
'!@#$-&*^+=`~?/;:[]{}.,<>') + "_tbl"# remove characters not valid in FC name
fcPath = os.path.join(gdb, alternateFcName)
if not arcpy.Exists(os.path.join(gdb, alternateFcName)):
alternateFcNameList.append(alternateFcName)
arcpy.AddMessage(
masterKey + " is not a valid table name.\nReplacing with " + alternateFcName + "\n")
arcpy.CreateTable_management(gdb, alternateFcName)
# Get data per row
fieldList = []
rowValues = []
for tier2key in tier2keys:
if tier2key != "GPS":
if tier2key != "objectid" and tier2key != "ObjectId" and tier2key != "OBJECTID":
arcpy.AddMessage("Import Field: " + tier2key)
arcpy.AddField_management(fcPath, str(tier2key), "TEXT")
fieldList.append(tier2key)
rowValues.append(str(dataField[masterKey][tier2key]))
rowValuesTuple = tuple(rowValues) # Create tuple from list of row values
rowValues = [rowValuesTuple] # because insert cursor expects a list of tuples (one tuple per data row)
with arcpy.da.InsertCursor(fcPath, fieldList) as cursor:
for row in rowValues:
arcpy.AddMessage("\nAppending data from row " + str(rowNum) + ": \n" + str(row))
cursor.insertRow(row)
if __name__ == '__main__':
inDB = arcpy.GetParameterAsText(0)
gdb = arcpy.GetParameterAsText(1)
surveys = readS123db(inDB)
arcpy.AddMessage("\nOh Joy!!!\nAll Surveys Have Been Exported!\n")
This is what the ArcMap tool pointing to the above script should look like:
