Data-driven testing with DataProvider, CSV, JSON and Excel

Data-driven testing means one test method runs many times with different data. You write the login steps once and test ten users. TestNG supports this with the @DataProvider annotation. The data can live in code, but at work it usually lives in a file that testers and business people can edit without touching Java.

How a DataProvider works

A data provider is a method that returns Object[][]. Each inner array is one row, and its values become the arguments of the test method, in order. TestNG runs the test once per row. Every run is a separate test in the report, and a failing row does not stop the other rows. The @BeforeMethod and @AfterMethod of your base class also run once per row, so each row gets a fresh browser.

@DataProvider(name = "csvLogins")
public Object[][] csvLogins() throws Exception {
    return TestData.csv("logins.csv");
}

@Test(dataProvider = "csvLogins")
public void loginFromCsv(String user, String pass, String expected) {
    open("login.html");
    assertEquals(new LoginPage(driver()).loginAs(user, pass).message(), expected);
}

The number and types of columns must match the parameters of the test method. If a row has three values and the method has two parameters, the method fails at once with a data provider mismatch error, and the test steps never run.

Reading a CSV file

CSV is the simplest format: one row per line, values separated by commas, with a header line. The file below is stored in src/test/resources/testdata.

username,password,expected
asha,secret,Welcome asha
ravi,wrong,Invalid login
meena,secret,Welcome meena

public static Object[][] csv(String name) throws Exception {
    List<String> lines = Files.readAllLines(Path.of("src/test/resources/testdata", name));
    Object[][] rows = new Object[lines.size() - 1][];
    for (int i = 1; i < lines.size(); i++) rows[i - 1] = lines.get(i).split(",");
    return rows;
}

Splitting on commas is fine for simple data. If a value can contain a comma or quotes, use a CSV library such as OpenCSV or Apache Commons CSV.

Reading JSON

JSON suits data with names for each field. Jackson can read a JSON array into a list of maps. Then each row can be passed as one Map parameter, and the test reads values by name, which is easier to understand than column positions.

List<Map<String, String>> list = new ObjectMapper().readValue(
        new File("src/test/resources/testdata/logins.json"),
        new TypeReference<>() {});
Object[][] rows = new Object[list.size()][];
for (int i = 0; i < list.size(); i++) rows[i] = new Object[] { list.get(i) };
return rows;

A test with the signature loginFromJson(Map<String, String> row) now runs once per JSON object. In the report the names look like this:

loginFromCsv[asha, secret, Welcome asha](1)
loginFromCsv[ravi, wrong, Invalid login](2)
loginFromJson[{username=asha, password=secret, expected=Welcome asha}](1)

Reading Excel

Business teams like Excel. Apache POI reads .xlsx files. The important detail is the cell type. A cell that holds a number, such as a phone number, is numeric, and calling getStringCellValue on it throws IllegalStateException: Cannot get a STRING value from a NUMERIC cell. POI has a DataFormatter that returns the text exactly as Excel shows it, for any cell type.

DataFormatter fmt = new DataFormatter();
for (int r = 1; r <= sheet.getLastRowNum(); r++)
    for (int c = 0; c < cols; c++)
        rows[r - 1][c] = fmt.formatCellValue(sheet.getRow(r).getCell(c));

Always close the workbook, for example with try-with-resources, and give the sheet name as a parameter so one workbook can hold data for many tests.

Good habits

• Keep data files outside the Java code, in a testdata folder, and name them after the feature.
• Put several data providers in one class and share them with dataProviderClass on @Test.
• Add @DataProvider(parallel = true) only when every test thread has its own driver. The suite attribute data-provider-thread-count sets how many rows run together.
• Never commit real passwords or personal data in a data file. Use test accounts, and read secrets from environment variables.

Try it yourself

• Create logins.csv with four rows, including one wrong password, and run the test.
• Write the same data as JSON and read it into a Map parameter.
• Create an .xlsx file with one numeric cell and read it with both getStringCellValue and DataFormatter.
• Change the test method to have one parameter fewer than the columns and read the error.