Tuesday, May 24, 2011

Reading Delimited Files Using FlatPack

FlatPack is a java tool box which supports you to write & read delimited files. This is a sample java code that i have tried out using FlatPack to delimited my .txt file which contains the columns names as the first record.

This sample code will describe you how to insert the content of a delimited file into a database. I have tried out my code in netbeans. You should import all the dependency jar files in to your project (to lib folder) before you run this code. Download dependency files flatpack.jar, jdom.jar & slf4j-api.jar.

Sample .txt file (test.txt)
"JOHN","ANAME","1234 CIRCLE CT","ELYRIA","OH","44035"
"JIMMY","ZNAME","180 SOME ST","AVON","OH","44011"
"JANE","ANAME","111 MILKY WY","AVON","OH","44001"
"FRED","ZNAME","123 ROCKY WY","ELYRIA","OH","12345"
"FRED","ZNAME","123 ROCKY WY","ELYRIA","OH","12345"

FileReader.java file
import java.io.File;
import net.sf.flatpack.DataSet;
import net.sf.flatpack.DefaultParserFactory;
import net.sf.flatpack.Parser;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;

public class FileReader {

    static Connection con;

    public static void main(String args[]) throws Exception {

        con = DriverManager.getConnection("jdbc:derby:myDB;create=true;user=admin;password=admin");//enter details of your database
        Statement stmt = con.createStatement();
        //create a new table in "myDB" database
        stmt.executeUpdate("CREATE TABLE USER_NAMES (first_name VARCHAR(32) NOT NULL, last_name VARCHAR(32) NOT NULL)");

        //.txt file type used here, comma delimted with text qualified by "'s
        Parser pzparser = DefaultParserFactory.getInstance().
                newDelimitedParser(new File("test.txt"), ',', '"');
        final DataSet ds = pzparser.parse();

        while (ds.next()) {
            //insert values into database by using the column names of the .txt file
            PreparedStatement psInsert = con.prepareStatement("insert into USER_NAMES values (?,?)");
            psInsert.setString(1, ds.getString("FIRSTNAME"));
            psInsert.setString(2, ds.getString("LASTNAME"));
If you need to print records inside the "USER_NAMES" table, you just need to copy following code snippet after the while loop.
Statement ss = con.createStatement();
        ResultSet rs = ss.executeQuery("select * from USERS");
        System.out.println("First Name | Last Name ");
        while (rs.next()) {
            System.out.println(rs.getString(1) + "\t" + rs.getString(2));

Hope this post will be a useful one for you all. :)

No comments:

Post a Comment