NAME

DBE::Driver::TEXT - Text Driver for the Perl Database Express Engine

SYNOPSIS

  use DBE;
  
  $con = DBE->connect( 'Provider=Text;DBQ=/path/to/text-files' );
  
  $res = $con->query( 'SELECT * FROM "table.csv"' );
      or
  $stmt = $con->prepare( 'SELECT * FROM "table.csv"' );
  $res = $stmt->execute();
      or
  $con->prepare( 'SELECT * FROM "table.csv"' );
  $res = $con->execute();

EXAMPLE

NOTE: This requires a schema.ini file in the same path as the delimited text file. You must change the columns in the schema.ini file to correspond to your delimited file. Also, you must change the path in the connect string to the correct path for your file.

To create a schema.ini file, use a text editor. Below are the schema.ini entries for the sample1.txt file listed below in this example:

schema.ini

  [sample1.txt]
  ColNameHeader = False
  Format = CSVDelimited
  CharacterSet = ANSI
  Col1 = ProductID int
  Col2 = ProductName char width 30
  Col3 = QuantityPerUnit char width 30
  Col4 = UnitPrice double
  Col5 = Discontinued int  

You could copy and paste the following delimited file example into a text editor and save it as sample1.txt. You could then modify the DBQ path, below, to point to the comma delimited file sample1.txt.

sample1.txt

  1,Chai,10 boxes x 20 bags,18.00,0
  2,Chang,24 - 12 oz bottles,19.00,0
  3,Aniseed Syrup,12 - 550 ml bottles,10.00,0
  4,Chef Anton's Cajun Seasoning,48 - 6 oz jars,22.00,0
  5,Chef Anton's Gumbo Mix,36 boxes,21.35,1
  6,Grandma's Boysenberry Spread,12 - 8 oz jars,25.00,0
  7,Uncle Bob's Organic Dried Pears,12 - 1 lb pkgs.,30.00,0 

NOTE: Make sure when you paste it in the text editor that you do not leave any blank lines at the top of the file.

sample1.pl

  use DBE;
  
  $con = DBE->connect(
      'Provider' => 'Text',
      'DBQ' => 'E:\Samples\Text\CommaDelimited',
      'Charset' => 'UTF-8',
  );
  
  $res = $con->query( 'SELECT * FROM "sample1.txt"' );

DESCRIPTION

DBE::Driver::Text is a DBE driver for text files. It is almost compatible to the Microsoft Text Driver.

CONNECT

  use DBE;
  
  DBE->connect( 'Provider=Text;DBQ=.' );

The parameters are specified as follows:

• PROVIDER [Text]

  Provider must be "Text".

• DBQ | DEFAULTDIR [path]

  Path to text files. Defaults to the current directory.

• USESCHEMA [1|0|Yes|No|True|False]

  A boolean value to enable oder disable "schema.ini". Defaults to Yes.

• COLNAMEHEADER [1|0|Yes|No|True|False]

  A boolean value to enable or disable column names in first row of the text files. Defaults to Yes.

• QUOTECHAR [char]

  The character for quoting values. Defaults to '"'.

• DEC | DECIMALSYMBOL [char]

  The character for the decimal point. Defaults to '.'.

• FORMAT [CSVDelimited|TABDelimited|Delimited(;)]

  Format definition of Text files. Format CSVDelimited delimits columns by comma, TABDelimited delimits columns by tab and Delimited(;) defines a custom delimiter, where ';' can be replaced with any character except of '"'.

• DELIMITER [char]

  Column delimiter character. Defaults to ';'. Overwrites FORMAT.

• MSR | MAXSCANROWS [num]

  Maximum number of rows to scan for to detect data types of a table. Defaults to 25.

• CHARSET | CHARACTERSET [string]

  Client character set. Supported charsets are ANSI and UTF-8. Default is ANSI.

• EXTENSIONS [string]

  Lists of file name extensions for the Text files on the data source separated by comma. Default is "csv,txt"

SQL SYNTAX

SELECT

  SELECT
      select_expr, ...
    FROM table_references
    WHERE where_condition
    [GROUP BY {col_name | expr}, ...]
    [ORDER BY {col_name | expr} [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
  
  table_references:
    table_reference [, table_reference] ...
  
  table_reference:
    tbl_name [[AS] alias]

CREATE TABLE

  CREATE TABLE [IF NOT EXISTS] tbl_name
    (create_definition, ...)
  
  create_definition:
    col_name column_definition
  
  column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
  
  data_type:
      SMALLINT[(length)]
    | INT[(length)]
    | INTEGER[(length)]
    | REAL[(length)]
    | DOUBLE[(length)]
    | FLOAT[(length)]
    | CHAR(length)
    | VARCHAR(length)
    | BINARY(length)
    | VARBINARY(length)
    | BLOB

DROP TABLE

  DROP TABLE [IF EXISTS] tbl_name

INSERT

  INSERT INTO tbl_name [(col_name, ...)]
    VALUES (expr, ...)

UPDATE

  UPDATE tbl_name
    SET col_name1=expr1 [, col_name2=expr2 ...]
    [WHERE where_condition]

Note: UPDATE creates a temporary table and fills it with the updated data. After then the temporary table will be replaced with the old table.

DELETE

  DELETE FROM tbl_name
    [WHERE where_condition]

Note: DELETE creates a temporary table and fills it with the undeleted data. After then the temporary table will be replaced with the old table.

SUPPORTED FUNCTIONS

Aggregate functions

COUNT (expr)

SUM (expr)

AVG (expr)

MIN (expr)

MAX (expr)

Numeric functions

ABS (expr)

ROUND (expr [, prec])

String functions

CONCAT (expr1, expr2, ...)

TRIM ([{LEADING|TRAILING|BOTH}] [expr FROM] expr)

LTRIM (expr)

RTRIM (expr)

LOWER (expr) | LCASE (expr)

UPPER (expr) | UCASE (expr)

LENGTH (expr) | OCTET_LENGTH (expr)

CHAR_LENGTH (expr) | CHARACTER_LENGTH (expr)

LOCATE (substr, str [, start])

POSITION (substr IN str)

SUBSTRING | SUBSTR (expr {,|FROM} index [{,|FOR} count])

Date and time functions

CURRENT_TIMESTAMP | CURRENT_TIMESTAMP () | NOW ()

CURRENT_DATE | CURRENT_DATE ()

CURRENT_TIME | CURRENT_TIME ()

Other functions

CONVERT (expr, type)

CONVERT (expr USING charset)

IF (expr_eval, expr_true, expr_false)

LIMITATIONS

Table format FIXEDLENGTH is not supported.

AUTHORS

Written by Christian Mueller

COPYRIGHT

The DBE::Driver::TEXT module is free software. You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

cpanm DBE

perl -MCPAN -e shell
install DBE