The CsvJdbc Logo

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

Features

CsvJdbc accepts all types of CSV files defined by RFC 4180.

CsvJdbc accepts only SQL SELECT queries from a single table and does not support INSERT, UPDATE, DELETE or CREATE statements.

SQL sub-queries are permitted but joins between tables in SQL SELECT queries are not yet 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] ...
  GROUP BY column ... [HAVING condition ...]
  ORDER BY column [ASC | DESC] ...
  LIMIT n [OFFSET n]

Each column is either a named column, *, a constant value, NULL, CURRENT_DATE, CURRENT_TIME, a sub-query, or an expression including functions, aggregate functions, operations +, -, /, *, %, ||, conditional CASE expressions and parentheses.

Supported comparisons in the optional WHERE clause are <, >, <=, >=, =, !=, <>, NOT, BETWEEN, LIKE, IS NULL, IN, EXISTS.

Use double quotes around table names or column names containing spaces or other special characters.

Function Description
ABS(N) Returns absolute value of N
COALESCE(N1, N2, …) Returns first expression that is not NULL
DAYOFMONTH(D) Extracts day of month from date or timestamp D (first day of month is 1)
HOUROFDAY(T) Extracts hour of day from time or timestamp T
LENGTH(S) Returns length of string
LOWER(S) Converts string to lower case
LTRIM(S [, T]) Removes leading characters from S that occur in T
MINUTE(T) Extracts minute of hour from time or timestamp T
MONTH(D) Extracts month from date or timestamp D (first month is 1)
NULLIF(X, Y) Returns NULL if X and Y are equal, otherwise X
ROUND(N) Rounds N to nearest whole number
RTRIM(S, [, T]) Removes trailing characters from S that occur in T
SECOND(T) Extracts seconds value from time or timestamp T
SUBSTRING(S, N [, L]) Extracts substring from S starting at index N (counting from 1) with length L
TRIM(S, [, T]) Removes leading and trailing characters from S that occur in T
UPPER(S) Converts string to lower case
YEAR(D) Extracts year from date or timestamp D

Additional functions are defined from java methods using the function.NAME driver property.

Aggregate Function Description
AVG(N) Average of all values
COUNT(N) Count of all values
MAX(N) Maximum value
MIN(N) Minimum value
STRING_AGG(S, D) All values of S concatenated with delimiter D
SUM(N) Sum of all values

For queries containing ORDER BY, all records are read into memory and sorted. For queries containing GROUP BY plus an aggregate function, all records are read into memory and grouped. For queries that produce a scrollable result set, all records up to the furthest accessed record are held into memory. For other queries, CsvJdbc holds only one record at a time in memory.

Dependencies

CsvJdbc requires Java version 1.6, or later. For reading DBF files, DANS DBF Library must be downloaded and included in the CLASSPATH.

Advanced Usage

Like other databases, creating a scrollable statement enables scrolling forwards and backwards through result sets. This is demonstrated in the following example.

import java.sql.*;

public class DemoDriver2
{
  public static void main(String[] args)
  {
    try
    {
      Class.forName("org.relique.jdbc.csv.CsvDriver");
      Connection conn = DriverManager.getConnection("jdbc:relique:csv:" + args[0]);

      // create a scrollable Statement so we can move forwards and backwards
      // through ResultSets
      Statement stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
        ResultSet.CONCUR_READ_ONLY);
      ResultSet results = stmt.executeQuery("SELECT ID,NAME FROM sample");

      // dump out the last record in the result set, then the first record
      if (results.last())
      {
        System.out.println("ID= " + results.getString("ID") +
              "   NAME= " + results.getString("NAME"));
        if (results.first())
        {
          System.out.println("ID= " + results.getString("ID") +
              "   NAME= " + results.getString("NAME"));
        }
      }

      // clean up
      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 DemoDriver3
{
  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));
        }
      }
      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 SELECT statements and to enable column values to be fetched using ResultSet.getInt, getDouble, getTime and other ResultSet.get methods.

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

public class DemoDriver4
{
  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));
      }
      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 connection string format jdbc:relique:csv:zip:filename.zip. This is demonstrated in the following example.

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

public class DemoDriver5
{
  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"));
      }
      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 MyHTTPReader implements TableReader
{
  public Reader getReader(Statement statement, String tableName) throws SQLException
  {
    try
    {
      URL url = new URL("http://csvjdbc.sourceforge.net/" + tableName + ".csv");
      HttpURLConnection connection = (HttpURLConnection) url.openConnection();
      InputStreamReader reader = new InputStreamReader(connection.getInputStream());
      return reader;
    }
    catch (Exception e)
    {
      throw new SQLException(e.getMessage()); 
    }
  }
  public List getTableNames(Connection connection)
  {
    // Return list of available table names
    Vector v = new Vector();
    v.add("sample");
    return v;
  }
}


import java.sql.*;
import org.relique.jdbc.csv.CsvDriver;

public class DemoDriver6
{
  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:" +
        MyHTTPReader.class.getName());
      Statement stmt = conn.createStatement();
      String sql = "SELECT * FROM sample";
      ResultSet results = stmt.executeQuery(sql);
      CsvDriver.writeToCsv(results, System.out, true);
      conn.close();
    }
    catch (Exception e)
    {
      e.printStackTrace();
    }
  }
}

Driver Properties

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

These properties are:

charset

columnTypes

commentChar

cryptoFilterClassName

cryptoFilterParameterTypes

cryptoFilterParameters

defectiveHeaders

fileExtension

fileTailParts

fileTailPattern

fileTailPrepend

fixedWidths

function.NAME

headerline

ignoreNonParseableLines

indexedFiles

isHeaderFixedWidth

missingValue

quotechar

quoteStyle

locale

separator

skipLeadingLines

skipLeadingDataLines

suppressHeaders

timestampFormat, timeFormat, dateFormat

timeZoneName

trimHeaders

trimValues

The 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 conn1 = Drivermanager.getConnection("jdbc:relique:csv:" + args[0], props);

  ...

  // Connections using a URL string containing both directory and
  // properties are also accepted (class java.net.URLEncoder encodes
  // property values containing special characters).
  Connection conn2 = DriverManager.getConnection("jdbc:relique:csv:" + args[0] + "?" +
    "separator=" + URLEncoder.encode("|", "UTF-8") + "&" +
    "quotechar=" + URLEncoder.encode("'", "UTF-8") + "&" +
    "fileExtension=.txt" + "&" +
    "suppressHeaders=true");

Updated: 6 January 2016

built on DEV @cloud