Get the Row number of a record on DataTable based on a value in first column
I have a Global DataTable in QTP with Parameters: Name, Age, Gender, Country
I need to get the row number of a particular record that is based on a value in the first column (Name), so I should have an output say: Patel = Row # 2 . Or Mary = Row # 4. So if more records are added to this DataTable I should see the corresponding changes in the output. Imagine that I have many records in this DataTable, and 5 records shown here is only a simplification. I need this info so that I can latter extract all the fields in the record and use it as parameters.
Name Age Gender Country
Jane 25 Female USA
Patel 28 Male Russia
Max 18 Male India
Mary 23 Female UK
Jose 45 Male Mexico
This datatable is imported, but is irrelevant to the question above.
I think there is no direct method to get the row number, You can follow the below approach And hope this will help you
01 Get the total row count.
02. start looping through all rows.
03. at each stage collect the value from column Name.
04. Check that with expected value, if true then capture the loop counter, that will be your row number.
Hope this works for you
Yeah, there's no "GetRowWithCellText" method for the data table like there is for web tables. You'll just have to write your own to iterate through and find it.
As said above by @NoUse4aName and @New_QTP_Person, you can have your custom method and later you can execute it once during each script execution and store the values in dictionary object and later retrieve the corresponding key(row #) against value(row value) or other way based on your requirement and output the same as required.
Last edited by Prashin2QTP; 06-24-2014 at 09:25 AM.
Lol! I was thinking if there were any undocumented ways in doing this. I was too lazy to do the loop as explained above. In any case, even the loop may not fulfill the purpose because the same 'string' value may occur multiple times on various rows in a column. This creates another problem with even another workaround to get the exact row.
So for now I will stick with the loop or named 'Row, Column' to get the values. Also I may use excel with database techniques as well but I just want to keep it simple.
Many thanks to you all to clear this up.
Helen it might make it a little easier (and quicker) if you pre-sort the sheet prior to import. Will certainly help if the required name is not in the sheet (if the scenarion can exist).
If you are searching on multiple values you might be better to query the spreadsheet like a DB (using the JetSQL driver). I've done this in the past for sheets with 50,000+ records and performace improves dramatically.
Thanks and great input Mark. That was in part when I said I may used database techniques.
However, in current project I can't shorten list because of the how all the data will be used on certain scenarios.
You can try below function or u can change as per your need:-
Public Function GetRowColumnfromTable(WebTableName,ElementTextLike )
if Browser("name:=.*").Page("title:=.*").WebTable("na me:="&WebTableName,"html tag:=TABLE","Index:=0").exist(10) then
TableRowCount = Browser("name:=.*").Page("title:=.*").WebTable("na me:="&WebTableName,"html tag:=TABLE","Index:=0").RowCount
TableColumnCount=Browser("name:=.*").Page("title:= .*").WebTable("name:="&WebTableName,"html tag:=TABLE","Index:=0").ColumnCount(TableRowCount)
GetTextCounter = 0
For r=1 to TableRowCount
For cnt = 1 to TableColumnCount
If GetTextCounter=0 Then
Text = Browser("name:=.*").Page("title:=.*").WebTable("na me:="&WebTableName,"html tag:=TABLE","Index:=0").GetCellData(r,cnt)
'if trim(Text) = trim(ElementTextLike) then
If instr(trim(Text),trim(ElementTextLike)) Then
Setting.WebPackage("ReplayType") = 2
'Browser("name:=.*").Page("title:=.*").WebTable("n ame:="&WebTableName,"html tag:=TABLE","Index:=0").Object.rows(r-1).cells(cnt).highlight
'Browser("name:=.*").Page("title:=.*").WebTable("n ame:="&WebTableName,"html tag:=TABLE","Index:=0").ChildItem(r,cnt,"WebElemen t",0).Highlight
Setting.WebPackage("ReplayType") = 1
If GetTextCounter=0 Then
TestStepName = ""
If your issue is with same name then one more possibility apart from what is mentioned by @marc you can use 2 dictionaries to store the entire table and later try retrieving the required value.
1st dictionary shall contain row number and reference to another dictionary which shall contain all the column headers and values. Yes it might involve some effort initially.
If needed, it would be my privilege to help you in creating a custom method and sending it to your id. Let me know.
In case you found any undocumented way apart from above mentioned ways enlighten us :-).