How to Read Data from Excel or CSV for Selenium Test Automation

In test automation with Selenium, reading test data from external files like Excel or CSV helps to run data-driven tests efficiently. Here's how you can achieve this in popular programming languages like Java or Python.

1. Reading Data from CSV Files

CSV (Comma-Separated Values) files are simple text files and easy to read.

Java Example (Using OpenCSV Library):

Add OpenCSV dependency (Maven):

xml

Copy

Edit

<dependency>

    <groupId>com.opencsv</groupId>

    <artifactId>opencsv</artifactId>

    <version>5.7.1</version>

</dependency>

Read CSV file:

java

Copy

Edit

import com.opencsv.CSVReader;

import java.io.FileReader;

public class CSVDataReader {

    public static void main(String[] args) throws Exception {

        CSVReader reader = new CSVReader(new FileReader("testdata.csv"));

        String[] nextLine;

        while ((nextLine = reader.readNext()) != null) {

            // nextLine[] is an array of values from the line

            System.out.println("Username: " + nextLine[0] + ", Password: " + nextLine[1]);

        }

        reader.close();

    }

}

Python Example (Using pandas or csv module):

python

Copy

Edit

import csv

with open('testdata.csv', newline='') as csvfile:

    datareader = csv.reader(csvfile)

    for row in datareader:

        print(f"Username: {row[0]}, Password: {row[1]}")

Or using pandas:

python

Copy

Edit

import pandas as pd

df = pd.read_csv('testdata.csv')

for index, row in df.iterrows():

    print(f"Username: {row['username']}, Password: {row['password']}")

2. Reading Data from Excel Files

Excel files (.xls or .xlsx) are more complex but allow for richer data and formatting.

Java Example (Using Apache POI Library):

Add Apache POI dependencies (Maven):

xml

Copy

Edit

<dependency>

    <groupId>org.apache.poi</groupId>

    <artifactId>poi-ooxml</artifactId>

    <version>5.2.3</version>

</dependency>

Read Excel file:

java

Copy

Edit

import org.apache.poi.ss.usermodel.*;

import java.io.File;

import java.io.FileInputStream;

public class ExcelDataReader {

    public static void main(String[] args) throws Exception {

        FileInputStream fis = new FileInputStream(new File("testdata.xlsx"));

        Workbook workbook = WorkbookFactory.create(fis);

        Sheet sheet = workbook.getSheetAt(0);

        for (Row row : sheet) {

            Cell usernameCell = row.getCell(0);

            Cell passwordCell = row.getCell(1);

            String username = usernameCell.getStringCellValue();

            String password = passwordCell.getStringCellValue();

            System.out.println("Username: " + username + ", Password: " + password);

        }

        workbook.close();

        fis.close();

    }

}

Python Example (Using pandas or openpyxl):

Using pandas (which uses openpyxl under the hood):

python

Copy

Edit

import pandas as pd

df = pd.read_excel('testdata.xlsx')

for index, row in df.iterrows():

    print(f"Username: {row['username']}, Password: {row['password']}")

Using openpyxl:

python

Copy

Edit

from openpyxl import load_workbook

wb = load_workbook('testdata.xlsx')

sheet = wb.active

for row in sheet.iter_rows(min_row=2, values_only=True):  # Skip header row

    username, password = row

    print(f"Username: {username}, Password: {password}")

3. Integrating with Selenium Tests

After reading data, you can pass it to your Selenium test cases. For example:

Loop through each row of test data.

Use the data to fill input fields and perform test actions.

Validate results based on inputs.

Summary

File Type Libraries/Tools Language Usage

CSV OpenCSV (Java), csv, pandas (Python) Java/Python Simple, fast reading of comma-separated data

Excel Apache POI (Java), pandas, openpyxl (Python) Java/Python Richer format, multiple sheets, data types

Learn Selenium Python Training in Hyderabad

Read More

Selenium with Python for Testing Login Pages

Taking Screenshots with Selenium WebDriver in Python

How to Handle Multiple Browser Tabs or Windows in Selenium

Waits in Selenium: Implicit vs Explicit Waits Explained

Visit Our Quality Thought Training in Hyderabad

Get Directions