StelsCSV JDBC Driver Documentation

 

 

Installation

 

Add the file csvdriver.jar to your classpath or extract the jar file in the directory of the application.

 

Driver Name

 

The class name of the driver is jstels.jdbc.csv.CsvDriver.

 

URL Syntax

 

The connection URL is jdbc:jstels:csv:csvdir, where csvdir - an absolute or relative to the application pathname of a local directory contained table files, e.g.:

 

jdbc:jstels:csv:c:\mydir\example or jdbc:jstels:csv:example

   

 

Driver Properties

 

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

These properties are:

 

separator used to specify a different column separator (Default is ‘\t’ (tab))

 

supressHeaders used to specify if the first line contains column header information (Default is false; column headers are on first line).

 

fileExtension used to specify a different file extension (Default is ".txt")

 

charset used to specify a different than default charset encoding of input file (default is same VM default charset)

 

dateFormat used to specify a sequence of date/time formats separated by the '|' character, e.g: “dd.MM.yy| dd.MM| dd”. (Default is "yyyy-MM-dd HH:mm:ss.SSS | yyyy-MM-dd HH:mm:ss | yyyy-MM-dd |  HH:mm:ss.SSS |  HH:mm:ss”)

 

This following example code shows how 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("charset","ISO-8859-2");       // file encoding is "ISO-8859-2"
  // set up date/time multiple formats
  props.put("dateFromat","yyyy-MM-dd HH:mm:ss.SSS | yyyy-MM-dd HH:mm:ss");       
 
  Connection conn = Drivermanager.getConnection("jdbc:jstels:csv:" + args[0],props);
 
 
Connection Example

 

This example code shows how the driver is used:

 
import java.sql.*;
 
public class DriverTest
{
  public static void main(String[] args)
  {
    try
    {
      // load the driver into memory
      Class.forName("jstels.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:jstels:csv:" + args[0] );
 
      // create a Statement object to execute the query with
      Statement stmt = conn.createStatement();
 
      // execute a query
      ResultSet rs = stmt.executeQuery("SELECT * FROM \"test.txt\"");
 
      // read the data and put it to the console
      while (rs.next())
      {
            for(int j=1; j <= rs.getMetaData().getColumnCount(); j++){
                   System.out.print(rs.getObject(j)+ "\t");
            }
            System.out.println();
      }
 
      // close the objects
      rs.close();
      stmt.close();
      conn.close();
    }
    catch(Exception e)
    {
      e.printStackTrace();
    }
  }
}
      

 

Database Schema

 

The database schema file is called “schema.xml”. This is XML file that contains the information about the table files in the CSV directory.

The schema has the following format:

<schema>

  <table name="my_table1.txt">

    <column name="my_field1" pos="1" type="Integer"/>

    <!-- the attribute 'name' used to define column name -->

<!-- the attribute 'pos' used to define column’s position in the table-->

<!-- note: tag 'column' must have defined either 'name' or 'pos' attribute -->

    <!-- the attribute 'type' used to assign SQL data type to a column-->

    <column name="my_field1" pos="2" type="Integer"/>

    <column name="my_field3" type="date"/>

  </table>

  <table name="my_table2.txt">

... ... ...   

</schema>

 

The driver supports the following column data types: CHAR, VARCHAR, LONGVARCHAR, INTEGER, TINYINT, SMALLINT, BIGINT, DOUBLE, REAL, FLOAT, DECIMAL, NUMERIC, DATETIME, DATE, TIME, TIMESTAMP that are finally transformed to INTEGER, FLOAT, VARCHAR and TIMESTAMP types.

 

Supported SQL Syntax

 

“SELECT” operatorsee specification, syntax and functions here.

 

Examples:

         SELECT SUM(a) AS col1, MAX(b) / MAX(c) AS col2 FROM “test.txt” GROUP BY a  HAVING AVG(a) > 30

    SELECT a, b, to_string (c) FROM “test.txt” WHERE ( a>5 AND b<>3 ) OR c IS NULL ORDER BY a

    SELECT name FROM “salesreps.txt” WHERE ( rep_office IN ( 22, 11, 12 ) )  OR ( manager IS NULL AND hire_date >= to_date ( ‘01-05-2002’,’dd-MM-yyyy’ ) OR ( sales > quota AND NOT sales > 600000.0 )

    SELECT city, target, sales FROM “offices.txt” WHERE region = ‘Eastern’ AND sales > target ORDER BY city

SELECT trim ( “football club” ) AS club, to_int ( replace_string ( goals, '-' ,'0' ) ) AS goals FROM  “football.txt”

 

“CREATE TABLE”,  UPDATE” and “DELETE” operators are coming with new versions.

Note: Upgrade to new versions is free for registered users!