FWIW to anyone:
Excel source values for import are top-level sums drilled-down through
multiple worksheets, copied as values-only to an export worksheet. I
discovered a my-bad formula change in one drilled worksheet which caused
a formatting error within the top-level sum in a column not implicated
as the problem. This, in turn affected the other summed columns, even
though they did not draw upon values in this column. Values copied for
export held ten-decimal place non-rounded values instead of zeroes, e.g.
1404006.4000999998 instead of 1404006.50.
I rounded the formula for the offending top-level sum column to two
decimal places in the top-level worksheet. Once rounded, the Excel sum
formula for that column's cells returned to two decimal places, as did
the values-only copy for export, as did the values displayed by the
manual Gateway preview, which then imported without issue.
Thank you for your assistance.
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.
