ADD_MAPPING is MUCHO handy! Dennis McGrath Software Developer QMI Security Solutions 1661 Glenlake Ave Itasca IL 60143 630-980-8461 [email protected] -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Bruce A. Chitiea Sent: Friday, May 24, 2013 12:54 PM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Gateway Autonum Failure: Solution: ADD_MAPPING
Javier et al: If I ever get to Murrysville, I'm buying ADD_MAPPING a beverage. Test results for anyone having similar difficulty. Recap: Target Table: Cashbalid integer (for autonumbering) Cashdate date Cashbal currency Cashavg currency Cashdelta currency Cashmin currency Import columns: Date Number (2digits) Number (2digits) Number (2digits) Number (2digits) Razzak's routine #01.RMD autonumbers the cashbalid column BEFORE the import Razzak's routine #02.RMD autonumbers the cashbalid column AFTER the import Routine #01.RMD, XLS or XLSX, No blank column, No ADD_MAPPING - An autonumbered cashbalid column is not inserted - Autonumbering does not occur. - Each imported column maps offset one column to the left - Date import appears in cashbalid as integer Excel date codes - Data format collision produces nulls in the cashdate column - The cashmin column is null (no column to import) Routine #02.RMD, XLS or XLSX, No blank column, No ADD_MAPPING - Same results, except the cashbalid column is now autonumbered, overwriting the Excel date codes. Routine #01.RMD, XLS or XLSX, No blank column, ADD_MAPPING - Sweetness and light Routine #02.RMD, XLS or XLSX, No blank column, ADD_MAPPING - Stardust and Golden Command structure: GATEWAY IMPORT XLS + opcashbalance.XLSX + APPEND topcashbalance + OPTION ADD_MAPPING cashdate=A + |ADD_MAPPING cashbal=B + |ADD_MAPPING cashavg=C + |ADD_MAPPING cashdelta=D + |ADD_MAPPING cashmin=E -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Javier Valencia Sent: Friday, May 24, 2013 8:54 AM To: RBASE-L Mailing List Subject: [RBASE-L] - Re: Gateway Autonum Failure 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
