1

I'm assuming I'm missing something and this is a pretty easy thing to do but I'm stumped...

I'm working on a script that currently takes and input excel file and uses a "hard coded" field name that I know is in the table. I would like to make the tool a little more user friendly in that I would like to have a drop-down of field values from within the table, letting the user select the appropriate field from the table. This would ensure that the field being used is indeed in the table.

So... If I have an input parameter for a file in which the user selects an excel workbook, how would I create another input parameter that would then be a drop-down menu of field headers from the selected excel workbook. Is there some kind of validation code for this?

  • Would you accept adding another step converting an Excel sheet to an in_memory table (conversion tools)? You could also use xlrd (python-excel.org) to read Excel sheet and get a list of strings which could be used for validation. – Alex Tereshenkov Nov 26 '14 at 17:07
  • Just add the parameter 'Data Type'= Field and specify 'Obtained from'. You can even filter field type, e.g. Text only using Filter – FelixIP Nov 26 '14 at 19:18
  • @FelixIP: 'Data Type'= Field and specify 'Obtained from' only works for feature class. Not on File DataType. – Surya Nov 30 '14 at 21:05
3

Yes, we can add excel file as input in tool parameter to add user-friendly fields but this practice is not recommended by esri's python team. You should open a file when tool run.

But as you need to do this in validation then here is the code. Add it accordingly. In tool, I added the first parameter DataType as File and second parameter DataType as String.

def updateParameters(self):
    """Modify the values and properties of parameters before internal
    validation is performed.  This method is called whenever a parameter
    has been changed."""
    # NOTE: your excel file must contain headers in first row of first sheet otherwise
    # change the code as your requirement and
    # rows with no value should not be listed in tool's option list

    # Only "xls" and "xlsx" file extention should be taken as your file param
    # param index 0 as in "self.params[0]" should be your parameter index where
    # you intended to take excel file
    if self.params[0].value and \
       (self.params[0].value.value.endswith("xls") or self.params[0].value.value.endswith("xlsx")):
        import xlrd
        book = xlrd.open_workbook(self.params[0].value.value)
        sheet = book.sheet_by_index(0)
        # change "fields" in below line according to your excel field header
        # this will first find that header in excel then populate the rows
        # listed below to the given field header(in my case "fields").
        col_num = (col_index for col_index in xrange(sheet.ncols) if sheet.cell(0, col_index).value.lower() == "fields").next()
        options = [sheet.cell(row_index, col_num).value for row_index in xrange(1, sheet.nrows) if sheet.cell(row_index, col_num).value]
        self.params[1].filter.list = options
    return

def updateMessages(self):
    """Modify the messages created by internal validation for each tool
    parameter.  This method is called after internal validation."""
    # Show error message if file is not excel file
    if self.params[0].value and not\
       (self.params[0].value.value.endswith("xls") or self.params[0].value.value.endswith("xlsx")):
        # Error message
       self.params[0].setErrorMessage("File selected is not a valid excel file type.")
    return

I hope this will help, enjoy.

  • Are you able to cite where Esri's Python Team say that this is not a recommended practice? – PolyGeo Nov 30 '14 at 21:11
  • Best I can recollect is they said in some esri training videos. One of the audience asked the question on how to handle file in Tool Validation part and they answered that they don't recommend this in Validation part. If I get the link sure I will share. – Surya Dec 1 '14 at 10:19
  • @Surya This looks good and seems to work fine. Not sure why it wouldn't be recommended by ESRI. I wonder... Could the same approach work if the input were a .csv? – GeoJohn Dec 1 '14 at 20:21
  • @GeoJohn: Please mark this as answered if this worked for you. Thanks. – Surya Dec 3 '14 at 11:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.