At 08:07 PM 5/23/2013, Bruce A. Chitiea wrote:
Summary:
a) The GATEWAY command at the R>Prompt fails completely to draw data
into the target table;
b) The GATEWAY tool, with or without a specification, loads everything
correctly with the exception of the AUTONUM column, which populates
with NULLs.
I have a simple table with an autonum column:
List table opcashbalance
Cashbalid integer not null autonumber
Cashdate date
Cashbal currency
Cashavg currency
Cashdelta currency
Cashmin currency
With an XLSX (single worksheet) source file, the following command
fails to draw data in:
GATEWAY IMPORT XLS d:\00\gway\gway_opcashbalance.xlsx +
APPEND opcashbalance +
OPTION AUTONUM ON +
|FIRST_ROW 2
When the XLSX file is saved as CSV, the following command fails to
draw data in:
GATEWAY IMPORT CSV d:\00\gway\gway_opcashbalance.csv +
APPEND opcashbalance +
OPTION AUTONUM ON +
|FIRST_ROW 2
When the process is worked through the GATEWAY tool, everything
imports except the AUTONUM column, which populates with NULLs.
Am I missing something here, or should I generate the autonum value
in the source file and learn to love the LOAD command?
Bruce,
A few questions and suggestions ...
What is the database SETting for ZERO?
CONNECT dbname
SHOW ZERO
Make sure the ZERO setting is SET to ON.
SET ZERO ON
What is the definition of AUTONUM column?
Without knowing all details, try the following exercises and see what
you get.
-- Exercise_01.RMD
-- Load Data with AutoNumbered Column
-- Define Temporary Table to Load Data
SET ERROR MESSAGE 2038 OFF
DROP TABLE tOpCashBalance
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE tOpCashBalance +
CashBalID INTEGER NOT NULL +
('Value for Column CashBalID Cannot be NULL'), +
CashDate DATE, +
CashBal CURRENCY, +
CashAvg CURRENCY, +
CashDelta CURRENCY, +
CashMin CURRENCY
AUTONUM CashBalID IN tOpCashBalance USING 1,1 NONUM
COMMENT ON TABLE tOpCashBalance IS 'Temporary Table to Load Data'
-- Import Data
GATEWAY IMPORT XLS +
tOpCashBalance.xls APPEND tOpCashBalance +
OPTION FIRST_ROW 2
-- Browse Imported Data
CLS
BROWSE * FROM tOpCashBalance
RETURN
-- Exercise_02.RMD
-- AutoNumber Column After Loading Data
-- Define Temporary Table to Load Data
SET ERROR MESSAGE 2038 OFF
DROP TABLE tOpCashBalance
SET ERROR MESSAGE 2038 ON
CREATE TEMPORARY TABLE tOpCashBalance +
CashBalID INTEGER NOT NULL +
('Value for Column CashBalID Cannot be NULL'), +
CashDate DATE, +
CashBal CURRENCY, +
CashAvg CURRENCY, +
CashDelta CURRENCY, +
CashMin CURRENCY
AUTONUM CashBalID IN tOpCashBalance USING 1,1 NONUM
COMMENT ON TABLE tOpCashBalance IS 'Temporary Table to Load Data'
-- Import Data
GATEWAY IMPORT XLS +
tOpCashBalance.xls APPEND tOpCashBalance +
OPTION FIRST_ROW 2
-- Autonum CashBalID Column
AUTONUM CashBalID IN tOpCashBalance USING 1,1 NUM
-- Browse Imported Data
CLS
BROWSE * FROM tOpCashBalance
RETURN
Hope that provides you with some blue's clues ...
Very Best R:egards,
Razzak.
www.rbase.com
www.facebook.com/rbase
