topic Re: Help with writing python code to stop a script if an external table isn't complete in Python Questions

Help with writing python code to stop a script if an external table isn't complete

ABishop — Wed, 08 Sep 2021 18:23:22 GMT

I am working in ArcGIS Pro 2.8.2. I am not a professional programmer. I have been writing python code for about a year and a majority of it comes from script I export and piece together using geoprocessing tools and models in ArcGIS Pro. I have written a series of scripts which run automatically at night to update our SDE and also dependent services which serve web maps/apps to the public. The issue is that one of the data sources that assists with these updates is a .csv file created from a batch script that is ran from our CAMA system. Sometimes this batch script doesn't work properly and the file doesn't have complete data which causes all of my scripts to generate files that aren't valid and then consequently, our web maps/apps fail the next day.

What can I add my python scripts to stop running if the source data is incomplete? How would I go about doing this? Any advice is greatly appreciated!

Re: Help with writing python code to stop a script if an external table isn't complete

MichaelVolz — Wed, 08 Sep 2021 19:31:28 GMT

What can you trap in the csv file to determine that the data is corrupt? Do you always get all CAMA records and is it just that some fields are empty or would you only get a subset of expected records?

The easiest approach would be to define a number of expected records and if you do not get that then cancel your scripts. The more difficult approach would be to read your data and look for Null fields where you would expect data and cancel your script if you find a Null value where a value is expected.

Re: Help with writing python code to stop a script if an external table isn't complete

JoeBorgione — Wed, 08 Sep 2021 19:59:41 GMT

What determines Sometimes this batch script doesn't work properly and the file doesn't have complete data

As @MichaelVolz suggests, if you can first check that file for completeness right at the beginning, you could exit before anything else is done.

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Wed, 08 Sep 2021 20:13:23 GMT

@JoeBorgione and @MichaelVolz

Thank you for replying! I would think the check of the .csv file from the batch process would be verifying a certain amount of records in the .csv and if it doesn't contain that amount of records, then the scripts that I have automated wouldn't run to extract the data which is used to create all the other features.

Do you have an example of code I could use? Is there a certain tool or command I can use in my python code to do this?

Thank you both for any assistance you can offer.

Amanda

Re: Help with writing python code to stop a script if an external table isn't complete

JoeBorgione — Wed, 08 Sep 2021 20:15:10 GMT

See if this helps you out: https://www.geeksforgeeks.org/count-number-of-lines-in-a-text-file-in-python/

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Wed, 08 Sep 2021 20:22:22 GMT

@JoeBorgione

Thanks for the suggestion. I assume that is only part of what I will need to do? I am not a programmer. I know a little bit of python code and the rest I rely on geoprocessing tool script and model builder. I guess what I am looking for is a python code that will do the check of the .csv file then stop the extract data script from running if the .csv doesn't have x # of lines/records.

Re: Help with writing python code to stop a script if an external table isn't complete

MichaelVolz — Wed, 08 Sep 2021 20:24:31 GMT

Not exactly sure how you would set your record threshold, but you could either hard-code that into the python script or read it in from a config file that you can modify in a text editor.

If your Counter from script < threshold then instead of running code send an e-mail alerting you to bad data:

SUBJECT = "CAMA Data Update Alert "
TO = ["e-mail address"]
FROM = "server address"
text = "There was an error GIS data"

BODY = string.join((
"From: %s" % FROM,
"To: %s" % TO,
"Subject: %s" % SUBJECT ,
"",
text
), "\r\n")
server = smtplib.SMTP('SMTP server URL',25) #server = smtplib.SMTP(HOST)
server.sendmail(FROM, TO, BODY)
server.quit()

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Wed, 08 Sep 2021 20:29:43 GMT

@MichaelVolz

Thank you again. I don't really want to rely on an email notification to alert me because then I have to manually stop the script on the server. Is there a way to just code the check into the script itself and then if the data is bad, stop the script right before the data is extracted from the .csv?

Re: Help with writing python code to stop a script if an external table isn't complete

JoeBorgione — Wed, 08 Sep 2021 20:33:18 GMT

I don't have anything written off hand, but if I get a chance I'll see if I can scrape something together. But that code in the sample is pretty straight forward. Here is some translation for you:

# Opening a file file = open("gfg.txt","r") # this opens gfg.txt for read only Counter = 0 # sets the Counter variable to 0 (zeror) # Reading from file Content = file.read() # sets another variable called Contente and reads # everything from the read only file you just #opened CoList = Content.split("\n") #sets yet another variable called CoList; when # the file is open and read, it's all in one #line. This splits each record at the new line for i in CoList: if i: Counter += 1 # this for loop just plows through all the records and each time there is a # new record, the counter variable increases by one. At the end of the for # loop, Counter is equal to the number of records. You can check this value # in an If/Then block against some value you set: if Counter < 1000: exit

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Wed, 08 Sep 2021 20:43:11 GMT

@JoeBorgione

Thank you. Please stand by while I try to implement this.

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Wed, 08 Sep 2021 21:06:29 GMT

@JoeBorgione

I tried the following script and it did not work. Please tell me what I'm doing wrong. Does the .csv file have to be a .txt? Does it have to be set in a variable instead of a file path?

#import arcpy module
import arcpy, os, sys
from arcpy import env
env.workspace = r"K:\GIS_UPDATE\DATA\EXTRACTVIEWS"
env.overwriteOutput = True

startTime = time.perf_counter()

# Opening a file
file = open("K:\AMANDA\Merlin226Data\MCPA226Data1.csv","r") # this opens txt file read only
Counter = 0 # sets the Counter variable to 0 (zero)

# Reading from file
Content = file.read() # sets another variable called Contente and reads
# everything from the read only file you just
#opened
CoList = Content.split("\n") #sets yet another variable called CoList; when
# the file is open and read, it's all in one
#line. This splits each record at the new line

for i in CoList:
if i:
Counter += 1
# this for loop just plows through all the records and each time there is a
# new record, the counter variable increases by one. At the end of the for
# loop, Counter is equal to the number of records.

if Counter < 250000:
exit

#MCPA226Data to Merlin226 Extract-----------------------------------------------------------------
arcpy.conversion.TableToTable(r"K:\AMANDA\Merlin226Data\MCPA226Data.csv", r"K:\GIS_UPDATE\DATA\EXTRACTVIEWS\EXTRACTVIEWS.gdb", "MCPA226DataTEST", '', r'PARCEL "PARCEL" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PARCEL,0,8000;ALT_KEY "ALT_KEY" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ALT_KEY,-1,-1;PC "PC" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PC,-1,-1;HX "HX" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,HX,0,8000;AG "AG" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,AG,0,8000;ADD_1 "ADD_1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_1,0,8000;ADD_2 "ADD_2" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_2,0,8000;ADD_3 "ADD_3" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_3,0,8000;ADD_4 "ADD_4" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_4,0,8000;ADD_5 "ADD_5" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_5,0,8000;ZIP "ZIP" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ZIP,0,8000;SITUS_1 "SITUS_1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SITUS_1,0,8000;SITUS_2 "SITUS_2" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SITUS_2,0,8000;MILL_GRP "MILL_GRP" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MILL_GRP,-1,-1;NABRHD "NABRHD" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,NABRHD,-1,-1;BK1 "BK1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BK1,0,8000;PG1 "PG1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PG1,0,8000;MO1 "MO1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MO1,-1,-1;YR1 "YR1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,YR1,-1,-1;Q1 "Q1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,Q1,0,8000;VI1 "VI1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,VI1,0,8000;INST1 "INST1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,INST1,0,8000;PR_1 "PR_1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PR_1,-1,-1;MAP_NBR "MAP_NBR" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MAP_NBR,-1,-1;TWP "TWP" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TWP,-1,-1;RGE "RGE" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RGE,-1,-1;SEC "SEC" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SEC,-1,-1;ACRES "ACRES" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ACRES,-1,-1;LND1 "LND1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LND1,0,8000;LND2 "LND2" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LND2,0,8000;LND3 "LND3" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LND3,0,8000;ZONE1 "ZONE1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ZONE1,0,8000;ZONE2 "ZONE2" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ZONE2,0,8000;ZONE3 "ZONE3" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ZONE3,0,8000;BLDS "BLDS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BLDS,-1,-1;WALL "WALL" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,WALL,-1,-1;AGE "AGE" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,AGE,-1,-1;COND "COND" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,COND,-1,-1;ARCH "ARCH" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ARCH,0,8000;USE_SF "USE_SF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,USE_SF,-1,-1;YRBLT1 "YRBLT1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,YRBLT1,-1,-1;QUAL1 "QUAL1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,QUAL1,-1,-1;TYPE1 "TYPE1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TYPE1,0,8000;POOL "POOL" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,POOL,0,8000;STRY "STRY" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,STRY,0,8000;BLD1_SF "BLD1_SF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BLD1_SF,-1,-1;WELL "WELL" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,WELL,0,8000;SEPTIC "SEPTIC" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SEPTIC,0,8000;ASSD_VAL "ASSD_VAL" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ASSD_VAL,-1,-1;TOT_VAL "TOT_VAL" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_VAL,-1,-1;TOT_LND_VA "TOT_LND_VA" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_LND_VA,-1,-1;TOT_BLD_VA "TOT_BLD_VA" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_BLD_VA,-1,-1;TOT_MISC_V "TOT_MISC_V" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_MISC_V,-1,-1;TOT_TAX_VA "TOT_TAX_VA" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_TAX_VA,-1,-1;TOT_TAXES "TOT_TAXES" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_TAXES,-1,-1;APP_DATE "APP_DATE" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,APP_DATE,-1,-1;APP_ID "APP_ID" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,APP_ID,-1,-1;SOS "SOS" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SOS,0,8000;SU "SU" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SU,0,8000;HX_YR "HX_YR" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,HX_YR,-1,-1;STR "STR" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,STR,-1,-1;GRP "GRP" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,GRP,-1,-1;CITY "CITY" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,CITY,-1,-1;VM "VM" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,VM,-1,-1;CF "CF" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,CF,0,8000;CL_VAL "CL_VAL" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,CL_VAL,-1,-1;N_CONS "N_CONS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,N_CONS,-1,-1;MKT_ADJ "MKT_ADJ" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MKT_ADJ,-1,-1;DATE_CREAT "DATE_CREAT" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,DATE_CREAT,-1,-1;DATE_L_UPD "DATE_L_UPD" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,DATE_L_UPD,-1,-1;B_UP "B_UP" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,B_UP,-1,-1;PPAR "PPAR" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PPAR,0,8000;RESUSESF "RESUSESF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RESUSESF,-1,-1;RESTOTSF "RESTOTSF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RESTOTSF,-1,-1;COMUSESF "COMUSESF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,COMUSESF,-1,-1;COMTOTSF "COMTOTSF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,COMTOTSF,-1,-1;RESBLDS "RESBLDS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RESBLDS,-1,-1;COMBLDS "COMBLDS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,COMBLDS,-1,-1;LAVALUE "LAVALUE" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LAVALUE,-1,-1;MRKT_AREA "MRKT_AREA" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MRKT_AREA,-1,-1;BEDS "BEDS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BEDS,-1,-1;BATHS "BATHS" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BATHS,-1,-1;GARAGE "GARAGE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,GARAGE,0,8000;FIRE "FIRE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,FIRE,0,8000;LOCOBS "LOCOBS" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LOCOBS,-1,-1;FUNCOBS "FUNCOBS" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,FUNCOBS,-1,-1;SEC_5 "SEC_5" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SEC_5,0,8000;LANDREV "LANDREV" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LANDREV,0,8000;NBRUNITS "NBRUNITS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,NBRUNITS,-1,-1;MH "MH" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MH,-1,-1;RTE "RTE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RTE,0,8000;HXUNDELIVERABLE "HXUNDELIVERABLE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,HXUNDELIVERABLE,0,8000;ROADTYPE "ROADTYPE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ROADTYPE,0,8000;SUBTYPE "SUBTYPE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SUBTYPE,0,8000;SINKHOLE "SINKHOLE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SINKHOLE,0,8000', '')

print("Merlin226Data to Merlin226DataTEST extract completed")

endTime = time.perf_counter()
elapsedTime = round((endTime - startTime) / 60, 2)
print("Script finished in {0} minutes".format(elapsedTime))

Re: Help with writing python code to stop a script if an external table isn't complete

JoeBorgione — Wed, 08 Sep 2021 21:13:33 GMT

file = open("K:\AMANDA\Merlin226Data\MCPA226Data1.csv","r") replace with: file = open(r"K:\AMANDA\Merlin226Data\MCPA226Data1.csv","r")

Use r to specify 'raw' text to the python interpreter. Otherwise you have to escape those slashes in your path like this:

file = open("K:\\AMANDA\\Merlin226Data\\MCPA226Data1.csv","r")

I'm lazy and forgetful so when it comes to paths I always use r"C:\blah\blah\blah" so I don't have to remember which slash to use.

Also, you'll notice I'm using a code window to past my python into. Expand the menu bar by clicking on the three dots; click on the </> symbol and choose python from the drop down list. Paste your code there. It makes it much easier for us to read and/or trouble shoot...

Re: Help with writing python code to stop a script if an external table isn't complete

Anonymous User — Wed, 08 Sep 2021 21:22:25 GMT

I'd add this to the mix of suggestions. If its a possible issue with the second script starting before the CAMA export is complete, and you know the amount of time it generally takes for the CAMA export, you can set your second script to start a few minutes after the csv is complete. Then start by checking the last modified time of the csv file to see if its done or is still being constructed and abort if it is or continue to check for valid data in the csv. This might need some tweaking to work with how the export is creating the csv and if the modified time is updated while the file is being created. Might need to add/combine a created time check if modified isn't working.

import os from datetime import datetime as dt # Check the last modified date yourCSV = os.path.getmtime(r'path to csv') lstEdt = dt.fromtimestamp(yourCSV) now = dt.now() timeDelta = now - lstEdt hours, remainder = divmod(timeDelta.seconds, 3600) minutes, seconds = divmod(remainder, 60) if minutes >= 15: # this means it has been over 15 minutes since last modified and you can assume its complete maybe print('CAMA Process is complete') # Check the CSV for completeness and abort if you find bad values with open(yourCSV) as csv_file: csv_reader = csv.reader(csv_file, delimiter='t') for row in csv_reader: if stuff is ok conditional: do more stuff else: print('Abort! CSV is corrupt') else: # Nope print('CAMA process is not complete. Epically aborting or waiting for 5 min and trying again')

Re: Help with writing python code to stop a script if an external table isn't complete

MichaelVolz — Wed, 08 Sep 2021 21:22:44 GMT

Amanda:

Do you have any communication with the admin of the original csv file? I ask because how would you know the data errors would be fixed the next time to try to run the script. Maybe it would be more proactive to trap the errors and let the csv admin personnel fix the data based on you errors you trapped in your code.

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Thu, 09 Sep 2021 10:59:39 GMT

@JoeBorgione

Thank you again. I will attempt to implement this morning and update you as soon as I get a result. Also, thanks for the tip on how to insert the python script in this message. I will definitely use this for the future! You are awesome!

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Thu, 09 Sep 2021 11:02:23 GMT

@MichaelVolz

Hi.... I do have communication with the admin of the original .csv file. The issue is that the batch process she has running to create the .csv is coming from our CAMA database and she said it is very complicated to recreate or even troubleshoot. I don't think she has much experience with it because she just took over as our IT Director. Anyway, I am doing what I can to mitigate the issues because, as you can imagine, I have spent a log of time and effort creating all of my tasks to make this work and when her file doesn't get created properly, well... then it reflects poorly on me because none of my tasks work. SMH

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Thu, 09 Sep 2021 11:04:45 GMT

@Anonymous User

Hello Jeff,

Thank you for suggestion. I will definitely consider it if my first option with Joe doesn't work. This has been a 4 month project for me to get all of these tasks automated. It certainly is disheartening to have one file that isn't create by me to mess it all up!

Amanda

Re: Help with writing python code to stop a script if an external table isn't complete

Anonymous User — Thu, 09 Sep 2021 13:12:29 GMT

Good plan. There was a lot of conversation I didn't see while writing that post so its late to the party. If you have any questions, feel free to ask.

Re: Help with writing python code to stop a script if an external table isn't complete

ABishop — Thu, 09 Sep 2021 13:50:40 GMT

@JoeBorgione

I tried to implement the script below this morning and it didn't exit, it continued to extract the table anyway. Any more suggestions?

#import arcpy module import arcpy, os, sys from arcpy import env env.workspace = r"K:\GIS_UPDATE\DATA\EXTRACTVIEWS" env.overwriteOutput = True startTime = time.perf_counter() # Opening a file file = open(r"K:\AMANDA\Merlin226Data\MCPA226Data.csv","r") # opens file read only Counter = 0 # sets the Counter variable to 0 (zero) # Reading from file Content = file.read() # sets another variable called Content and reads # everything from the read only file you just # opened CoList = Content.split("\n") #sets yet another variable called CoList; when #the file is open and read, it's all in one #line. This splits each record at the new line for i in CoList: if i: Counter += 1 # this for loop just plows through all the records and each time there is a # new record, the counter variable increases by one. At the end of the for # loop, Counter is equal to the number of records. if Counter < 250000: exit #MCPA226Data to Merlin226 Extract----------------------------------------------------------------- arcpy.conversion.TableToTable(r"K:\AMANDA\Merlin226Data\MCPA226Data.csv", r"K:\GIS_UPDATE\DATA\EXTRACTVIEWS\EXTRACTVIEWS.gdb", "MCPA226DataTEST", '', r'PARCEL "PARCEL" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PARCEL,0,8000;ALT_KEY "ALT_KEY" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ALT_KEY,-1,-1;PC "PC" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PC,-1,-1;HX "HX" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,HX,0,8000;AG "AG" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,AG,0,8000;ADD_1 "ADD_1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_1,0,8000;ADD_2 "ADD_2" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_2,0,8000;ADD_3 "ADD_3" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_3,0,8000;ADD_4 "ADD_4" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_4,0,8000;ADD_5 "ADD_5" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ADD_5,0,8000;ZIP "ZIP" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ZIP,0,8000;SITUS_1 "SITUS_1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SITUS_1,0,8000;SITUS_2 "SITUS_2" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SITUS_2,0,8000;MILL_GRP "MILL_GRP" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MILL_GRP,-1,-1;NABRHD "NABRHD" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,NABRHD,-1,-1;BK1 "BK1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BK1,0,8000;PG1 "PG1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PG1,0,8000;MO1 "MO1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MO1,-1,-1;YR1 "YR1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,YR1,-1,-1;Q1 "Q1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,Q1,0,8000;VI1 "VI1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,VI1,0,8000;INST1 "INST1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,INST1,0,8000;PR_1 "PR_1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PR_1,-1,-1;MAP_NBR "MAP_NBR" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MAP_NBR,-1,-1;TWP "TWP" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TWP,-1,-1;RGE "RGE" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RGE,-1,-1;SEC "SEC" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SEC,-1,-1;ACRES "ACRES" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ACRES,-1,-1;LND1 "LND1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LND1,0,8000;LND2 "LND2" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LND2,0,8000;LND3 "LND3" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LND3,0,8000;ZONE1 "ZONE1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ZONE1,0,8000;ZONE2 "ZONE2" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ZONE2,0,8000;ZONE3 "ZONE3" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ZONE3,0,8000;BLDS "BLDS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BLDS,-1,-1;WALL "WALL" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,WALL,-1,-1;AGE "AGE" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,AGE,-1,-1;COND "COND" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,COND,-1,-1;ARCH "ARCH" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ARCH,0,8000;USE_SF "USE_SF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,USE_SF,-1,-1;YRBLT1 "YRBLT1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,YRBLT1,-1,-1;QUAL1 "QUAL1" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,QUAL1,-1,-1;TYPE1 "TYPE1" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TYPE1,0,8000;POOL "POOL" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,POOL,0,8000;STRY "STRY" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,STRY,0,8000;BLD1_SF "BLD1_SF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BLD1_SF,-1,-1;WELL "WELL" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,WELL,0,8000;SEPTIC "SEPTIC" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SEPTIC,0,8000;ASSD_VAL "ASSD_VAL" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ASSD_VAL,-1,-1;TOT_VAL "TOT_VAL" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_VAL,-1,-1;TOT_LND_VA "TOT_LND_VA" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_LND_VA,-1,-1;TOT_BLD_VA "TOT_BLD_VA" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_BLD_VA,-1,-1;TOT_MISC_V "TOT_MISC_V" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_MISC_V,-1,-1;TOT_TAX_VA "TOT_TAX_VA" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_TAX_VA,-1,-1;TOT_TAXES "TOT_TAXES" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,TOT_TAXES,-1,-1;APP_DATE "APP_DATE" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,APP_DATE,-1,-1;APP_ID "APP_ID" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,APP_ID,-1,-1;SOS "SOS" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SOS,0,8000;SU "SU" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SU,0,8000;HX_YR "HX_YR" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,HX_YR,-1,-1;STR "STR" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,STR,-1,-1;GRP "GRP" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,GRP,-1,-1;CITY "CITY" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,CITY,-1,-1;VM "VM" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,VM,-1,-1;CF "CF" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,CF,0,8000;CL_VAL "CL_VAL" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,CL_VAL,-1,-1;N_CONS "N_CONS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,N_CONS,-1,-1;MKT_ADJ "MKT_ADJ" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MKT_ADJ,-1,-1;DATE_CREAT "DATE_CREAT" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,DATE_CREAT,-1,-1;DATE_L_UPD "DATE_L_UPD" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,DATE_L_UPD,-1,-1;B_UP "B_UP" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,B_UP,-1,-1;PPAR "PPAR" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,PPAR,0,8000;RESUSESF "RESUSESF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RESUSESF,-1,-1;RESTOTSF "RESTOTSF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RESTOTSF,-1,-1;COMUSESF "COMUSESF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,COMUSESF,-1,-1;COMTOTSF "COMTOTSF" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,COMTOTSF,-1,-1;RESBLDS "RESBLDS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RESBLDS,-1,-1;COMBLDS "COMBLDS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,COMBLDS,-1,-1;LAVALUE "LAVALUE" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LAVALUE,-1,-1;MRKT_AREA "MRKT_AREA" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MRKT_AREA,-1,-1;BEDS "BEDS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BEDS,-1,-1;BATHS "BATHS" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,BATHS,-1,-1;GARAGE "GARAGE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,GARAGE,0,8000;FIRE "FIRE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,FIRE,0,8000;LOCOBS "LOCOBS" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LOCOBS,-1,-1;FUNCOBS "FUNCOBS" true true false 8 Double 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,FUNCOBS,-1,-1;SEC_5 "SEC_5" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SEC_5,0,8000;LANDREV "LANDREV" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,LANDREV,0,8000;NBRUNITS "NBRUNITS" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,NBRUNITS,-1,-1;MH "MH" true true false 4 Long 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,MH,-1,-1;RTE "RTE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,RTE,0,8000;HXUNDELIVERABLE "HXUNDELIVERABLE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,HXUNDELIVERABLE,0,8000;ROADTYPE "ROADTYPE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,ROADTYPE,0,8000;SUBTYPE "SUBTYPE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SUBTYPE,0,8000;SINKHOLE "SINKHOLE" true true false 8000 Text 0 0,First,#,K:\AMANDA\Merlin226Data\MCPA226Data.csv,SINKHOLE,0,8000', '') print("Merlin226Data to Merlin226DataTEST extract completed") endTime = time.perf_counter() elapsedTime = round((endTime - startTime) / 60, 2) print("Script finished in {0} minutes".format(elapsedTime)) #input('Press ENTER to exit')

Re: Help with writing python code to stop a script if an external table isn't complete

MichaelVolz — Thu, 09 Sep 2021 15:50:07 GMT

Is there a chance that the counter is never getting to 250000? Why not set it at a much lower value and print out what the counter is at right before the program exits as it might be doing something unexpected at this point? This would only be needed for trouble-shooting purposes.