FRONTBASE TECHNICAL NOTES
FRONTBASE TECHNICAL NOTES

Enhanced flat-file import and export functions

By: Technical Support,November 12, 2001

FrontBase has provided flat-file import/export functionality in the past but we have recently sought to improve this with some enhancements in FrontBase 3.x. This technical note seeks to explain and demonstrate these enhancements.

Enhanced flat-file export function

To improve the features, flexibility and robustness of the flat-file export function, we have enhanced the export functionality (the previous import SQL statements still function unchanged).

Syntax:

WRITE ALL OUTPUT(list of <key value pair>);

<key value pair> ::= <key> = <value>
<key>            ::= RSEP  | CSEP | DIR  | FOLDER | CONTENT
<value>          ::= "general expression"

RSEP - Row SEParator

<value> must be a string expression and it identifies a string that separates the rows in the actual input from each other. Certain control characters can be escaped by using a normal backslash+letter combo:
\n new line
\r carriage return
\t horizontal tab

CSEP - Column SEParator

<value> must be a string expression and it identifies a string that separates the columns in the actual input from each other (within a row). Certain control characters can be escaped by using a normal backslash+letter combo (see RSEP).

DIR or FOLDER

<value> must be a string expression and it identifies a string that denotes the path of the directory (folder) into which the flat-file export will be generated. If the directory doesn't exist, it will be attempted generated.

CONTENT

<value> must be a boolean expression that evaluates to either TRUE or FALSE. TRUE means that also the contents of tables will be exported (one file per table).

Enhanced flat-file import filter

To improve the features, flexibility and robustness of the flat-file import filter, we have enhanced the import filter functionality (the previous import SQL statements still function unchanged).

Syntax:

INSERT INTO <table> [FROM] INPUT(list of <key value pair>)
[COMMIT <count>];
<key value pair> ::= <key> = <value>
<key>            ::= RSEP  | CSEP | FILE  | TYPE | COLUMNS | COUNT | SKIP | CHECK | RDQ  | STOP
<value>          ::= "general expression"

TYPE

<value> must be one of 'FrontBase', 'Access', 'Sybase', 'Informix' or 'MSSQL'.

This value provides a hint to the import filter and implies a number of default values:

TYPE = 'FrontBase' =>
RSEP = '\n', CSEP = '', SKIP = 1

This value also implies that the actual input file has a format that is identical to what FrontBase produces when dumping a table into a flat-file (see WRITE TABLE).

TYPE = 'Access' =>
RSEP = '\n', CSEP = ';'

The value of TYPE also implies in what format DATE, TIME and TIMESTAMP values are given.

RSEP - Row SEParator

<value> must be a string expression and it identifies a string that separates the rows in the actual input from each other. Certain control characters can be escaped by using a normal backslash+letter combo:
\n new line
\r carriage return
\t horizontal tab

CSEP - Column SEParator

<value> must be a string expression and it identifies a string that separates the columns in the actual input from each other (within a row). Certain control characters can be escaped by using a normal backslash+letter combo (see RSEP).

FILE

<value> must be a string expression and it identifies a string that denotes the path of the actual flat file input.

COLUMNS

<value> must be a list of column names each denoting a column of the given <table>. The order in which the column names are given is important and must match the actual input.

SKIP

<value> must be an integer expression and it identifies how many lines of the actual input file that are to be unconditionally skipped before any other lines are read.

COUNT

<value> must be an integer expression and it identifies for how many columns there will be values in the actual input. COUNT is only necessary if no value for COLUMNS is specified and if TYPE = 'Access' (the column names are assumed given on the first line of the actual input).

CHECK

<value> must be a boolean expression that evaluates to either TRUE or FALSE. TRUE means that for each row to be inserted, all constraint checks are enforced. If FALSE is specified, no constraint checks are enforced. It is a good idea to make sure that the input is indeed wellformed if CHECK is set to FALSE, i.e. to avoid duplicate PRIMARY KEYs.

RDQ - Remove Double Qoutes

<value> must be a boolean expression that evaluates to either TRUE or FALSE. TRUE means that if a column value is enclosed in double quotes, they will automatically be removed.

STOP - Stop after an error has been generated

<value> must be a boolean expression that evaluates to either TRUE or FALSE. TRUE means that an import session will be terminated once an error has been found.