Re: What is the recommended way to handle character cells that have numeric values?

On Thu, Nov 12, 2015 at 3:19 PM, Buck Calabro <kc2hiz@xxxxxxxxx> wrote:

On 11/12/2015 2:59 PM, Darryl Freinkel wrote:

I have the situation the a column has values that are sometimes numeric and
sometimes character. Example: 22.

The value brought in is 22.00.

Is this a case where I need to override the system value and if I know it's
character use the string value?

Fortunately, the cell itself knows if it is holding a character or a
number.

The issue is that some cells in a given column can be Excel numeric
while other cells in the same column can be Excel character. And this
should never be the case in a database table. (At least not a
traditional database like DB2.)

So, assuming we are reading Excel values so that we can import them
into a DB2 table, the proper thing to do is to cast the Excel value,
if needed, to the type which is declared for the receiving column in
the table.

Now, the tricky thing here is that Excel only has one numeric type:
float. There is, by definition, no fundamental difference between
22.00, 22.0, and 22 in Excel.

If the receiving column (or RPG variable) is a numeric type, then you
can usually just use %INT or %DEC as necessary to do the cast.

If the receiver is character, then you have to be careful. In my
experience, the vast majority of cases involving a character column
and "numeric-looking" input involve values that can only reasonably be
digits (think 5-byte U.S. postal codes). In these cases, you assume
that the cell represents an integer and either "pre-cast" Excel's
float to integer before using %CHAR, or do %CHAR first and strip off
any "floaty" parts.

For example, if you've got a Boston area code like '02108', it could
be stored in Excel as character '02108', in which case you use it
as-is; or it could be in Excel as float 2108.0, in which case you do
something like

myvar = %char(%int(xlvar));

Because RPG is statically typed, you do need to check the type of the
Excel value first, which is where Buck's/Scott's example comes into
play.

(And this is one area where dynamic languages like PHP, Ruby, or
Python make life tremendously easier.)

John Y.