If the data exists in specific cells, you can use Set_List command to generate a list from the XLSX file.
Afterwards, you can use Compare command where the left-hand side will be from the list and the right-hand side will be your data to compare against.
Here's an article that shows how to use Set_List command: https://subject7.atlassian.net/wiki/spaces/SD/pages/879493553/SET+LIST+Command
Please let me know if you have any other questions.
If you would like to compare it to the entire list, you can set up nested Loops, where one loop goes over the columns and one goes over the rows. The 2nd scenario in this document has a similar example:
The only thing to consider is that Compare command will fail if the right-hand side value is different from the left-hand side value, so usually there are 2 solutions:
1. You can change the Runtime options on Compare to Continue on Failure
2. You can use If command whereby you add different actions for each result. So if a match is found, you can print a message or maybe fill a blank list to say "In Row # and Column #, a match was found".
I have created a sample test that you can run yourself and use if it works for your scenario. In a new test case, click Advanced View, paste the below in the Commands Editor tab and hit Apply:
LIST_SET LIST_VARIABLE list CSV URL "https://docs.google.com/spreadsheets/d/1vsDA76VruA-989sTHSHdP4HGJsiUU2X9hKZqVDcj5is/export?format=csv" HAS_HEADERS START_INDEX_WITH_ONE HALT ON ERROR LOOP rows FROM 1 TO @list.rowsCount STEP 1 LOOP columns FROM 1 TO @list.columnsCount STEP 1 COMPARE "tsmith" EQUAL "@list[@rows][@columns]" AS TEXT CONTINUE_WARN ON ERROR LOOP_END LOOP_END LIST_SET LIST_VARIABLE blank EMPTY START_INDEX_WITH_ONE HALT ON ERROR LIST_MODIFY LIST_VARIABLE @blank ADD_COLUMN "matches" HALT ON ERROR LIST_MODIFY LIST_VARIABLE @blank ADD_ROW HALT ON ERROR LOOP rows FROM 1 TO @list.rowsCount STEP 1 LOOP columns FROM 1 TO @list.columnsCount STEP 1 IF "tsmith" EQUAL "@list[@rows][@columns]" AS TEXT LIST_MODIFY LIST_VARIABLE @blank MODIFY_VALUE ROW_INDEX @rows BY_NAME "matches" VALUE "tsmith was a match with @list[@rows][@columns] in row # @rows and column # @columns" HALT ON ERROR IF_END LOOP_END LOOP_END
This depends on your data. If you want to compare one or two values against one list, you can create a Compare/IF step for each one and place them in the same loop where each value will be cross-referenced against the whole list in the same iterations.
But if your data is bigger in size, the approach will be different. Sorry if the answer is too generic, but if you can provide more specific information, I can provide real test cases that you can execute.
Hi - good morning,
Yes, I guess to be more specific we will run a function in our warehouse management system and then want to download the report to verify these values have actually changed/updated properly. For example, we will move a product in inventory to a different location. It would be nice to check the transaction history report to make sure it is updated along with the on-hand inventory report. Another example might run the cycle count function, and then downloading the report for that cycle count. Just validating the numbers and values that are in the downloaded sheet from what were actually inputted.
How do we go about comparing specific values against the entire excel sheet? Because let's say we know the transaction and know that it has a specific lot number, unit number, but we don't know where what line it might show up on the sheet exactly. Is there a way for this?
If you are not sure which column/row the data exists in, you can use a Loop with IF condition to do your comparison. To make this robust and not give a false positive, you can always add one additional step to the condition if it finds a match and fail the test if the variable does not have a value assigned to it. Here is a sample scenario:
Set_List / generate a list from an Excel file
Get_Web_Value / retrieve a value from a web page or some source
Loop / Outer loop for columns
Loop / Inner loop for rows
IF / value from variable in step 2 matches an entry in the list
Set_Var / Assigns a value to a variable
IF value in step 6 is not blank
Assert / test fails if this value did not match anything in the list
Please let me know what you think.
Please sign in to leave a comment.