Read Test Data from Excel

Data-driven testing is one of the most effective ways to improve test automation. Instead of hardcoding test data directly inside test cases, we can store the data externally in files such as Excel, CSV, JSON, or databases. In this article, we will see how to implement Data-Driven Testing in Robot Framework using Excel data with the ExcelLibrary.

We will use Excel data to select values from three dropdowns:

• Day
• Month
• Year

The same test logic can then be reused with different rows of Excel data.

What Is Data-Driven Testing?

In traditional test automation, test data is often hardcoded directly into the test case.

For example:

Select Options By    //div[4]/div/select[1]    text    5
Select Options By    //div[4]/div/select[2]    text    January
Select Options By    //div[4]/div/select[3]    text    2026

This approach becomes difficult to maintain when we want to test multiple combinations of data.

With data-driven testing, the test data is stored separately:

The test case reads the required values from Excel and uses them during execution.

The automation logic remains the same while the test data can be changed independently.

Project Structure

A simple Robot Framework project can be structured like this:

RobotFrameworkProject/
│
├── Tests/
│   └── DateTests.robot
│
├── Data/
│   └── Data.xlsx
│
└── Resources/
    └── CommonKeywords.robot

The Excel file contains the test data.

For example, the date sheet may contain:

In this example, row 5 contains:

Day   = 20
Month = April
Year  = 2027

Import ExcelLibrary

First, import ExcelLibrary into the Robot Framework file.

*** Settings ***
Library    ExcelLibrary

The complete settings section may look like this:

*** Settings ***
Library    SeleniumLibrary
Library    ExcelLibrary

Here:

• SeleniumLibrary is used for browser automation.
• ExcelLibrary is used to read data from Excel files.

Creating a Reusable Keyword to Read Excel Data

Instead of writing Excel-related logic repeatedly inside the test case, we can create a reusable keyword.

Read DropDown Data
    [Arguments]    ${filepath}    ${sheetname}    ${rownum}    ${colnum}
    Open Excel Document    ${filepath}    1
    Get Sheet              ${sheetname}
    ${data}    Read Excel Cell    ${rownum}    ${colnum}
    [Return]    ${data}
    Close Current Excel Document

This keyword accepts four arguments:

For example:

Read DropDown Data
    ${CURDIR}${/}Data${/}Data.xlsx
    date
    5
    1

This means:

Excel File : Data.xlsx
Sheet      : date
Row        : 5
Column     : 1

The keyword returns the value stored in that cell.

Reading Data from Excel in a Test Case

Now we can use the reusable keyword inside our test case.

*** Test Cases ***
SelectDateFromExcelData
    ${day}      Read DropDown Data
    ...         ${CURDIR}${/}Data${/}Data.xlsx
    ...         date
    ...         5
    ...         1
    ${month}    Read DropDown Data
    ...         ${CURDIR}${/}Data${/}Data.xlsx
    ...         date
    ...         5
    ...         2
    ${year}     Read DropDown Data
    ...         ${CURDIR}${/}Data${/}Data.xlsx
    ...         date
    ...         5
    ...         3

The three calls read:

${day}   → Row 5, Column 1
${month} → Row 5, Column 2
${year}  → Row 5, Column 3

For example, if Excel contains:

The variables will contain:

${day}   = 20
${month} = April
${year}  = 2027

Converting Excel Values to Strings

Excel may return numeric values as numbers.

For example:

20
2027

However, a web application’s dropdown may expect the value as a string.

Therefore, we can explicitly convert the values to strings:

${StringDay}      Convert To String    ${day}
${StringMonth}    Convert To String    ${month}
${StringYear}     Convert To String    ${year}

Now the values can be used as:

${StringDay}   = "20"
${StringMonth} = "April"
${StringYear}  = "2027"

This can be useful when the dropdown option text is represented as a string in the web application.

Selecting Dropdown Values Using Excel Data

After reading the data from Excel, we can use the values to select the dropdown options.

Select Options By
...    //div[4]/div/select[1]
...    text
...    ${StringDay}

The complete test case can look like this:

*** Test Cases ***
SelectDateFromExcelData
    # Read specific row from Excel
    ${day}    Read DropDown Data
    ...       ${CURDIR}${/}Data${/}Data.xlsx
    ...       date
    ...       5
    ...       1
    ${month}    Read DropDown Data
    ...         ${CURDIR}${/}Data${/}Data.xlsx
    ...         date
    ...         5
    ...         2
    ${year}    Read DropDown Data
    ...        ${CURDIR}${/}Data${/}Data.xlsx
    ...        date
    ...        5
    ...        3
    # Convert Excel values to String
    ${StringDay}      Convert To String    ${day}
    ${StringMonth}    Convert To String    ${month}
    ${StringYear}     Convert To String    ${year}
    # Scroll to the required section
    Scroll To Element    id=Email
    # Select Day
    Select Options By
    ...    //div[4]/div/select[1]
    ...    text
    ...    ${StringDay}
    # Select Month
    Select Options By
    ...    //div[4]/div/select[2]
    ...    text
    ...    ${StringMonth}
    # Select Year
    Select Options By
    ...    //div[4]/div/select[3]
    ...    text
    ...    ${StringYear}

The test case is now data-driven.

The test logic remains the same, but the values are obtained from Excel.

How the Complete Data Flow Works

The execution flow looks like this:

Excel File
    ↓
Data.xlsx
    ↓
date Sheet
    ↓
Read Row 5
    ↓
Day = 20
Month = April
Year = 2027
    ↓
Convert Values to String
    ↓
Select Day Dropdown
    ↓
Select Month Dropdown
    ↓
Select Year Dropdown

The test case does not need to hardcode:

20
April
2027

Instead, it reads the values dynamically from Excel.

Running the Same Test with Different Excel Rows

One of the biggest advantages of data-driven testing is that the same test logic can be used with different rows.

For example:

Row 2 → 1, January, 2024
Row 3 → 10, February, 2025
Row 4 → 15, March, 2026
Row 5 → 20, April, 2027

Changing the row number:

${day}    Read DropDown Data
...       ${filepath}
...       date
...       5
...       1

to:

${day}    Read DropDown Data
...       ${filepath}
...       date
...       4
...       1

allows the test to use a different set of data.

The automation code does not need to change.

A More Maintainable Approach: Store the File Path in a Variable

Instead of repeating the Excel file path multiple times, we can define it once.

*** Variables ***
${EXCEL_FILE}    ${CURDIR}${/}Data${/}Data.xlsx

The test case becomes easier to read:

*** Test Cases ***
SelectDateFromExcelData
    ${day}    Read DropDown Data
    ...       ${EXCEL_FILE}
    ...       date
    ...       5
    ...       1
    ${month}    Read DropDown Data
    ...         ${EXCEL_FILE}
    ...         date
    ...         5
    ...         2
    ${year}    Read DropDown Data
    ...        ${EXCEL_FILE}
    ...        date
    ...        5
    ...        3

This is better because if the Excel file location changes, we only need to update one variable.

Why Use ${CURDIR}${/}?

The following expression:

${CURDIR}${/}Data${/}Data.xlsx

is useful for creating a platform-independent file path.

${CURDIR} represents the directory of the current Robot Framework file.

${/} represents the operating system’s path separator.

For example, the path may become:

Windows:
C:\Project\Data\Data.xlsx

or:

Linux/macOS:
Project/Data/Data.xlsx

This makes the test framework more portable across operating systems.

Adding Logging for Debugging

When working with external test data, logging the values can be helpful.

Log    Day: ${StringDay}
Log    Month: ${StringMonth}
Log    Year: ${StringYear}

Or:

Log To Console    Selected Date: ${StringDay}-${StringMonth}-${StringYear}

Example output:

Selected Date: 20-April-2027

This makes it easier to troubleshoot problems when the Excel data is incorrect or the dropdown selection fails.

Important Consideration: Excel Data Types

One common issue when reading Excel data is the difference between numeric and string values.

For example, Excel may store:

20

as a numeric value.

The web application may expect:

"20"

Therefore, converting the value is sometimes necessary:

${StringDay}    Convert To String    ${day}

This is especially useful when working with:

• Day dropdowns
• Month values
• Year values
• IDs
• Zip codes
• Numeric text fields
• Dropdown option values

A Reusable Keyword for Selecting the Complete Date

We can also move the complete date selection logic into a reusable keyword.

Select Date From Excel
    [Arguments]    ${filepath}    ${sheetname}    ${rownum}
    ${day}    Read DropDown Data
    ...       ${filepath}
    ...       ${sheetname}
    ...       ${rownum}
    ...       1
    ${month}    Read DropDown Data
    ...         ${filepath}
    ...         ${sheetname}
    ...         ${rownum}
    ...         2
    ${year}    Read DropDown Data
    ...        ${filepath}
    ...        ${sheetname}
    ...        ${rownum}
    ...        3
    ${StringDay}      Convert To String    ${day}
    ${StringMonth}    Convert To String    ${month}
    ${StringYear}     Convert To String    ${year}
    Select Options By
    ...    //div[4]/div/select[1]
    ...    text
    ...    ${StringDay}
    Select Options By
    ...    //div[4]/div/select[2]
    ...    text
    ...    ${StringMonth}
    Select Options By
    ...    //div[4]/div/select[3]
    ...    text
    ...    ${StringYear}

The test case becomes much simpler:

*** Test Cases ***
SelectDateFromExcelData
    Select Date From Excel
    ...    ${EXCEL_FILE}
    ...    date
    ...    5

This is a good example of separating:

Test Data
    ↓
Excel File
Test Logic
    ↓
Reusable Robot Framework Keyword
Test Case
    ↓
Calls the Reusable Keyword

Advantages of Excel-Based Data-Driven Testing

Using Excel as a test data source provides several advantages.

1. Test Data Is Separated from Test Logic

The test case does not need to contain hardcoded values.

This makes the automation easier to maintain.

2. Multiple Test Scenarios Can Be Created Easily

New test data can be added to Excel without changing the automation code.

3. Non-Technical Users Can Modify Test Data

Excel is familiar to most users, including:

• QA testers
• Business analysts
• Product owners
• Manual testers

4. The Same Test Can Be Reused

The same automation logic can be executed with multiple rows of data.

5. Test Data Is Easier to Review

A business user can easily review a spreadsheet containing test scenarios.

Potential Improvements to This Approach

The current approach works well for small and medium-sized test suites.

For larger frameworks, consider the following improvements:

Use Named Variables

Instead of:

5
1

you can define:

${TEST_ROW}       5
${DAY_COLUMN}     1
${MONTH_COLUMN}   2
${YEAR_COLUMN}    3

This makes the code easier to understand.

Avoid Repeated Excel File Operations

The current keyword opens and closes the Excel document for every cell read.

For example:

Open Excel
Read Day
Close Excel
Open Excel
Read Month
Close Excel
Open Excel
Read Year
Close Excel

For a small test this may be acceptable.

For larger test suites, a more efficient approach is to:

1. Open the Excel document once.
2. Read all required values.
3. Close the document after all data has been read.

Use Stable Locators

The following XPath:

//div[4]/div/select[1]

may be fragile if the HTML structure changes.

Prefer stable locators whenever possible:

id=day
id=month
id=year

or:

css=select[name="day"]
css=select[name="month"]
css=select[name="year"]

Stable locators make UI automation more reliable.

Conclusion

Robot Framework can easily support data-driven testing by separating test data from test logic.

In this example:

Excel File
    ↓
ExcelLibrary
    ↓
Reusable Robot Framework Keyword
    ↓
Read Day, Month, and Year
    ↓
Convert Values to String
    ↓
Select Web Dropdown Options

The main advantage is that the test logic does not need to change when the test data changes.

Instead of creating separate test cases for every date combination, we can maintain the data in Excel and reuse the same automation workflow.

This approach can be particularly useful for testing:

• Date selection
• Registration forms
• Login scenarios
• Search forms
• Multiple user combinations
• Different input combinations
• Positive and negative test scenarios

The key principle is simple:

Keep the test logic in Robot Framework and keep the test data in Excel.

That separation makes the automation framework more maintainable, reusable, and scalable.

Related Links

• What is Robot Framework?
• Framework Architecture
• Installation
• Installing Browser Library
• Browser Library vs SeleniumLibrary
• Writing Your First Browser Test
• Sections in Robot Test File
• Robot Resource Files
• Robot Test Templates
• Robot Test Setup & TearDown
• Browser Library Keywords