Steve:
I use the R:BASE CURRENCY default format. For at least seven years all
import-source financial Excel data has been in [Number(2)(No Comma)]
format, on the instruction to present numbers as unformatted as
possible.
Hence my surprise, now.
Next test: Create temp table columns as DOUBLE, import; then INSERT as
CURRENCY from temp to permanent table.
Thanks for the clue.
Bruce
------ Original Message ------
Sent: 8/17/2017 12:36:38 PM
Subject: RE: Re[3]: [RBASE-L] - Error Message 122 - Gateway Import XLS
Fail
From: "Stephen Markson" <[email protected]>
To: "[email protected]" <[email protected]>
Cc:
Hi Bruce,
Sounds like you need to change either the source currency format or the
R:Base CURRENCY settings so that they match.
Regards,
Stephen Markson
The Pharmacy Examining Board of Canada
416.979.2431 x251
From:[email protected] [mailto:[email protected]] On
Behalf Of Bruce Chitiea
Sent: August-17-17 1:58 PM
To:[email protected]
Subject: Re[3]: [RBASE-L] - Error Message 122 - Gateway Import XLS Fail
Razzak:
R> show currency
CURRENCY SYMBOL '$'
CURRENCY LOCATION PREF
CURRENCY DIGITS 2
CURRENCY CONVENTION B
With the Excel source formatted as [Number(2)(No Comma)], coded and
manual Gateway processes produce:
'-ERROR- Column TReserveBalanceCD must be a valid CURRENCY. ( 122)'
Testing for faulty source data:
1. Regenerate the Excel source five times, inspect formats. No obvious
anomalies. Drop/Create temp table. Fail, as above;
2. Save Excel source as .CSV. Fail, as above;
3. Hand-type first row. Success!
Compare Excel source to Gateway preview value:
1. Excel Format [Number(10)(No Comma)].
- Original format: 1404006.50
- New format: 1404006.5000000000
- Gateway preview: 1404006.4999999998
2. Excel Format [Currency(10)].
- Original format: 1404006.50
- Resulting display: $1,404,006.5000000000
- Gateway preview: $1,404,006.4999999998
Close enough for horseshoes, hand-grenades and the girl I go with.
Truly at a loss.
Bruce Chitiea
SafeSectors, Inc.
909.238.9012 Mobile
------ Original Message ------
Sent: 8/16/2017 8:33:47 PM
Subject: Re[2]: [RBASE-L] - Error Message 122 - Gateway Import XLS Fail
From: "A. Razzak Memon" <[email protected]>
To: [email protected] <mailto:[email protected]>
Cc:
Bruce,
Check your connected database CURRENCY settings.
SHOW CURRENCY
Very Best R:egards,
Razzak
At 09:52 PM 8/16/2017, Bruce Chitiea wrote:
Thank you, Razzak.
With a new, clean temp file, the manual process adds no records to the
table.
Error Message at R>:
'-ERROR- Column TReserveBalanceCD must be a valid CURRENCY. ( 122)'
The formatted value in the TReserveBalanceCD cell within the Excel
source file is: '1404006.50' [Number(2)]
I will try a CSV import in the morning and report back.
Bruce
------ Original Message ------
Sent: 8/16/2017 5:56:57 PM
Subject: Re: [RBASE-L] - Error Message 122 - Gateway Import XLS Fail
From: "A. Razzak Memon" <[email protected]>
To: [email protected] <mailto:[email protected]>
Cc:
Bruce,
Once the temporary table is created successfully, try the manual
process to IMPORT
the exact gway_ReserveFundBalance.xlsx file, and see what you get.
Hope that provides you with some blue's clues ...
Vert Best R:egards,
Razzak
At 08:48 PM 8/16/2017, Bruce Chitiea wrote:
WIN10
Error Message:
[]
This in a GATEWAY IMPORT XLS routine which has worked without drama
for over two years. Relevant code:
DEBUG SET TRACE ON
SET ERROR MESSAGE 2038 OFF
  DROP TABLE Import_ReserveFundBalance_tt
SET ERROR MESSAGE 2038 ON
CREATE TEMP TABLE Import_ReserveFundBalance_tt +
   ( TReserveBalanceDATE  DATE NOT NULL, +
    TReserveBalanceCASH  CURRENCY NOT NULL, +
    TReserveBalanceCD   CURRENCY NOT NULL, +
    TReserveBalanceTOTAL CURRENCY NOT NULL, +
    TReserveWithdrawCUMUL CURRENCY NOT NULL, +
    TReserveAddCUMUL    CURRENCY NOT NULL, +
    TReserveContribCUMUL CURRENCY NOT NULL, +
    TReserveInflowNET   CURRENCY NOT NULL, +
    TReserveDailyCHANGE  CURRENCY NOT NULL )
GATEWAY IMPORT XLS +
 D:\00\GWAY\gway_ReserveFundBalance.xlsx +
 APPEND Import_ReserveFundBalance_tt +
 OPTION FIRST_ROW 2 +
 |ADD_MAPPING TReserveBalanceDATE = A +
 |ADD_MAPPING TReserveBalanceCASH = B +
 |ADD_MAPPING TReserveBalanceCD = C +
 |ADD_MAPPING TReserveBalanceTOTAL = D +
 |ADD_MAPPING TReserveWithdrawCUMUL = E +
 |ADD_MAPPING TReserveAddCUMUL = F +
 |ADD_MAPPING TReserveContribCUMUL = G +
 |ADD_MAPPING TReserveInflowNET = H +
 |ADD_MAPPING TReserveDailyCHANGE = I -- FAIL
The Excel source data is [Number(2)], no dollar sign, no comma.
The Excel data has been scrubbed, checked, re-formatted; file deleted
and new one created three times; and the error repeated through one
unload/reload and ten follow-on tests.
Mystified. Thoughts? Course of action?
-- For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
--- You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
For more options, visit https://groups.google.com/d/optout.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
<http://www.rbase.com/support/usersgroup_guidelines.php>
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
For more options, visit https://groups.google.com/d/optout.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google
Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected].
For more options, visit https://groups.google.com/d/optout.
--
For group guidelines, visit
http://www.rbase.com/support/usersgroup_guidelines.php
---
You received this message because you are subscribed to the Google Groups "RBASE-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
For more options, visit https://groups.google.com/d/optout.
