Selecting an element from a drop down list by using an Excel sheet

Help Studio
1

Hello guys,
Have an Excel list and items in thé list should be sélected from a drop down list on thé web , ı already read thé Excel and drop down list but ı Can not match thèm therefore ıt can not be clicked • by usıng find children activity ı got thé drop down list and by using get attribute ı assigned in into a variable bécause thé list ı get thé web was a type Of uielement • thé main problem is matching thé items in Excel With the items in drop down by an assign , so what is the solution should be

2

@Fatih_kizil

welcome to the community

are the values in excel not an exact match?

cheers

1 Like
4

To match Excel items with the dropdown:

  1. Read Excel: Store data in a List<String> or DataTable.
  2. Get Dropdown Items: Use Find Children and Get Attribute to get the dropdown options.
  3. Match and Click:
  • Loop through Excel items and dropdown options.
  • Compare each dropdown’s innertext with the Excel item, then Click when a match is found.

Example

For Each excelItem In excelList
For Each dropdownItem In dropdownElements
If dropdownItem.Get(“innertext”).ToString = excelItem Then
dropdownItem.Click()
Exit For
End If
Next
Next

1 Like
5

thank you @Anil_G ,

yes , the items in the excel can not match the items in the drop-down list.
What ı already did is, read the excel file and assing it into a datatable named dtExcel and assign the items in the excel into a variable named CurrentExcel, and get the items in the drop-down list by using find children activity and assign it into a variable named Children(type is Uielement) and by using get attribute put it into another variable named OptionText and finally inside of a for each loop tried to match them . Below the flow is belong to my last sequence which is my stuck point.
Use App/Browser
-Click (keep the drop down list open)
-Find Children (get the data from the drop-down list)
For each row in data table (for excel)

Body: Assign CurrentExcel=CurrentRow(“Nameoftheexcel”).ToString.Trim

For each currentUiElement

Body: Get Attribute attribute “innerText”, save to Optiontext

Assign OptionText=OptionText.ToString.Trim

İf activity condition OptionText.Trim.ToLower.Equals(CurrentExcel.Trim.ToLower)

Then > Click (classic version) Properties element is filled as currentUiElement

when ı run it , the error was

Error message
Error message757×312 35.9 KB

drop down list has 1841 items and excel has 2 items. If ı run correctly the items in the excel file will be increase but ı think that it can not find the items of excel inside the drop down list. I hope ı clearly explained the problem, thanks for your time and help. Best regards.
6

If condition is like below

OptionText.Trim.ToLower.Equals(CurrentExcel.Trim.ToLower), what do you think of it? is this correct ? ı tried to compare the items in the excel and drop down by using this condition and ı put a click activity after this.

OptionText: string variable for the drop down items

CurrentExcel: string variable for the excel list items.

7

OptionText.Trim.ToLower.Equals(CurrentExcel.Trim.ToLower) is correct syntactically.

The problem is not the condition. The text in the web dropdown does NOT match Excel text exactly even after trimming and lowercasing, as in real UI dropdowns, options often contain hidden characters, extra spaces, line breaks, on-breaking spaces etc…

NormalizedOption = System.Text.RegularExpressions.Regex.Replace(OptionText, “\s+”, " ").Trim.ToLower
NormalizedExcel = System.Text.RegularExpressions.Regex.Replace(CurrentExcel, “\s+”, " ").Trim.ToLower

8

The condition OptionText.Trim.ToLower.Equals(CurrentExcel.Trim.ToLower) is mostly correct for comparing dropdown and Excel items, as it handles case insensitivity and trims spaces. However, I’d suggest the following improvements:

  1. Null Checks: Ensure neither OptionText nor CurrentExcel is null or empty to avoid errors:
If Not String.IsNullOrEmpty(OptionText) AndAlso Not String.IsNullOrEmpty(CurrentExcel) Then
    If OptionText.Trim.ToLower.Equals(CurrentExcel.Trim.ToLower) Then
        ' Click activity
    End If
End If
  1. Culture-Invariant Comparison: For consistency across different systems, use ToLowerInvariant():
If OptionText.Trim.ToLowerInvariant.Equals(CurrentExcel.Trim.ToLowerInvariant) Then
    ' Click activity
End If
9

@Fatih_kizil

as per what you claim items on excel are not an exact match of item in dropdown

can you do one thing..print both currentExcel and optionText variables and check the values or show which you feel are a match..from there we can tell you what to do to find a match

cheers