Syntax:
GATEWAY IMPORT XLS +
filename.ext {APPEND/APPEND_UPDATE/CREATE/REPLACE} +
<tablename> +
OPTION SHEET_INDEX value +
|ADD_MAPPING value +
|SPECIFICATION_FILE_NAME filename +
|COLUMN_COUNT value +
|KEYS value +
|AUTONUM value +
|FIRST_ROW n +
|LAST_ROW n +
|PERCENT_MODE value +
|SHOW_PROGRESS value +
|MESSAGES value +
|PASSWORD value +
|ERROR_FILE EXCEPT.DAT
filename.ext
This parameter defines the file name of the target file with data using the MS Excel format. The file extension can be xls, xlsx, or xlsm.
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.
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
PASSWORD
Specifies the possible password for the Excel import file. Passwords are case sensitive.
PERCENT_MODE
Specifies how percentage cells are treated, as the XLS and XLSX store the values differently. Available Parameters: DEFAULT / 100 / RAW
•DEFAULT - the default behavior is used for importing. XLS stores the value as "9.5" where XLSX stores the value as "0.095".
•100 - the imported data will be "9.5", where the stored value is multiplied by 100.
•RAW - the imported data will be "0.095". Note that percentage values are stored as (raw) decimals.
SHEET_INDEX value
This parameter is used to specify a specific sheet of the spreadsheet file, starting with 0.
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 XLS D:\DataStore\Contacts.XLS CREATE ContactsImport OPTION SHEET_INDEX 0|FIRST_ROW 2
GATEWAY IMPORT XLS D:\DataStore\Transactions.XLSX APPEND TransLog OPTION SHOW_PROGRESS OFF|MESSAGES OFF
--This command will only append cell B, cell C and cell D data into the Company, CustAddress and CustState columns of the Customer table.
GATEWAY IMPORT XLS Customer.XLS APPEND Customer +
OPTION ADD_MAPPING Company=B|ADD_MAPPING CustAddress=C|ADD_MAPPING CustState=D
GATEWAY IMPORT XLS +
Jan2020-City-Rates.xlsx REPLACE +
tTaxTableImport +
OPTION SHEET_INDEX 0 +
|AUTONUM OFF +
|FIRST_ROW 7 +
|SHOW_PROGRESS ON +
|MESSAGES ON +
|PERCENT_MODE RAW +
|ERROR_FILE EXCEPT.DAT
Notes:
Use this option to import data from a MS Excel file. The process does not use the MS Excel application as OLE server unless the password option is specified. If the process uses a password, MS Excel is required.
