• CSV Files in Selenium

CSV Files with selenium

A CSV (Comma Separated Values) file is a text based format that represents the data typically found in a spreadsheet or a database table.

It is a common format for data interchange as it is simple, compact, and used everywhere. It will open into Excel with a double click, and nearly all databases have a tool to allow import from CSV. It is also readily parseable with simple code.

The basic format is defined by rows of column data. Each row is terminated by a newline to begin the next row. Within the row, each column is distinguished by a comma.

Data within each column can be double-quoted to escape embedded commas in the data. Embedded double quotes are escaped with a pair of double-quote characters.

CSV files are also called flat files, as CSV files contain only simple formatted 2 Dimensional tables. Also, you do not need sophisticated software to handle CSV. You need a simple text editor to manipulate the CSV files.


We can handle the CSV file using the below packages :

  • Apache Commons CSV
  • Open CV

Read and Write Excel with Apache POI

Apache Commons CSV Integration

First of all, you need to add an apache-commons-csv dependency in your project. If you use maven, then add the following dependency to your pom.xml file.

<dependency>
    <groupId>org.apache.commons</groupId>
    <artifactId>commons-csv</artifactId>
    <version>1.5</version>
</dependency>


For Jar file, please go to : https://commons.apache.org/proper/commons-csv/download_csv.cgi, download the zip/tar.gz file under binaries section and add the jar into eclipse with External Jars.

Sample CSV Files

Please save the below CSV content in your local system with .csv extension.

CSV file without header

Name,Product,Description
karthiQ,chercher tech,tutorial
larry page,google,search engine
gates,microsoft,operating system


CSV file with a header

karthiQ,chercher tech,tutorial
larry page,google,search engine
gates,microsoft,operating system

Handle Nested frames in Selenium

Reading a CSV file with Column Index [ Apache Commons CSV]

We can read the CSV file using Apache Commons CSV packages; there could be two kinds of CSV files.

  • CSV file with Header
  • CSV file without Header

In this Program, I will be using the CSV file without header, follow below steps to read the CSV file using Apache Commons CSV package.

Steps to Read CSV file :

1. Create the CSV file and store it in your local machine.

2. Create a new BufferReader object, using the Files class and pass the above CSV file as a parameter.

// read the file
Reader reader = Files.newBufferedReader(Paths.get(CSV_File_Path));


3. Now All the details are stored in the BufferReader object. To parse the BufferReader object into comma-separated values, we have to create an object for CSV parser. We have to pass what we are going to parse, and what is the delimiter for parsing (separator)

// parse the file into csv values
CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT);


4. Now all the data is loaded into CSVparse object like a table like rows and columns; now, we have to iterate over every row present in it. We can access the column using the index, along with get() method

for (CSVRecord csvRecord : csvParser) {
		// Accessing Values by Column Index
		String name = csvRecord.get(0);
		String product = csvRecord.get(1);
		String description = csvRecord.get(2);

Complete program for reading CSV file with column index

import org.testng.annotations.Test;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVRecord;
import java.io.IOException;
import java.io.Reader;
import java.nio.file.Files;
import java.nio.file.Paths;
public class ApacheCommonsCSV {
	@Test
	public void readCSV() throws IOException {
		String CSV_File_Path = "C:UsersuserDesktopq.csv";
		// read the file
		Reader reader = Files.newBufferedReader(Paths.get(CSV_File_Path));
		// parse the file into csv values
		CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT);
        for (CSVRecord csvRecord : csvParser) {
            // Accessing Values by Column Index
			String name = csvRecord.get(0);
			String product = csvRecord.get(1);
			String description = csvRecord.get(2);
			// print the value to console
			System.out.println("Record No - " + csvRecord.getRecordNumber());
			System.out.println("---------------");
			System.out.println("Name : " + name);
			System.out.println("Product : " + product);
			System.out.println("Description : " + description);
			System.out.println("---------------
");
        }
	}
}

Read and Write Property files in Selenium

Reading a CSV file with Column Name [ Apache commons CSV]

In the above example, we have seen how to read the CSV file with an index of the column, but when we read like that, we may not get the exact information what row we are reading.

To make it more readable, we should create it with column names so that we can call the values by its column name.


We have to create headers to call the values with its name; We can set the header while creating the CSVParser.

For this example, I am using a CSV file without a header.

CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
	.withHeader("Name", "Product", "Description")
	.withIgnoreHeaderCase()
	.withTrim());


Complete program for reading CSV file with Column Names.

public class ReadWithColumnName {
	@Test
	public void readCSV() throws IOException {
		String CSV_File_Path = "C:UsersuserDesktopq.csv";
		// read the file
		Reader reader = Files.newBufferedReader(Paths.get(CSV_File_Path));
        CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                .withHeader("Name", "Product", "Description")
                .withIgnoreHeaderCase()
                .withTrim());
        for (CSVRecord csvRecord : csvParser) {
            // Accessing values by the names assigned to each column
            String name = csvRecord.get("Name");
            String product = csvRecord.get("Product");
            String description = csvRecord.get("Description");
            System.out.println("Record No - " + csvRecord.getRecordNumber());
            System.out.println("---------------");
            System.out.println("Name : " + name);
            System.out.println("Product : " + product);
            System.out.println("Description : " + description);
            System.out.println("---------------
");
        }
	}
}

Running TestNG from CMD

Reading a CSV file with Header Auto-detection.

In the last example, we have seen how to create Column names for the CSV files, but most of the time CSV files do come with headers.

When we have headers in the CSV file, we may need to convert them into column names, or we have to tell the CSV parse that this fils has Headers.

CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
	.withFirstRecordAsHeader()
	.withIgnoreHeaderCase()
	.withTrim());


Complete program for reading CSV files with headers

public class CSVWithHeader {
	@Test
	public void readCSV() throws IOException {
		String CSV_File_Path = "C:UsersuserDesktopq_header.csv";
		// read the file
		Reader reader = Files.newBufferedReader(Paths.get(CSV_File_Path));
        CSVParser csvParser = new CSVParser(reader, CSVFormat.DEFAULT
                .withFirstRecordAsHeader()
                .withIgnoreHeaderCase()
                .withTrim());
        for (CSVRecord csvRecord : csvParser) {
            // Accessing values by the names assigned to each column
            String name = csvRecord.get("Name");
            String product = csvRecord.get("Product");
            String description = csvRecord.get("Description");
            System.out.println("Record No - " + csvRecord.getRecordNumber());
            System.out.println("---------------");
            System.out.println("Name : " + name);
            System.out.println("Product : " + product);
            System.out.println("Description : " + description);
            System.out.println("---------------
");
        }
	}
}

Robot Class in Selenium

Generating a CSV file

Sometimes we may need to create the CSV files based on the values present in the website

Steps to Create a CSV file :

1. Create the BufferedWriter object for the file to which we are going to write our content. This file may or may not exist in the File system.

If File exists in the file system, then it will be replaced, if the file is not present, then the new file will be created with the given name.

String CSV_File_Path = "C:UsersuserDesktopq1.csv";
// writer
BufferedWriter writer = Files.newBufferedWriter(Paths.get(CSV_File_Path));


2. Create the object for the CSVPrinter class and also specify what is the delimiter and headers of the file.

CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
    .withHeader("ID", "Name", "Designation", "Company"));


3. Using the printRecord method, you can push the values into the file.

csvPrinter.printRecord("1", "karthiq", "admin", "chercher tech");


4. The flush() method pushes the file and its content into the local system. Complete program for writing the data into CSV files

import java.io.BufferedWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVPrinter;
import org.testng.annotations.Test;
public class WriteCSV {
	@Test
	public void writeCSVFileTest() throws IOException {
		String CSV_File_Path = "C:UsersuserDesktopq1.csv";
		BufferedWriter writer = Files.newBufferedWriter(Paths.get(CSV_File_Path));
        CSVPrinter csvPrinter = new CSVPrinter(writer, CSVFormat.DEFAULT
                .withHeader("ID", "Name", "Designation", "Company"));
        csvPrinter.printRecord("1", "karthiq", "admin", "chercher tech");
        csvPrinter.printRecord("2", "Jeff", "CEO", "Amazon");
        csvPrinter.printRecord("3", "Tim", "CEO", "Apple");
        csvPrinter.flush();
				csvPrinter.close();
	}
}

Listeners in Selenium

Convert Excel File into CSV file

public class ConvertExcelToCSV {
public static void xls(File inputFile, File outputFile)
{
    // For storing data into CSV files
    StringBuffer data = new StringBuffer();
    try
    {
    FileOutputStream fos = new FileOutputStream(outputFile);
    // Get the workbook object for XLS file
    HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inputFile));
    // Get first sheet from the workbook
    HSSFSheet sheet = workbook.getSheetAt(0);
    Cell cell;
    Row row;
    // Iterate through each rows from first sheet
    Iterator rowIterator = sheet.iterator();
    while (rowIterator.hasNext())
    {
            row = rowIterator.next();
            // For each row, iterate through each columns
            Iterator cellIterator = row.cellIterator();
            while (cellIterator.hasNext())
            {
                    cell = cellIterator.next();
                    switch (cell.getCellType())
                    {
                    case Cell.CELL_TYPE_BOOLEAN:
                            data.append(cell.getBooleanCellValue() + ",");
                            break;
                    case Cell.CELL_TYPE_NUMERIC:
                            data.append(cell.getNumericCellValue() + ",");
                            break;
                    case Cell.CELL_TYPE_STRING:
                            data.append(cell.getStringCellValue() + ",");
                            break;
                    case Cell.CELL_TYPE_BLANK:
                            data.append("" + ",");
                            break;
                    default:
                            data.append(cell + ",");
                    }
            }
            data.append('
');
    }
    fos.write(data.toString().getBytes());
    fos.close();
    }
    catch (FileNotFoundException e)
    {
            e.printStackTrace();
    }
    catch (IOException e)
    {
            e.printStackTrace();
    }
    }
    public static void main(String[] args)
    {
            File inputFile = new File("data.xls");
            File outputFile = new File("input.csv");
            xls(inputFile, outputFile);
    }
}

Compare Dropdown values

Recommended Readings

About Author :

I am Pavankumar, Having 8.5 years of experience currently working in Video/Live Analytics project.

Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions