Dynamically generate data in Cypress from CSV/XLSX

A quick walkthrough on how to use data from Excel spreadsheets or CSV files, in order to dynamically generate multiple Cypress tests.

We are going to use a 2 column table witht username & password for our example, but in reality this could be any data. We have the following table in csv & xlsx format.

username	password
User1	Password1
User2	Password2
User3	Password3
User4	Password4
User5	Password5
User6	Password6
User7	Password7
User8	Password8
User9	Password9
User10	Password10

And we are going to login into the following page

First we need to convert our XLSX file to JSON with https://github.com/SheetJS/js-xlsx

import { writeFileSync } from "fs";
import * as XLSX from "xlsx";
try {
  const workBook = XLSX.readFile("./testData/testData.xlsx");
  const jsonData = XLSX.utils.sheet_to_json(workBook.Sheets.testData);
  writeFileSync(
    "./cypress/fixtures/testData.json",
    JSON.stringify(jsonData, null, 4),
    "utf-8"
  );
} catch (e) {
  throw Error(e);
}

or CSV file to JSON with https://www.papaparse.com/

import { readFileSync, writeFileSync } from "fs";
import { parse } from "papaparse";
try {
  const csvFile = readFileSync("./testData/testData.csv", "utf8");
  const csvResults = parse(csvFile, {
    header: true,
    complete: csvData => csvData.data
  }).data;
  writeFileSync(
    "./cypress/fixtures/testDataFromCSV.json",
    JSON.stringify(csvResults, null, 4),
    "utf-8"
  );
} catch (e) {
  throw Error(e);
}

In our cypress test file, we are going to

1. Import our generated JSON file into testData
2. Loop over each testDataRow, inside the describe block, and set the data object with our username & password
3. Setup a mocha context with a dynamically generated title, unique for each data row
4. A single test is written inside the it block using our data attributes, this will be executed as 10 separate tests

import { login } from "../support/pageObjects/login.page";
const testData = require("../fixtures/testData.json");
describe("Dynamically Generated Tests", () => {
  testData.forEach((testDataRow: any) => {
    const data = {
      username: testDataRow.username,
      password: testDataRow.password
    };
    context(`Generating a test for ${data.username}`, () => {
      it("should fail to login for the specified details", () => {
        login.visit();
        login.username.type(data.username);
        login.password.type(`${data.password}{enter}`);
        login.errorMsg.contains("Your username is invalid!");
        login.logOutButton.should("not.exist");
      });
    });
  });
});

You can extend this further by

• Manipulating the data in the test script, prior to using it in your test such as shifting date of birth by an offset
• Having different outcomes in your test or running different assertions based on a parameter in your test data file.

A full working example can be downloaded here:- https://github.com/YOU54F/cypress-dynamic-data

git clone git@github.com:YOU54F/cypress-docker-typescript.git

yarn install