Re: XLParser treats text fields as numeric fields

On 1/15/2013 2:39 AM, Dirk Marien wrote:

Hi,

I?m using Scott Klement?s XLS Parser and it works file. However, the
parser sometimes treats zones in excel formatted as text as numeric
fields. For example : the as text formatted cel 000123 is parsed as 123
numeric where it should be treated as 000123 alpha. When I put an
apostrophe befor the zone (?00123) it is treated correctly. Unfortunately,
putting an apostrophe before each zone is no option for me?. . Anybody any
suggestions ?

I'm guessing you're using something like XLS2TABLE. Very cool utility,
but Excel has... issues. We RPG programmers think of a field (or an
Excel column) as having a name, a type, a length and a decimal position.
Excel thinks of a cell as being a container for data of any type at
all. So A1 can be reported as text, A2 a number, A3 a date (which Excel
thinks is a number!) and so on. Excel isn't nearly as strict about
variables as we are.

XLS2TABLE relies on Excel properly reporting the cell type, like this:
select;
when type = CELL_TYPE_STRING;
when type = CELL_TYPE_FORMULA;
when type = CELL_TYPE_NUMERIC;
other;
endsl;

What I've done is to write my own utility (Thanks for the source,
Scott!) and I don't rely on what Excel reports, I rely on what I
actually see in the spreadsheet. I use the same sort of select...endsl
group to decode the contents of the cell, but then depending on the
column, I push that value into the real datatype.

So for your example, I might have
select;
when columnLetter = 'J';
zoneCode = Cell2Char(cell);
endsl;

and inside Cell2Char, I'd extract the cell contents based on the
reported CellType and then move that into the output return variable.
--buck