raoulc

QA QC excel  log for SDE Layers

Discussion created by raoulc on Jun 13, 2014
I am still very new to python and very much learning on the fly. I am trying to create and email a excel spread sheet of various layers in a SDE db. I am doing this because we have multiple users editing and creating new layers and to QA QC all of, we can sort by certain fields and time stamps. I have been able to create a log for one particular feature data set but would like to ultimately have this file monitor about 17 more files within this SDE db system.

The code is below. in this instance the fields of interest are:
sde_master.GIS_USER.AccessPoints.OBJECTID #;
sde_master.GIS_USER.AccessPoints.CREATEDBY #;
sde_master.GIS_USER.AccessPoints.CREATEDDATE #;
sde_master.GIS_USER.AccessPoints.EDITEDBY #;
sde_master.GIS_USER.AccessPoints.EDITEDDATE

How would I modify this script below to track and monitor certain fields(not necessarily the ones above) and have those results emailed to me in a condensed spread sheet. A multi page excel work book with each tab representing a various SDE layer would be optimal. Thank you in advance:

Raoul


# Import arcpy module
import arcpy
import time

# Set the Date
Date = time.strftime("%m-%d-%Y", time.localtime())

# Set the time
Time = time.strftime("%I:%M:%S %p", time.localtime())

# Set Environment Variables
arcpy.env.overwriteOutput = True

print "Process started at " + str(Date) + " " + str(Time) + "." + "\n" #Log File Start Time

# Set up the log file.
LogFile = file('C:\\Temp\\Esri_anon' + '.txt', 'w') #Creates a log file with todays date.
output = open('C:\\Temp\\EsrianonLog' + '.txt', 'w') #Path to log file.
output.write(str("Process started at " + str(Date) + " " + str(Time) + "." + "\n")) # Write the start time to the log file.

# Local variables:
Input_Tables = "'Database Connections\\gis_ims@sde_master.sde\\sde_master.GIS_USER.CommunicationNetwork\\sde_master.GIS_USER.AccessPoints'"
QueryTable = "QueryTable"
QueryTable_View = "QueryTable_View"
new_xls = "E:\\GIS\\RJC\\GeoProccessing\\20140612\\new.xls"

# Process: Make Query Table
arcpy.MakeQueryTable_management(Input_Tables, QueryTable, "USE_KEY_FIELDS", "", "sde_master.GIS_USER.AccessPoints.OBJECTID #;sde_master.GIS_USER.AccessPoints.CREATEDBY #;sde_master.GIS_USER.AccessPoints.CREATEDDATE #;sde_master.GIS_USER.AccessPoints.EDITEDBY #;sde_master.GIS_USER.AccessPoints.EDITEDDATE #", "EDITEDBY in( 'Esri_Anonymous') AND EDITEDDATE >= GetDate()-14")

# Process: Make Table View
arcpy.MakeTableView_management(QueryTable, QueryTable_View, "", "", "sde_master.GIS_USER.AccessPoints.OBJECTID sde_master.GIS_USER.AccessPoints.OBJECTID VISIBLE NONE;sde_master.GIS_USER.AccessPoints.CREATEDBY sde_master.GIS_USER.AccessPoints.CREATEDBY VISIBLE NONE;sde_master.GIS_USER.AccessPoints.CREATEDDATE sde_master.GIS_USER.AccessPoints.CREATEDDATE VISIBLE NONE;sde_master.GIS_USER.AccessPoints.EDITEDBY sde_master.GIS_USER.AccessPoints.EDITEDBY VISIBLE NONE;sde_master.GIS_USER.AccessPoints.EDITEDDATE sde_master.GIS_USER.AccessPoints.EDITEDDATE VISIBLE NONE;sde_master_GIS_USER_AccessPoints_CREATEDBY sde_master_GIS_USER_AccessPoints_CREATEDBY VISIBLE NONE;sde_master_GIS_USER_AccessPoints_CREATEDDATE sde_master_GIS_USER_AccessPoints_CREATEDDATE VISIBLE NONE;sde_master_GIS_USER_AccessPoints_EDITEDBY sde_master_GIS_USER_AccessPoints_EDITEDBY VISIBLE NONE;sde_master_GIS_USER_AccessPoints_EDITEDDATE sde_master_GIS_USER_AccessPoints_EDITEDDATE VISIBLE NONE")

# Process: Table To Excel
arcpy.TableToExcel_conversion(QueryTable_View, new_xls, "ALIAS", "DESCRIPTION")

print "Process Successful Completed at" + str(Date)+" " + str(Time) + "."

# Sets the Date & Time since the script Ended.
Date = time.strftime("%m-%d-%Y", time.localtime())# Set the date.
Time = time.strftime("%I:%M:%S %p", time.localtime()) # Set the time.
output.write(str("Process completed at " + str(Date) + " " + str(Time) + "." + "\n")) # Write end time to the log
output.write(str("\n" + " !!!!!!!!!!!!!!!!Import Sucessful!!!!!!!!!!!!!!!!!!!"))
output.close() # Closes the log file.

print "!!!Records exported!!!!"

#End of program

Outcomes