CSV Files

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 a 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 as flat files, as CSV files contains only simple formated 2 Dimentional tables, Also you donot not shopisticated softwares to handle CSV. You just need simple text editor to manipulate the CSV files.


We can hanlde the CSV file using below packages :

  • Apache Commons CSV
  • Open CV

Apache Commons CSV Integration

First of all, you need to add 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 got 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 Externar 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 header


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

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 kind of CSV file.

  • 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 parameter.


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


3. Now All the details are stored in BufferReader object. To parse the BufferReader object into comma seperated values, we have to create object for CSV parser. We have to pass what we are going to parse, and what is the delimeter 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 as table like rows and columns, now we have to iterate over every row present in it. We can acces the column using 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 prgram 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:\\Users\\user\\Desktop\\q.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("---------------\n\n");
        }
	}
}			


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

In above example we have seen how to read the CSV file with 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 readabe 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 CSV file without header


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


Complete program for reading CSV file with Collumn Names.


public class ReadWithColumnName {
	@Test
	public void readCSV() throws IOException {
		String CSV_File_Path = "C:\\Users\\user\\Desktop\\q.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("---------------\n\n");
        }
	}
}			


Reading a CSV file with Header Auto-detection

In 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 CSV file, we may need to convert them into colum names or we have to tell the CSV parse that this fils has Headres.


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:\\Users\\user\\Desktop\\q_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("---------------\n\n");
        }
	}
}			


Generating a CSV file

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

Steps to Create 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 File system.

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


String CSV_File_Path = "C:\\Users\\user\\Desktop\\q1.csv";
// writer		
BufferedWriter writer = Files.newBufferedWriter(Paths.get(CSV_File_Path));			


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


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


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


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


4. The fulsh() method pushes the file and its content into 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:\\Users\\user\\Desktop\\q1.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();		
	}
}			


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('\n'); 
    }
    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);
    }
}			


About Author

Myself KarthiQ, I am the author of this blog, I know ways to write a good article but some how I donot have the skills to make it to reach people, would you like help me to reach more people By sharing this Article in the social media.

Share this Article Facebook
Comment / Suggestion Section
Point our Mistakes and Post Your Suggestions

Recent Addition

new tutorial Registrations for Selenium Online Training is Over.

Below are the training details:
Meeting link : https://zoom.us/j/737840591
Starting Time : 9:00PM 18th DEC 2018
 
Join My Facebook Group
Join Group