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

https://the-internet.herokuapp.com/login

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");
      });
    });
  });
});
Voila – Dynamically generated tests from Excel or CSV files! Enjoy

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

To convert Excel files to JSON

make convertXLStoJSON or npm run convertXLStoJSON

  • File:- testData/convertXLStoJSON.ts
  • Input:- testData/testData.xlsx
  • Output:- cypress/fixtures/testData.json

To convert CSV to JSON

make convertCSVtoJSON or yarn run convertCSVtoJSON

  • File:- testData/convertCSVtoJSON.ts
  • Input:- testData/testData.csv
  • Output:- cypress/fixtures/testDataFromCSV.json

To see the test in action

  • export CYPRESS_SUT_URL=https://the-internet.herokuapp.com
  • npx cypress open --env configFile=development or make test-local-gui

Open the script login.spec.ts which will generate a test for every entry in the CSV or XLS (default) file.

If you wish to read from the CSV, in the file cypress/integration/login.spec.ts

Change const testData = require("../fixtures/testData.json"); to

const testData = require("../fixtures/testDataFromCSV.json");

Published by

YOU54F

I have been a Software Test Engineer for 11+ years now, starting off in Accessibility Testing at a large UK Banking organisation. I have since worked with financial / medical healthcare / betting / telecommunication providers testing software and along the way helping some migrate from traditional software development methodologies to a leaner Agile based approach. I now work for a consultancy company, providing insight into tools / technologies / approaches to ensure we are testing the right things, at the right place in the stack. Less of my time these days is spent writing test code, and more time championing test processes with our developers, who have picked it up and hit the ground running.

2 thoughts on “Dynamically generate data in Cypress from CSV/XLSX”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.