Latest Contributions by TedKowal

I tried a few other things today... though it may be a file locking issue so I copied the template to the local database ...  The error message is still the same even in your  Try/Exception code This is what the output shows when run Executing (Create Table): CreateTable D:\Workspace\MaintUpdate\ProjGIS\83629_Baseline.mdb pTemplate \\mdxinfo\GIS\BaseData\Signs\MDX_Signs.mdb\SignPanelFieldTemplate # Start Time: Tue Feb 10 10:56:53 2015 Succeeded at Tue Feb 10 10:57:00 2015 (Elapsed Time: 7.48 seconds) Executing (Get Sign Panels for the Selected Supports): GetPanels \\mdxinfo\GIS\BaseData\Signs\MDX_Signs.mdb\SignPanels D:\Workspace\MaintUpdate\ProjGIS\83629_Baseline.mdb\SignSupports D:\Workspace\MaintUpdate\ProjGIS\83629_Baseline.mdb\pTemplate Start Time: Tue Feb 10 10:57:01 2015 Running script GetPanels... Too few parameters. Expected 1. Completed script GetPanels... Succeeded at Tue Feb 10 10:57:06 2015 (Elapsed Time: 5.86 seconds) ... View more

I am using the template. Ran your original code directly from Arcmap same error. Modified the code slightly to run in a model -- same error running arc map 10.2.2  python 2.7.5 The modified model version is: import arcpy  import os  # Local variables  modified to work in the model as a script #fielddb = arcpy.GetParameterAsText(3)  #in memory panels = arcpy.GetParameterAsText (0) supports = arcpy.GetParameterAsText (1) template = arcpy.GetParameterAsText (2) # Get all SupportIDs for where clause IN() statement  supportid = tuple(i[0] for i in arcpy.da.SearchCursor(supports, "SupportID"))  # Optional truncate  # arcpy.TruncateTable_management(template)  # Retreive only panel records that have a support and write to output table  fields = [f.name for f in arcpy.ListFields(template)]  where_clause = "SupportID IN {}".format(supportid)  #arcpy.AddMessage(where_clause) with arcpy.da.SearchCursor(panels, fields, where_clause) as s_cursor:  #too few paramenters. Expected 1   with arcpy.da.InsertCursor(template, fields) as i_cursor:         for row in s_cursor:              i_cursor.insertRow(row) ... View more

My python debugging is not strong in comparison to .net....  but the code bombs out around line 22 RuntimeError: Too few parameters.  Expected 1. No clue .. everything appears to be properly coded.  I noticed that this error code shows up in regards to an access geodatabase allot of times and the recommendation is to try file geodatase which is not an option for me... I print out the field and the where_clause --- they looked fine? ... View more

That is a lot cleaner code than what I was going to try and do....  I see you were frustrated with the output of the join tools as well (That's what I was fighting!) I will give this a shot and let you know.... I will however have to think about where to develop a template table  That was only included for clarification and does not really exist (Yet!) Thanks for your help in this matter --- really appreciated!  I will be paying this back forward! ... View more

The sample dataset contains a table called AllSignPanels small representation my base data describing signs on supports on our system.  SignSupports is a FC containing all the Sign Supports in the Project Area.  What I am trying to put into the model --- create a table called SignPanels that contains all the sign panels that are on the supports within the Project Area. AllSignPanels.SupportID = SignSupport.SupportID based the SignPanelTemplate.   A working query in the database  "SelectQueryIwishToModel"  creates a view of the table I wish to create. ----------------------- I am also thinking of making a python script tool to construct an "in" statement for the where clause ....  thinking about adapting this script: # Local Variables OriginTable = "This must be a Table View or Feature Layer" DestinationTable = "This must be a Table View or Feature Layer" PrimaryKeyField = "Matching Origin Table Field" ForiegnKeyField = "Matching Destination Table Field" def buildWhereClauseFromList(OriginTable, PrimaryKeyField, valueList):   """Takes a list of values and constructs a SQL WHERE        clause to select those values within a given PrimaryKeyField        and OriginTable."""     # Add DBMS-specific field delimiters     fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(OriginTable).path, PrimaryKeyField)     # Determine field type     fieldType = arcpy.ListFields(OriginTable, PrimaryKeyField)[0].type     # Add single-quotes for string field values     if str(fieldType) == 'String':     valueList = ["'%s'" % value for value in valueList]     # Format WHERE clause in the form of an IN statement     whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))     return whereClause def selectRelatedRecords(OriginTable, DestinationTable, PrimaryKeyField, ForiegnKeyField):     """Defines the record selection from the record selection of the OriginTable       and applys it to the DestinationTable using a SQL WHERE clause built       in the previous defintion"""     # Set the SearchCursor to look through the selection of the OriginTable     sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(OriginTable, PrimaryKeyField)])     # Establishes the where clause used to select records from DestinationTable     whereClause = buildWhereClauseFromList(DestinationTable, ForiegnKeyField, sourceIDs)     # Process: Select Layer By Attribute     arcpy.SelectLayerByAttribute_management(DestinationTable, "NEW_SELECTION", whereClause) # Process: Select related records between OriginTable and DestinationTable selectRelatedRecords(OriginTable, DestinationTable, PrimaryKeyField, ForiegnKeyField) ... View more

Unfortunately neither works in my case...the Join Fields in order for me to get something close I have to join the many to the one and takes too long and the script adds fields and changes fieldnames with the dataset qualifier to the output in both joins (Fields and Add)...  ESRI just does not have any good tabular data tools; unless I wanted a feature class or feature, which I don't.  I also ran across in making a feature layer/class in order to get the join to work when converting back to a table view it did not honor the alias name.... the model got too cludgy and messy for such a simple operation. Although the join tool is the solution and does get the proper data it is of a format that is not usable .,.,. it will not work for my Automation project.  I am now looking at making (hopefully) a simple python script tool to export the records from the (many) from a unique list of keys.  --- I wish they could bring back VBA --- I could have done this in my sleep -- create a recordset -- loop through it and append to a new table.   The Dinosaur ... View more

Here is one methodology to take: In vb.net or C#  Make a reference to ADO object (ActiveX Data Object) connect to your database and use the command object to run any functions from your access database.  Set up an parameters in code you need to pass..  You will have to set this up as an addin or extension to your mxd. Some references: How to use the ESRI OLE DB provider with a geodatabase Preparing and Executing Commands This is not an easy task  Some python possibilities that look promising but you will have to find and install various modules because python does not natively talk with ADO/ActiveX objects Mayukh's World While I was searching I did find this on calling Access from VBScript: Calling function in Access from Vbscript ... View more

Thinking about that, yes I believe you cannot reference an access object with VBScript... I know you can in VBA but ESRI has terminated that so another way I think you can do it is through  Net  C# or VB.Net where you can reference the object but you would have to write an addin or extension to do it. My knowledge of python is not adequate to speculate whether or not this could be done -- I know the ODBC api is quite buggy when I played around with that. ... View more

Does this article help you any ... I have used VBS functions to open Access modules functions, forms etc .. https://community.esri.com/thread/32176#post117532 ... View more

Yes I have been in the trenches of this project too long.... I have had too many interruptions.  I did have an Add Join at one time (probably when I was fresher earlier in the undertaking).  For some reason I thought the iterator was a little more full fledged and got bogged in that direction... knowing that I could perform a simple query to extract the filtered table at last resort leaving one manual step! Mainly this project was an exercise for me to finally start to learn Python and the Modeling Tools which I have totally ignored up to this point!  I was and am determined to at least get one under my belt! Thanks alot for pulling me out of my fog! Ted ... View more

No problem!  We all started new to the GIS Field and received help.  My background is not computers or GIS, I am a Structural Engineer -- but I had to learn GIS and Programming....  I receive lots of help from this site from some very smart and intelligent people.  So I help others out when and if I can. Thanks for your gracious comments! ... View more

I just saw this post a little late ... but I had to do some cleaning of a text address field from another agency... I ran across a script that would parse many of the US type of addressing using pyparsing -- I do not know where I got the script or to whom to give credit to but I have adapted the script for use in the field calculator and has proven to be an excellent workhorse for me.... You will of course have to adapt it for you own specific use.... the example below parses the streetname from the address..... you can retrieve also the house number, streetnumber, streettype, PO Box etc... from pyparsing import * def ParseAddress(address): # define number as a set of words     units = oneOf("Zero One Two Three Four Five Six Seven Eight Nine Ten"               "Eleven Twelve Thirteen Fourteen Fifteen Sixteen Seventeen Eighteen Nineteen",               caseless=True)     tens = oneOf("Ten Twenty Thirty Forty Fourty Fifty Sixty Seventy Eighty Ninety",caseless=True)     hundred = CaselessLiteral("Hundred")     thousand = CaselessLiteral("Thousand")     OPT_DASH = Optional("-")     numberword = ((( units + OPT_DASH + Optional(thousand) + OPT_DASH +                       Optional(units + OPT_DASH + hundred) + OPT_DASH +                       Optional(tens)) ^ tens )                    + OPT_DASH + Optional(units) )     # number can be any of the forms 123, 21B, 222-A or 23 1/2     housenumber = originalTextFor( numberword | Combine(Word(nums) +                         Optional(OPT_DASH + oneOf(list(alphas))+FollowedBy(White()))) +                         Optional(OPT_DASH + "1/2")                         )     numberSuffix = oneOf("st th nd rd").setName("numberSuffix")     streetnumber = originalTextFor( Word(nums) +                      Optional(OPT_DASH + "1/2") +                      Optional(numberSuffix) )     # just a basic word of alpha characters, Maple, Main, etc.     name = ~numberSuffix + Word(alphas)     # types of streets - extend as desired     type_ = Combine( MatchFirst(map(Keyword,"ST BLVD LN RD AVE PASS "                             "TRL PATH PSGE WAY LANE "                             "CIR DR PKWY CT SQ "                             "LP TER TERR PL".split())) + Optional(".").suppress())     # street name     nsew = Combine(oneOf("N S E W North South East West NW NE SW SE") + Optional("."))     streetName = (Combine( Optional(nsew) + streetnumber +                             Optional("1/2") +                             Optional(numberSuffix), joinString=" ", adjacent=False )                     ^ Combine(~numberSuffix + OneOrMore(~type_ + Combine(Word(alphas) + Optional("."))), joinString=" ", adjacent=False)                     ^ Combine("Avenue" + Word(alphas), joinString=" ", adjacent=False)).setName("streetName")     # PO Box handling     acronym = lambda s : Regex(r"\.?\s*".join(s)+r"\.?")     poBoxRef = ((acronym("PO") | acronym("APO") | acronym("AFP")) +                  Optional(CaselessLiteral("BOX"))) + Word(alphanums)("boxnumber")     # basic street address     streetReference = streetName.setResultsName("name") + Optional(type_).setResultsName("type")     direct = housenumber.setResultsName("number") + streetReference     intersection = ( streetReference.setResultsName("crossStreet") +                      ( '@' | Keyword("and",caseless=True)) +                      streetReference.setResultsName("street") )     streetAddress = ( poBoxRef("street")                       ^ direct.setResultsName("street")                       ^ streetReference.setResultsName("street")                       ^ intersection )     # how to add Apt, Suite, etc.     suiteRef = (                 oneOf("Suite Ste Apt Apartment Room Rm #", caseless=True) +                 Optional(".") +                 Word(alphanums+'-')("suitenumber"))     streetAddress = streetAddress + Optional(Suppress(',') + suiteRef("suite"))     return streetAddress.parseString(address, parseAll=True) __esri_field_calculator_splitter__ ParseAddress( !PHY_ADDR1!).street.name ... View more