Pages Navigation Menu

Coding is much easier than you think

Read / Write CSV file in Java using opencsv library

Posted by on Oct 10, 2012 in CSV, Java | 7 comments

csv
 
We often need to read data from a CSV file to do some manipulation. Most of the examples I have seen uses StringTokenizer to read the CSV file but that has certain limitations. It cannot read a CSV file properly if the data present in the CSV has a comma in them. We can avoid this issue by using a open source project called Opencsv
 
You can use this library to Create, Read and write CSV files. The best part of OpenCSV parser is, it takes a CSV file and map the result data to a Java Bean object.
 
Please download and add Open Csv.jar in your class path.
 
Let’s get started.
 
1. Reading CSV file in Java

We will use following CSV sample file for this example:
 
File: Book.csv

Product No:, Product Name :,Price :,Quantity :
1,"Core Java, A cumulative approach",300 $,30
2,Complete reference,200 $,30
3,Struts 2 Black Book,85 $,30

 
File : ParseCSVFile.java
 

package com.simplecode.csv;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import au.com.bytecode.opencsv.CSVReader;

public class ParseCSVFileReadLineByLine
{
public static void main(String[] args) 
{
	String filename = "C:\Book1.csv";
	ParseCSVFileReadLineByLine parseCSVFile = new ParseCSVFileReadLineByLine();
	
	System.out.println("Starting to parse CSV file using opencsv");
	parseCSVFile.parseUsingOpenCSV(filename);
}

private void parseUsingOpenCSV(String filename) 
{
	CSVReader reader;
	try 
	{
	reader = new CSVReader(new FileReader(filename));
	String[] row;

	while ((row = reader.readNext()) != null) 
	{
		for (int i = 0; i < row.length; i++) 
		{
			// display CSV values
			System.out.println("Cell column index: " + i);
			System.out.println("Cell Value: " + row[i]);
			System.out.println("-------------");
		}
	}
	} 
	catch (FileNotFoundException e) 
	{
		System.err.println(e.getMessage());
	}
	catch (IOException e) 
	{
		System.err.println(e.getMessage());
	}
}
}

 
In above code, the readNext() method of CSVReader class to read CSV file line by line. It returns a String array for each value in row.
 

Recommended reading:

 
It is also possible to read full CSV file entierly by using readAll() method.
 

package com.simplecode.csv;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.List;

import au.com.bytecode.opencsv.CSVReader;

public class ParseCSVFileReadAll 
{

public static void main(String[] args) 
{
	String filename = "C:\Book.csv";
	ParseCSVFileReadAll parseCSVFile = new ParseCSVFileReadAll();

	System.out.println("Starting to parse CSV file using opencsv");
	parseCSVFile.parseUsingOpenCSV(filename);
}

private void parseUsingOpenCSV(String filename) 
 {
 CSVReader reader;
  try 
  {
	reader = new CSVReader(new FileReader(filename));
	String[] row;
	List<?> content = reader.readAll();

	for (Object object : content) 
	{
		row = (String[]) object;
		for (int i = 0; i < row.length; i++) 
		{
			// display CSV values
		System.out.println("Cell column index: " + i);
		System.out.println("Cell Value: " + row[i]);
		System.out.println("-------------");
		}
	}
  }
  catch (FileNotFoundException e) 
  {
    System.err.println(e.getMessage());
  }
  catch (IOException e) 
  {
   System.err.println(e.getMessage());
  }
  }
}

 
The readAll() method returns a List of String[] for given CSV file.
 

Also Read:

 
2. Mapping CSV with Java beans

OpenCSV parser take a CSV file and map the result data to a Java Bean object. For example we created a Java bean to store Product information.
 
File : ProductDetail.java
 

package com.simplecode.csv;

public class ProductDetail 
{
	String productNumber = null;
	String productName = null;
	String price = null;
	String quantity = null;

	public String getProductNumber() 
	{
		return productNumber;
	}

	public void setProductNumber(String productNumber) 
	{
		this.productNumber = productNumber;
	}

	public String getProductName() 
	{
		return productName;
	}

	public void setProductName(String productName) 
	{
		this.productName = productName;
	}

	public String getPrice() 
	{
		return price;
	}

	public void setPrice(String price) 
	{
		this.price = price;
	}

	public String getQuantity() 
	{
		return quantity;
	}

	public void setQuantity(String quantity) 
	{
		this.quantity = quantity;
	}

}

 
Now we can map this bean with Opencsv and read the CSV file. Check out below example file:
 
File : ParseCSV2Bean.java

package com.simplecode.csv;

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.util.List;
import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;
import au.com.bytecode.opencsv.bean.CsvToBean;

public class ParseCSV2Bean 
{
	public static void main(String[] args) 
	{
		String filename = "C:\Book1.csv";
	ParseCSV2Bean parseCSVFile = new ParseCSV2Bean();

	System.out.println("Starting to parse CSV file and map to Java Bean");
	parseCSVFile.parseCSVtoBean(filename);
}

private void parseCSVtoBean(String filename) 
{
	try 
	{
	// To ignore Processing of 1st row
	CSVReader reader = new CSVReader(new FileReader(filename), ',', '\"', 1);

	ColumnPositionMappingStrategy<ProductDetail> mappingStrategy 
	                         = new ColumnPositionMappingStrategy<ProductDetail>();
	mappingStrategy.setType(ProductDetail.class);

	// the fields to bind do in your JavaBean
	String[] columns = new String[] {"productNumber","productName","price","quantity"};
	mappingStrategy.setColumnMapping(columns);

	CsvToBean<ProductDetail> csv = new CsvToBean<ProductDetail>();
	List<ProductDetail> productList = csv.parse(mappingStrategy, reader);
	
	for (int i = 0; i < productList.size(); i++) 
	{
		ProductDetail productDetail = productList.get(i);
		// display CSV values
		System.out.println("Product No : " + productDetail.getProductNumber());
		System.out.println("Product Name : " + productDetail.getProductName());
		System.out.println("Price: " + productDetail.getPrice());
		System.out.println("Quandity: " + productDetail.getQuantity());
		System.out.println("------------------------------");
		}

	}
	catch (FileNotFoundException e) 
	{
	   System.err.println(e.getMessage());
	}
}
}

 
The methodsetColumnMapping is used to map individual property of Java bean to the CSV position. In this example we map first CSV value to productNumber attribute and next to productName, and 3rd and 4th as price and quantity.
 
3. Writing CSV file in Java
 
Simple example to write one line in CSV file.
 

String csv = "C:\output.csv";
CSVWriter writer = new CSVWriter(new FileWriter(csv));

String [] fruits= "Apple,Orange,PineApple".split(",");
writer.writeNext(fruits);
writer.close();

 
We created object of class CSVWriter and called its writeNext() method. The writeNext() methods takes String [] as argument.

You can also write a List of String[] to CSV entirely. Following is code snippet for that.
 

String csv = "C:\output.csv";
CSVWriter writer = new CSVWriter(new FileWriter(csv));
 
List<String[]> database = new ArrayList<String[]>();
database.add(new String[] {"DB2", "Enterprise Database"});
database.add(new String[] {"PostgreSQL", "Open Source Enterprise Database"});
database.add(new String[] {"MySQL", "Open Source Database"});
 
writer.writeAll(database); 
writer.close();

 
We used writeAll() method of class CSVWriter to write a List of String[] as CSV file.
 
4. Dumping SQL Table as CSV

OpenCSV also provides support to dump data from SQL table directly to CSV. For this we need ResultSet object. Following API can be used to write data to CSV from ResultSet.
 

java.sql.ResultSet rSet = getResultSet();
writer.writeAll(rSet, includeTableName);

 
The writeAll(ResultSet, boolean) method is utilized for this. The first argument is the ResultSet which you want to write to CSV file. And the second argument is boolean which represents whether you want to write table column names to file or not.
 
download
 
5. Reference

Read More