CsvJdbc

a Java database driver for reading comma-separated-value files

About

CsvJdbc is a simple read-only JDBC driver that uses Comma Separated Value (CSV) files as database tables. It is ideal for writing data import programs or analyzing log files.

The driver enables you to access a directory or a ZIP file containing CSV files as if it were a database containing tables. As there is no real database management system behind the scenes, not all JDBC functionality is available.

Usage

The CsvJdbc driver is used just like any other JDBC driver:

• load the driver class, (its full name is org.relique.jdbc.csv.CsvDriver)
• use the DriverManager to get a connection to the database (the directory or ZIP file)
• create a statement object
• use the statement object to execute an SQL SELECT query
• the result of the query is a ResultSet

The following example puts all of the above steps into practice.

import java.sql.*;

public class DemoDriver
{
  public static void main(String[] args)
  {
    try
    {
      // load the driver into memory
      Class.forName("org.relique.jdbc.csv.CsvDriver");

      // create a connection. The first command line parameter is assumed to
      //  be the directory in which the .csv files are held
      Connection conn = DriverManager.getConnection("jdbc:relique:csv:" + args[0]);

      // create a Statement object to execute the query with
      Statement stmt = conn.createStatement();

      // Select the ID and NAME columns from sample.csv
      ResultSet results = stmt.executeQuery("SELECT ID,NAME FROM sample");

      // dump out the results
      while (results.next())
      {
        System.out.println("ID= " + results.getString("ID") +
                        "   NAME= " + results.getString("NAME"));
      }

      // clean up
      results.close();
      stmt.close();
      conn.close();
    }
    catch(Exception e)
    {
      e.printStackTrace();
    }
  }
}
      

To read several files (for example, daily log files) as a single table, set the database connection property indexedFiles. The following example demonstrates how to do this.

import java.sql.*;
import java.util.Properties;

public class DemoDriver2
{
  public static void main(String[] args)
  {
    try
    {
      Class.forName("org.relique.jdbc.csv.CsvDriver");
      Properties props = new Properties();
      props.put("fileExtension", ".txt");
      props.put("indexedFiles", "true");
      // We want to read test-001-20081112.txt, test-002-20081113.txt and many
      // other files matching this pattern.
      props.put("fileTailPattern", "-(\\d+)-(\\d+)");
      // Make the two groups in the regular expression available as
      // additional table columns.
      props.put("fileTailParts", "Seqnr,Logdatum");
      Connection conn = DriverManager.getConnection("jdbc:relique:csv:" +
        args[0], props);
      Statement stmt = conn.createStatement();
      ResultSet results = stmt.executeQuery("SELECT Datum, Station, " +
        "Seqnr, Logdatum FROM test");
      ResultSetMetaData meta = results.getMetaData();
      while (results.next())
      {
        for (int i = 0; i < meta.getColumnCount(); i++)
        {
          System.out.println(meta.getColumnName(i + 1) + " " +
            results.getString(i + 1));
        }
      }
      results.close();
      stmt.close();
      conn.close();
    }
    catch(Exception e)
    {
      e.printStackTrace();
    }
  }
}

Set the database connection property columnTypes to enable expressions containing numeric, time and date data types to be used in the SELECT statement and to enable them be fetched using ResultSet.getInt, getDouble, getTime and other ResultSet.get methods.

import java.sql.*;
import java.util.Properties;

public class DemoDriver3
{
  public static void main(String[] args)
  {
    try
    {
      Class.forName("org.relique.jdbc.csv.CsvDriver");
      Properties props = new Properties();
      // Define column names and column data types here.
      props.put("suppressHeaders", "true");
      props.put("headerline", "ID,ANGLE,MEASUREDATE");
      props.put("columnTypes", "Int,Double,Date");
      Connection conn = DriverManager.getConnection("jdbc:relique:csv:" +
        args[0], props);
      Statement stmt = conn.createStatement();
      ResultSet results = stmt.executeQuery("SELECT Id, Angle * 180 / 3.1415 as A, " +
        "MeasureDate FROM t1 where Id > 1001");
      while (results.next())
      {
    	  // Fetch column values with methods that match the column data types.
          System.out.println(results.getInt(1));
          System.out.println(results.getDouble(2));
          System.out.println(results.getDate(3));
      }
      results.close();
      stmt.close();
      conn.close();
    }
    catch(Exception e)
    {
      e.printStackTrace();
    }
  }
}

To read the compressed files inside a ZIP file as database tables, make a database connection to the ZIP file using the JDBC connecting string format jdbc:relique:csv:zip:filename.zip. This is demonstrated in the following example.

import java.sql.*;
import java.util.Properties;

public class DemoDriver4
{
  public static void main(String[] args)
  {
    try
    {
      Class.forName("org.relique.jdbc.csv.CsvDriver");
      String zipFilename = args[0];
      Connection conn = DriverManager.getConnection("jdbc:relique:csv:zip:" +
        zipFilename);
      Statement stmt = conn.createStatement();
      // Read from file mytable.csv inside the ZIP file
      ResultSet results = stmt.executeQuery("SELECT * FROM mytable");
      while (results.next())
      {
          System.out.println(results.getString("COUNTRY"));
      }
      results.close();
      stmt.close();
      conn.close();
    }
    catch(Exception e)
    {
      e.printStackTrace();
    }
  }
}

To read data that is either held inside the Java application (for example, in a JAR file) or accessed remotely (for example, using HTTP requests), create a Java class that implements the interface org.relique.io.TableReader and give this class name in the connection URL. CsvJdbc then creates an instance of this class and calls the getReader method to obtain a java.io.Reader for each database table being read. This is demonstrated in the following two Java classes.

import java.io.*;
import java.sql.*;
import java.util.*;

import org.relique.io.TableReader;

public class MyTableReader implements TableReader
{
  public Reader getReader(Statement statement, String tableName) throws SQLException
  {
    if (tableName.equalsIgnoreCase("ELEMENT"))
      return new StringReader("ATOMIC_NUMBER,SYMBOL,NAME\n1,H,Hydrogen\n2,He,Helium\n3,Li,Lithium\n");
    throw new SQLException("Table does not exist: " + tableName);
  }

  public List getTableNames(Connection connection) throws SQLException
  {
    Vector v = new Vector();
    v.add("ELEMENT");
    return v;
  }
}

import java.sql.*;

public class DemoDriver5
{
  public static void main(String []args)
  {
    try
    {
      Class.forName("org.relique.jdbc.csv.CsvDriver");
      // Give name of Java class that provides database tables.
      Connection conn = DriverManager.getConnection("jdbc:relique:csv:class:" +
        MyTableReader.class.getName());
      Statement stmt = conn.createStatement();
      ResultSet results = stmt.executeQuery("SELECT Atomic_Number from element where Symbol='Li'");
      results.next();
      System.out.println(results.getString(1));
      results.close();
      stmt.close();
      conn.close();
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
  }
}

Features

CsvJdbc accepts only SQL SELECT queries from a single table and does not support INSERT, UPDATE, DELETE or CREATE statements. Joins between tables in SQL SELECT queries are not supported.

SQL SELECT queries must be of the following format.

SELECT [DISTINCT] [table-alias.]column [[AS] alias], ...
  FROM table [[AS] table-alias]
  WHERE [NOT] condition [AND | OR condition] ...
  ORDER BY column [ASC | DESC] ...
  LIMIT n [OFFSET n]

Each column is either a named column, *, a constant value, NULL, CURRENT_DATE, or an expression including functions COUNT, LOWER, MAX, MIN, ROUND, UPPER and operations +, -, /, * and parentheses. Supported comparisons in the optional WHERE clause are <, >, <=, >=, =, !=, <>, BETWEEN, LIKE, IS NULL.

Requirements

CsvJdbc requires Java version 1.6, or later.

Driver Properties

The driver also supports a number of parameters that change the default behaviour of the driver.

These properties are:

charset

type: String

default: Java default

Defines the character set name of the files being read, such as UTF-16. See the Java Charset documentation for a list of available character set names.

columnTypes

type: String

default: all Strings

this property defines SQL data types for tables as a comma-separated list. When record fields are retrieved with getObject (as opposed to getString), the driver will parse the value and return a correctly typed object. The order must match the column order in the file. If less data types than the number of columns are provided, the last type is repeated for all trailing columns. If columnTypes is set to an empty string then column types are inferred from the data. When working with multiple tables with different column types, define properties named columnTypes.CATS and columnTypes.DOGS to define different column types for tables CATS and DOGS.

commentChar

type: Character

default:

Lines before the header starting with this character are ignored. After the header has been read, all lines are interpreted as data.

cryptoFilterClassName

type: Class

default: null

The full class name of a Java class that decrypts the file being read. The class must implement interface org.relique.io.CryptoFilter. The class org.relique.io.XORFilter included in CsvJdbc implements an XOR encryption filter.

cryptoFilterParameterTypes

type: String

default: String

Comma-separated list of data types to pass to the constructor of the decryption class set in property cryptoFilterClassName.

cryptoFilterParameters

type: String

default:

Comma-separated list of values to pass to the constructor of the decryption class set in property cryptoFilterClassName.

defectiveHeaders

type: Boolean

default: False

in case a column name is the emtpy string, replace it with COLUMNx, where x is the ordinal identifying the column.

fileExtension

type: string

default: ".csv"

Used to specify a different file extension.

fileTailParts

type: String

default: null

Comma-separated list of column names for the additional columns generated by regular expression groups in the property fileTailPattern.

fileTailPattern

type: String

default: null

Regular expression for matching filenames when property indexedFiles is True. If the regular expression contains groups (surrounded by parentheses) then the value of each group in matching filenames is added as an extra column to each line read from that file. For example, when querying table test, the regular expression -(\d+)-(\d+) will match files test-001-20081112.csv and test-002-20081113.csv. The column values 001 and 20081112 are added to each line read from the first file and 002 and 20081113 are added to each line read from the second file.

fileTailPrepend

type: Boolean

default: False

when True, columns generated by regular expression groups in the fileTailPattern property are prepended to the start of each line. When False, the generated columns are appended after the columns read for each line.

headerline

type: string

default: None

Used in combination with the suppressHeaders property to specify a custom header line for tables. With suppressHeaders set and headerline not set, all columns are named sequentially COLUMN1, COLUMN2, ... When working with multiple tables with different headers, define properties named headerline.CATS and headerline.DOGS to define different header lines for tables CATS and DOGS.

ignoreNonParseableLines

type: Boolean

default: False

when True, unparseable lines will not cause exceptions but will be silently ignored.

indexedFiles

type: Boolean

default: False

when True, all files with a filename matching the table name plus the regular expression given in property fileTailPattern are read as if they were a single file.

quotechar

type: Character

default: "

Defines quote character. Column values surrounded with the quote character are parsed with the quote characters removed. This is useful when values contain the separator character or line breaks. Only one character is allowed.

quoteStyle

type: String

default: SQL

Defines how a quote character is interpreted inside a quoted value. When SQL, a pair of quote characters together is interpreted as a single quote character. When C, a backslash followed by a quote character is interpreted as a single quote character.

raiseUnsupportedOperationException

type: Boolean

default: True

when True, calls to functions in the JDBC driver for features that are not available (such as commit or rollback) will throw an SQLException. When False, the functions do nothing and return a default value.

separator

type: character

default: ','

Used to specify a different column separator.

skipLeadingLines

type: Integer

default: 0

after opening a file, skip this many lines before starting to interpret the contents.

skipLeadingDataLines

type: Integer

default: 0

after reading the header from a file, skip this many lines before starting to interpret lines as records.

suppressHeaders

type: boolean

default: False

Used to specify that the file does not contain column header information. if False, column headers are on first line.

timestampFormat, timeFormat, dateFormat

type: String

default: YYYY-MM-DD HH:mm:ss, HH:mm:ss, YYYY-MM-DD

Defines the format from which columns of type Timestamp, Date and Time are parsed.

timeZoneName

type: String

default: UTC

The time zone of Timestamp columns. To use the time zone of the computer, set this to the value returned by the method java.util.TimeZone.getDefault().getID().

trimHeaders

type: Boolean

default: False

This following example code shows how some of these properties are used.

  ...

  Properties props = new java.util.Properties();

  props.put("separator", "|");              // separator is a bar
  props.put("suppressHeaders", "true");     // first line contains data
  props.put("fileExtension", ".txt");       // file extension is .txt
  props.put("timeZoneName", "America/Los_Angeles"); // timestamps are Los Angeles time

  Connection conn = Drivermanager.getConnection("jdbc:relique:csv:" + args[0], props);

  ...
    

Building From Source

To checkout and build the latest CsvJdbc source code, use the following commands (git and ant must first be installed).

git clone git://csvjdbc.git.sourceforge.net/gitroot/csvjdbc/csvjdbc
cd csvjdbc
cd build
ant jar
cd ..
cd release
dir csvjdbc.jar

Building With Maven

To include CsvJdbc in a Maven project, add the following lines to the pom.xml file.

<project>
 ...
  <repositories>
    <repository>
      <id>SourceForge</id>
      <url>http://csvjdbc.sourceforge.net/maven2</url>
    </repository>
  </repositories>

  <dependencies>
    <dependency>
      <groupId>net.sourceforge.csvjdbc</groupId>
      <artifactId>csvjdbc</artifactId>
      <version>1.0.9</version>
    </dependency>
  </dependencies>

License

CsvJdbc is released under the GNU Lesser General Public License (LGPL).