/ 
DBE-0.99_1
River stage zero No dependents
/ DBE::Driver::TEXT

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.