Python Behave : How to read examples from external excel file for Scenarios Outline

Question

Instead of passing data from Examples: I want to pass the data from excel. as in that step I just want to compare expected and actual results for batch automation. and want to capture the total pass or failure of the test cases.

Scenario Outline: Browser Test

    When I visit the URL <base>/<page>/<ordNumber>/<custName>
    Then the browser contains test <custNumber>

    Examples: 
     | base                         | page   | ordNumber | custName |
     | http://www.stackoverflow.com | orders | 123       | John     |
     | http://www.stackoverflow.com | orders | 456       | Mike     |
     | http://www.stackoverflow.com | orders | 789       | Tom      |


examples.xls

 | base                         | page   | ordNumber | custName |
 | http://www.stackoverflow.com | orders | 123       | John     |
 | http://www.stackoverflow.com | orders | 456       | Mike     |
 | http://www.stackoverflow.com | orders | 789       | Tom      |

score 2 · Answer 1 · answered Aug 20 '21 at 13:33

I've had the same problem, and the solution isn't as straight forward as one might hope. You can't simply point Examples to your Excel file, instead, build your Examples table using the data from the Excel. You do this in environment.py under the before_feature hook.

Here's a proof of concept based on the scenario you posted:

The feature file should contain just the header in the Examples table. Note also that I have tagged the feature, will use the tag further down:

 @test-data-from-excel
 Feature: Examples table with test data from Excel

     Scenario Outline: Browser Test

         When I visit the URL <base>/<page>/<ordNumber>/<custName>
         Then the browser contains test <custNumber>

     Examples: 
      | base                         | page   | ordNumber | custName |

In environment.py:

import pandas as pd

def before_feature(context, feature):
...
if 'test-data-from-excel' in feature.tags:   # >>> you can have this check on feature.name instead of tag
    path_to_file = '*<path/to/file/here.xlsx>*'
    df=pd.read_excel(path_to_file)
    example = next(sc.examples[0] for sc in feature.scenarios if sc.name == 'Browser Test') # >>> find the first examples object for scenario with given name
    test_table = example.table
    for row in df.itertuples(index=False):
        test_table.add_row(row)

I am using pandas to read the Excel file - I find it most convenient, also because I need it elsewhere in the framework. There are other libraries allowing the same operation, use whatever you prefer. If you don't have pandas installed, then:

pip install pandas

Thank you Obuz for sharing this, It will help to create solution for my Batch automation. Actually in my Excel file, i have multiple scenarios, for which I'm calculating expected results in one scenarios, and we are executing the batch run in one scenario and then in next sceanrio we are updating the actual result and adding infront of each account. and in final scenario i will be comparing and expected and actual result. To plush each comparison result as a separate case to pass and fail, i wanted to get it Read by Examples. Will try implementing it and will share a solution if works. — Arpan Saini, Aug 20 '21 at 16:47
You need to install openpyxl for this to work though. [Copied from the NAA-answer](https://stackoverflow.com/a/73077320/5211833) by [Uğur GELEÇ](https://stackoverflow.com/users/14692679/u%c4%9fur-gele%c3%87) — Adriaan, Jul 22 '22 at 08:29

Jerome Josephraj · Answer 2 · 2021-08-19T21:01:27.250

I would recommend putting all the data for a scenario inside of Gherkin documents, but you might have a valid use cases for pulling data from excel. However, in my experience, these type of requirements are rare. The reason why it is not recommended is, your BDD feature files are your requirements and should contain the right level of information to document the expected behavior of the system. If your data comes from an excel, then it just makes the requirement reading bit more difficult and makes it difficult to maintain.

Saying that if there is a strong reason for you to have these data stored in excel, you could easily achieve this using NoCodeBDD. All you have to do is map the column names and upload the excel and the tool take care of the rest. Please check this .gif to see how it is done. https://nocodebdd.live/examples-using-excel

Disclaimer: I am the founder of NoCodeBDD.

If you are using Junit5 here is an example on how it is done https://newbedev.com/data-driven-testing-in-cucumber-using-excel-files-code-example

Thanks Jerome, I understand the point of keeping the implementation that, way, i have created a patch automation framework, which require such a need. in last scenario we will be comparing all expected and actual results for all the scenarios. Which has been feed in excel in previous scenarios. Now to generate a Report, i need to get it passed through scenario example. — Arpan Saini, Aug 20 '21 at 16:51

Python Behave : How to read examples from external excel file for Scenarios Outline

2 Answers2