We often need to import data from a CSV file to populate a relational database table. Most of the examples I have seen use the StringTokenizer to import a CSV file but that has its limitations such as if you come across data that has a comma in them. That's when I found this open source project called opencsv. It's a very simple csv (comma-separated values) parser library for Java. It takes care of the basic stuff such as comma inside the data itself and more. Also you can use the library to create CSV files. In addition to all that the best part is take a CSV file and map to a Java Bean. Here is the site link for more information and to download the jar file for this project - http://opencsv.sourceforge.net/
Parse a CSV file in Java using opencsv
Parse a CSV file in Java using opencsv
Sample Java program that parses a CSV file
package com.as400samplecode;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.List;
import java.util.StringTokenizer;
import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;
import au.com.bytecode.opencsv.bean.CsvToBean;
public class ParseCSVFile {
public static void main(String[] args) {
String filename = args[0];
ParseCSVFile parseCSVFile = new ParseCSVFile();
System.out.println("Starting to parse CSV file using StringTokenizer");
parseCSVFile.parseUsingStringTokenizer(filename);
System.out.println("Starting to parse CSV file using opencsv");
parseCSVFile.parseUsingOpenCSV(filename);
System.out.println("Starting to parse CSV file and map to Java Bean");
parseCSVFile.parseCSVtoBean(filename);
}
private void parseUsingStringTokenizer(String filename){
try {
//create BufferedReader to read CSV file
BufferedReader br = new BufferedReader( new FileReader(filename));
String record = "";
StringTokenizer st = null;
int rowNumber = 0;
int cellIndex = 0;
//read comma separated file line by line
while( (record = br.readLine()) != null)
{
rowNumber++;
//break comma separated line using ","
st = new StringTokenizer(record, ",");
while(st.hasMoreTokens()) {
//display CSV values
cellIndex++;
System.out.println("Cell column index: " + cellIndex);
System.out.println("Cell Value: " + st.nextToken());
System.out.println("---");
}
//reset cell Index number
cellIndex = 0;
}
}
catch(Exception e){
System.out.println("Exception while reading csv file: " + e);
}
}
private void parseUsingOpenCSV(String filename){
try {
CSVReader reader = new CSVReader(new FileReader(filename));
String [] nextLine;
int rowNumber = 0;
while ((nextLine = reader.readNext()) != null) {
rowNumber++;
for(int i = 0;i< nextLine.length; i++){
//display CSV values
System.out.println("Cell column index: " + i);
System.out.println("Cell Value: " + nextLine[i]);
System.out.println("---");
}
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void parseCSVtoBean(String filename){
try {
CSVReader reader = new CSVReader(new FileReader(filename), ',', '\"', 2);
ColumnPositionMappingStrategy<OrderDetail> mappingStrategy =
new ColumnPositionMappingStrategy<OrderDetail>();
mappingStrategy.setType(OrderDetail.class);
// the fields to bind do in your JavaBean
String[] columns = new String[] {"itemNumber", "description", "price","quantity"};
mappingStrategy.setColumnMapping(columns);
CsvToBean<OrderDetail> csv = new CsvToBean<OrderDetail>();
List<OrderDetail> orderList = csv.parse(mappingStrategy, reader);
for(int i = 0;i< orderList.size(); i++){
OrderDetail orderDetail = orderList.get(i);
//display CSV values
System.out.println("Item Number: " + orderDetail.getItemNumber());
System.out.println("Description: " + orderDetail.getDescription());
System.out.println("Price: " + orderDetail.getPrice());
System.out.println("Quantity: " + orderDetail.getQuantity());
System.out.println("---");
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Java Bean - OrderDetail.java
ackage com.as400samplecode;
public class OrderDetail {
String itemNumber = null;
String description = null;
Double price = 0.0;
int quantity = 0;
public String getItemNumber() {
return itemNumber;
}
public void setItemNumber(String itemNumber) {
this.itemNumber = itemNumber;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
}
Parse a CSV file in Java using opencsv
Parse a CSV file in Java using opencsv
Sample Java program that parses a CSV file
package com.as400samplecode;
import java.io.BufferedReader;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.util.List;
import java.util.StringTokenizer;
import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;
import au.com.bytecode.opencsv.bean.CsvToBean;
public class ParseCSVFile {
public static void main(String[] args) {
String filename = args[0];
ParseCSVFile parseCSVFile = new ParseCSVFile();
System.out.println("Starting to parse CSV file using StringTokenizer");
parseCSVFile.parseUsingStringTokenizer(filename);
System.out.println("Starting to parse CSV file using opencsv");
parseCSVFile.parseUsingOpenCSV(filename);
System.out.println("Starting to parse CSV file and map to Java Bean");
parseCSVFile.parseCSVtoBean(filename);
}
private void parseUsingStringTokenizer(String filename){
try {
//create BufferedReader to read CSV file
BufferedReader br = new BufferedReader( new FileReader(filename));
String record = "";
StringTokenizer st = null;
int rowNumber = 0;
int cellIndex = 0;
//read comma separated file line by line
while( (record = br.readLine()) != null)
{
rowNumber++;
//break comma separated line using ","
st = new StringTokenizer(record, ",");
while(st.hasMoreTokens()) {
//display CSV values
cellIndex++;
System.out.println("Cell column index: " + cellIndex);
System.out.println("Cell Value: " + st.nextToken());
System.out.println("---");
}
//reset cell Index number
cellIndex = 0;
}
}
catch(Exception e){
System.out.println("Exception while reading csv file: " + e);
}
}
private void parseUsingOpenCSV(String filename){
try {
CSVReader reader = new CSVReader(new FileReader(filename));
String [] nextLine;
int rowNumber = 0;
while ((nextLine = reader.readNext()) != null) {
rowNumber++;
for(int i = 0;i< nextLine.length; i++){
//display CSV values
System.out.println("Cell column index: " + i);
System.out.println("Cell Value: " + nextLine[i]);
System.out.println("---");
}
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
private void parseCSVtoBean(String filename){
try {
CSVReader reader = new CSVReader(new FileReader(filename), ',', '\"', 2);
ColumnPositionMappingStrategy<OrderDetail> mappingStrategy =
new ColumnPositionMappingStrategy<OrderDetail>();
mappingStrategy.setType(OrderDetail.class);
// the fields to bind do in your JavaBean
String[] columns = new String[] {"itemNumber", "description", "price","quantity"};
mappingStrategy.setColumnMapping(columns);
CsvToBean<OrderDetail> csv = new CsvToBean<OrderDetail>();
List<OrderDetail> orderList = csv.parse(mappingStrategy, reader);
for(int i = 0;i< orderList.size(); i++){
OrderDetail orderDetail = orderList.get(i);
//display CSV values
System.out.println("Item Number: " + orderDetail.getItemNumber());
System.out.println("Description: " + orderDetail.getDescription());
System.out.println("Price: " + orderDetail.getPrice());
System.out.println("Quantity: " + orderDetail.getQuantity());
System.out.println("---");
}
} catch (FileNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
Java Bean - OrderDetail.java
ackage com.as400samplecode;
public class OrderDetail {
String itemNumber = null;
String description = null;
Double price = 0.0;
int quantity = 0;
public String getItemNumber() {
return itemNumber;
}
public void setItemNumber(String itemNumber) {
this.itemNumber = itemNumber;
}
public String getDescription() {
return description;
}
public void setDescription(String description) {
this.description = description;
}
public Double getPrice() {
return price;
}
public void setPrice(Double price) {
this.price = price;
}
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
}
