How to Open Recent Download .xlsx File

Hi Zach,

Thank you for creating a post. Does the text you want to verify exist in specific cells or not?

Regards,
Subject7 Team

Yes it should exist in specific cells, that being said I am not sure if they will always be in the same cell.

Hi Zach,

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.

Regards,
Subject7 Team

Hi,

This is a great solution!

Thanks,

Zach

Hey Joseph, 

I am curious still what the other option would be relating to comparing the variable to the entire list? Is this a simple process?

Thanks,

Zach

Hi Zach,

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:

https://subject7.atlassian.net/wiki/spaces/SD/pages/879493553/SET+LIST+Command

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

Regards,
Subject7 Team

Hi,

This works great, the only question I have is if I wanted to add multiple 'compare' functions within that same list, where do I add it within the script?

Thanks,

Zach

Hi Zach,

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.

Regards,
Subject7 Team

Hi,

The data is definitely huge in size. For example, we will export a On-Hand Inventory or Cycle Count report and want to validate the new information the automation script added. The variables we might verify be like an item number, quantity, or and unit number. 

Hi Zach,

So your automation scripts adds data to the website, then you export a file and you want to check that the new data is added to the file

Regards,
Subject7 Team

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. 

Thanks,

Zach

Thanks Zach,

Yes this is entirely possible to automate, lists can be generated from the downloaded reports, then you can create verification steps (Verify_Element or Verify_Text) that use data coming from lists. 

Regards,
Subject7 Team

Is this with the expectation that the report is being opened? We talked about setting it as a list and being able to compare values against that, I guess I am not sure how the step execution would look like with this suggestion..sorry.

Hi Zach,

In order to compare Excel reports, they have to generated as a list using Set_list command so you will be comparing a list against data on your website, or vice versa. 

Regards,
Subject7 Team

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?

Hi Zach,

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
End_If
Loop_end
Loop_end

IF value in step 6 is not blank
Assert / test fails if this value did not match anything in the list
End_if

Please let me know what you think.

Regards,
Subject7 Team

I think this might work! Definitely will take the system awhile to go through the spreadsheet though.