Overview | Environments | Options | Datatypes | Offset Indexes | Exporting | Limitations | Example
The OPTIONS clause of the CREATE TABLE statement is used to specify the column and record delimiters and other behavior or delimited files. The following options are supported:
COLUMN='c' or COLUMN='cc'
The COLUMN option indicates the delimiter used to separate columns in a row. Unprintable characters can be used as well, and are specified using the tokens described below:
Character | Description |
---|---|
\a | alert |
\b | bell |
\f | formfeed |
\n | linefeed |
\r | carriage-return |
\t | horizontal tab |
\v | vertical tab |
\\ | backslash |
\' | single-quote |
\nnn | octal value |
RECORD='c' or RECORD='cc'
The RECORD option indicates the delimiter used to separate rows in a file. Unprintable characters can be used as well, and are specified using the tokens described below:
Character | Description |
---|---|
\a | alert |
\b | bell |
\f | formfeed |
\n | linefeed |
\r | carriage-return |
\t | horizontal tab |
\v | vertical tab |
\\ | backslash |
\' | single-quote |
\nnn | octal value |
The default record separator is a combination of carriage return and linefeed for Microsoft Windows servers (eg. RECORD='\r\n'), and a linefeed for UNIX servers (eg. RECORD='\n').
QUOTES
Some delimited files will quote the content of some or all of the columns. This insures that delimiter characters found in the data are not treated as actual delimiters. For example, the column “William Smith, Attorney at Law” is often quoted in a comma-delimited file so that the embedded comma is not treated as a delimiter. In these cases, use the QUOTES option to interpret quotation marks as having this purpose, rather than being part of the data. Quotes will be considered part of the data by default.
In this example, the textual data is quoted to insure that delimiter characters in the data are not treated as column or record delimiters:
Column Datatype | ID INTEGER | COMPANY STRING(31) |
---|---|---|
Row 1 | 1,“Mabel's Famous Diner” | |
Row 2 | 2,“William Smith, Attorney at Law” |
ESCAPE='c'
As an alternative to quoting, delimited files may use an escape character to indicate that the subsequent character should not be considered a delimiter, but rather is part of the data. Rather than quoting the data, an escape character precedes every occurrence of the delimiter in the data itself. (Ironically, if the escape character itself exists in the data, then it too must be escaped using its own character.) Escaping may be turned off using the OFF setting. In this example, the default escape character of \ is used to prevent commas, apostrophes, and quotes from being considered delimiters:
Column Datatype | ID INTEGER | COMPANY STRING(31) |
---|---|---|
Row 1 | 1,Mabel\'s \“Famous\” Diner | |
Row 2 | 2,William Smith\, Attorney at Law |
HEADER
The HEADER option indicates that the first row of the file contains column names rather than data and should be ignored when reading the table.
See also: