Tuesday, October 2, 2012

Java CSV file Import and parsing using opencsv library

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;

    }

}

0 comments:

Post a Comment