Using tool validation to create parameter based on fields of another parameters input?

Question

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?

Answer 1

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.