Syntax:
GATEWAY IMPORT TXT +
filename.txt {APPEND/APPEND_UPDATE/CREATE/REPLACE} +
<tablename> +
OPTION SPECIFICATION_FILE_NAME filename +
|COLUMN_COUNT value +
|KEYS value +
|ADD_MAPPING value +
|AUTONUM value +
|FIRST_ROW n +
|LAST_ROW n +
|QUALIFIER value +
|SEPARATOR value +
|REC_SEP value +
|ESCAPE_QUOTES value +
|SHOW_PROGRESS value +
|MESSAGES value +
|ERROR_FILE EXCEPT.DAT
Code |
Delimiter |
Qualifier |
TXT |
None |
None |
CSV |
Comma |
Double Quotes |
QSV |
Comma |
Single Quotes |
ISV |
Semicolon |
Single Quotes |
TAB |
Tab |
None |
TIL |
Tilde |
None |
CUS |
Database DELIMIT setting or SEPARATOR parameter |
Database QUOTES setting or QUALIFIER parameter |
filename.txt
This parameter defines the file name of the target file with data using the ASCII format.
APPEND
Adds the imported data to the end of the table or view specified in tblview.
APPEND_UPDATE
Updates the record if it exists in the tblview destination, otherwise the record is added. In order to specify a matching row for the UPDATE comparison, use the KEYS parameter.
CREATE
Creates a destination table with the same name as the imported file.
REPLACE
Replaces the rows in the table or view specified in tblview with the imported data.
tblview
Specifies the name of a table or view for import.
OPTION
ADD_MAPPING
This parameter assigns a source column to a destination table column, rather than relying on column position.
AUTONUM
This parameter specifies whether the data imported will follow the auto-numbering formula for the table. Available Parameters: ON / OFF
COLUMN_COUNT
This parameter specifies the number of columns to be imported from file. COLUMN_COUNT is only supported when using CREATE in the GATEWAY syntax.
ERROR_FILE
Specifies a file to capture errors encountered during the import process.
ESCAPE_QUOTES
Specifies if R:BASE distinguishes between double quote characters (") embedded in data and double quote characters used to delimit the fields during the import process. When a CSV file contains a data segment with a double quote character, R:BASE expects it to be doubled (e. g. the data 3”” gasket will be imported as 3” gasket). If ESCAPE_QUOTES is set to OFF, double quote characters in the imported file are always interpreted as field delimiters. The default is set to ON. Available Parameters: ON / OFF
FIRST_ROW
This parameter specifies the first row(s) to be used within the file that is being imported into the table.
KEYS
This parameter specifies the matching row to compare for table updates with the APPEND_UPDATE import mode. For multiple key fields to locate a matching row, use a comma separated list of field names.
LAST_ROW
This parameter specifies the last row to be used within the file that is being imported into the table.
MESSAGES
If ON, during import process some messages will be displayed such as -ERROR- messages, if any. If OFF, the entire import process will be in "silent" mode. Available Parameters: ON / OFF
QUALIFIER
Changing the value of this option you can define any text qualifier for fields in the target file. The popular value is Quotes (") but you can define any custom character you wish. Use the keyword PIPE as the value if you wish to use the pipe character as the qualifier, or place double pipes. QUALIFIER is only supported for the CUS import format.
REC_SEP
Specifies the record separator in the target file. The common value is CRLF (carriage return + line feed, which moves the parser down to the next line and then to the beginning of the line. A custom record separator can be specified. Available Parameters: CRLF / CR / LF / <Custom>
SEPARATOR
Defines the separator/delimiter of fields in target text file. The popular values are semicolon (;), comma (,), and space (CHAR(32)), but you can define any custom character if you wish. If the pipe character is needed to be specified as the separator, use the PIPE keyword as the value, or place double pipes. SEPARATOR is only supported for the CUS import format. Available Parameters: COMMA / TAB / SEMICOLON / SPACE / PIPE / NONE / <Custom>
SHOW_PROGRESS
Indicates whether to display status dialog during the import process. Set SHOW_PROGRESS to OFF if you wish to hide the animated status dialog during the import process. Available Parameters: ON / OFF
SPECIFICATION_FILE_NAME
This parameter is used to load a prepared specification with all pre-defined settings for importing. The import must be performed once using the graphic user interface and saved using the "Specifications.." button. The specifications will be stored in a file with the .RGW file extension. The .RGW file acts as a set of default values. Any value in the .RGW file will be overwritten by the same parameter specifically assigned by an OPTION parameter. Since the input file name is required in the GATEWAY command, the input file name in the .RGW will always be overwritten.
Examples:
GATEWAY IMPORT CSV D:\DataStore\Titles_Import.CSV APPEND_UPDATE Titles OPTION KEYS EmpTID|FIRST_ROW 2
GATEWAY IMPORT TAB D:\DataStore\Contacts.TAB CREATE ContactsImport OPTION SHOW_PROGRESS ON
GATEWAY IMPORT TXT D:\DataStore\Transactions.TXT APPEND TransLog OPTION SHOW_PROGRESS OFF|MESSAGES OFF|AUTONUM ON
GATEWAY IMPORT CSV Final22.csv APPEND FinalTR +
OPTION FIRST_ROW 2 +
|ADD_MAPPING ID=Field1 +
|ADD_MAPPING Tax=Field2 +
|ADD_MAPPING Code=Field3 +
|ADD_MAPPING School=Field4 +
|ADD_MAPPING Owner=Field5
