This shows you the differences between two versions of the page.
|
integration:rawdata:delimited:options [2011/03/14 14:21] 127.0.0.1 external edit |
integration:rawdata:delimited:options [2016/06/28 22:38] |
||
|---|---|---|---|
| Line 1: | Line 1: | ||
| - | ~~NOTOC~~ | ||
| - | {{page>:top_add&nofooter&noeditbtn}} | ||
| - | |||
| - | ====== Adminstration: Raw Data Files ====== | ||
| - | |||
| - | ===== Delimited Files ===== | ||
| - | |||
| - | [[integration:rawdata:delimited:home|Overview]] | | ||
| - | [[integration:rawdata:delimited:environments|Environments]] | | ||
| - | **[[integration:rawdata:delimited:options|Options]]** | | ||
| - | [[integration:rawdata:delimited:datatypes|Datatypes]] | | ||
| - | [[integration:rawdata:delimited:ofx|Offset Indexes]] | | ||
| - | [[integration:rawdata:delimited:export|Exporting]] | | ||
| - | [[integration:rawdata:delimited:limitations|Limitations]] | | ||
| - | [[integration:rawdata:delimited:example|Example]] | ||
| - | |||
| - | ---- | ||
| - | |||
| - | ==== 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 | | ||
| - | | ||
| - | The default column separator is a tab (eg. COLUMN='\t'). | ||
| - | |||
| - | == 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 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 == | ||
| - | |||
| - | 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. | ||
| - | |||
| - | |||
| - | ===== ===== | ||
| - | |||
| - | **[[integration:rawdata:delimited:environments|Prev]]** | | ||
| - | **[[integration:rawdata:delimited:datatypes|Next]]** | ||
| - | |||
| - | ====== Additional Resources ====== | ||
| - | |||
| - | See also: | ||
| - | |||
| - | {{page>:integration:rawdata:see_also&nofooter&noeditbtn}} | ||
| - | |||
| - | {{page>:bottom_add&nofooter&noeditbtn}} | ||