Re: Date validation of different formats in RPG

[Scott Klement <klemscot@xxxxxxxxxxxx>]

> 2) Scott - thanks for the ideas. Since most data arrives as .xls and can be
> in varying formats, we've been using StrPcCmd to pass the data through Excel
> and save it to the IFS with the help of some VB code.

Dates in Excel are stored as a number of days since Jan 1, 1900, and are
stored in a 8F variable (64-bit floating point) The format of the date is
a separate field that is only used for presenting it to the user.

Since dates in Excel are always in this format, there's no reason for this
rather error prone and complex process of determining the date format.  A
simple routine like the following is all you need (this is a copy/paste
from my own Excel parsing code)

/free

   wwDate = wwStrDate + %days(%int(peXls) - 1);

   // Excel incorrectly thinks that 1900-02-29 is
   //  a valid date.

   if (wwDate > d'1900-02-28');
        wwDate = wwDate - %days(1);
   endif;

   return wwDate;

/end-free


> Excel allows the operators to view, re-arrange columns and format cells
> prior to using CpyFrmStmf to populate a work-file in qsys.lib which is
> fed to a batch validation/posting routine. All this occurs within one CL
> and actually works pretty smoothly and quickly, but I was hoping to
> remove some of the manual intervention. I was wondering how other folks
> have handled similar situations. Given the variability of the input, it
> may not be possible to simplify the import process any further.

On the contrary, the dates being in Excel makes everything much simpler.
You just need to get rid of your VB nastiness and use POI/HSSF -- or if
you're going to use VB to parse the Excel data, have the VB program write
the dates out in an unambiguous format.

>  Excel is made for just that sort of data manipulation and it makes no sense
> to try to duplicate it with an RPG subfile. I may enhance the date checking
> with CEEDAYS, though. One of these days I'll finish implementing those
> RPG/POI routines and just read the .xls directly into RPG...

Have you seen my article on parsing excel documents on the iSeries
Network?  With the code from that article, this should be a rather simple
thing to do.

Here's a link to the article.  It requires a (free) membership to view:
http://www.iseriesnetwork.com/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=17839