Sunday, 16 February 2014

How to read a CSV file using JDBC (in 4 lines!)

Here is a 4 line JDBC program that reads a csv file and prints records in it. You will not need lengthy IO code to do this if you have Windows operating system. Here I will be using Type 1 driver and all you need to do is just write queries.

First you need to add a system dsn in your Windows operating system.

1. Go to Control Panel > Switch to classic view (if not in classic view)
2. Go to Administrative Tools
3. In it Data Sources (ODBC)
4. Go to System DSN tab and then click Add
5. Select Microsoft Text Driver (*.txt;*.csv) and then click Finish
6. Now give a name in the first field and hit Enter. Here i gave the name google

CSV File

CSV stands for Comma separated values. In this data is stored in the form of a table but each cell in a row is separated by a comma and each record (row) is separated by a new line. The first line is the heading of the columns. Each column heading is separated by a comma. Here is a simple CSV file that I'll be using

sample1.csv



sno,sname,age


101,"smith",20


102,"scott",25

Here sno,sname,age are column headings and 101,"smith",20 is a record where 101 is sno, "smith" is sname and 20 is age.

csv1.java



import java.sql.*;
class csv1
{
    public static void main(String args[]) throws Exception
    {
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
   
    Connection c=DriverManager.getConnection("jdbc:odbc:google");
   
    Statement s=c.createStatement();

    ResultSet rs=s.executeQuery("select * from sample1.csv where sno=101");

    // to select all records
    // select *from sample1.csv
   
        while(rs.next())
        {
        System.out.println(rs.getObject(1)+"  "+rs.getObject(2)+"  "+rs.getObject(3));
        }
   
    // you can insert a record
    // here sample1.csv exists in current dir
    s.executeUpdate("insert into sample1.csv values(103,'scott',20)");
   
    s.close();
    c.close();
    }
}

Output of csv1



101  smith  20

Note: The problem here is that you cannot update a record or delete a record following this procedure.

No comments:

Post a Comment