Bruce, I believe the problem is that you have a "blank" column 1 to accommodate you auto-number column. R:Base thinks that you have a value for that column and are trying to load a blank/null value into that column. Try deleting the blank column in your data source and don't specify that column in the Gateway expression and R:base will generate the values for that column.
Javier, Javier Valencia, PE O: 913-829-0888 H: 913-397-9605 C: 913-915-3137 -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Bruce A. Chitiea Sent: Friday, May 24, 2013 10:11 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Gateway Autonum Failure Razzak: Thank you very much for the exercises. My SHOW ZERO shows "ON" I've performed a set of controlled experiments with a set of source files: Opcashbalance.xlsx Opcashbalance.xls Opcashbalance.csv ... generated from Excel 2013 ... containing only one worksheet ... with blank column 1 to accommodate the CASHBALID autonumber column ... with data starting on row 1 ... sourced sequentially from each two folders: D:\00\gway <database folder> ... with your exercise code appropriately modified both exercises 01.RMD and 02.RMD fail to import data. The import dialog appears and is immediately overlain by a constraint error message: "Value for Column CashBalID Cannot be NULL" [OK] "Value for Column CashBalID Cannot be NULL" [OK] "No rows in table, INSERT a NULL row? [NO] There may be more clues, but through the blue, hard to view. Thoughts? And thanks. Bruce -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of A. Razzak Memon Sent: Thursday, May 23, 2013 8:00 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Gateway Autonum Failure 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
