Integration: Raw Data Files

Delimited Files

Options

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
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
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
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
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.

Additional Resources

See also:

 
Back to top
integration/rawdata/delimited/options.txt · Last modified: 2016/06/28 22:38 (external edit)
 
 
chimeric.de = chi`s home Valid CSS Driven by DokuWiki do yourself a favour and use a real browser - get firefox!! Recent changes RSS feed Valid XHTML 1.0