RE: Best Way to get Excel data to AS/400

["Phil" <sublime78ska@xxxxxxxxx>]

Chuck,

The easiest way, IMO, is to parse a comma-delimited file in RPG (unless you
can connect to the RS6000).  And I'd read the file directly from the IFS
(but you'd better check the RPG list for that) but you can also copy it into
a database file with one field and read that.  Only caveat is you need a way
to identify the last column because there won't be a comma after the last
column.  Which is why this snippet looks for x'0D' but that character won't
exist in an as400 db file.  This snippet also shows how I handled signed
numeric data with decimal positions.

Essentially, all that is needed is %SUBST and %SCAN.  Maybe that's why so
many people were eager to offer their services ;-)

Phil

<snip>
D rate            s             10
D rateWk          ds             7
D  rateWk2                       7  2
....
C                   for       i = 1 by 1 to columnCount
C                   eval      length = %scan(',':csvData:offset)
C                   if        length > *zero
C                   exsr      parseSR
C                   eval      offset = length + 1
C                   else
C                   eval      length = %scan(x'0D':csvData:offset)
C                   if        length > *zero
C                   exsr      parseSR
C                   eval      offset = length + 1
C                   endif
C                   endif
C
C                   endfor
......
C     parseSR       begsr
C                   select
C* F bol
C                   when      i = 1
C                   eval      bol = *blank
C                   eval      bol = %subst(csvData:offSet
C                             :length-offSet)
C* F amount
C                   when      i = 2
C                   eval      rate = *blank
C                   eval      rate = %subst(csvData:offSet
C                             :length-offSet)
C                   eval      pos1 = %scan('-':rate)
C                   if        pos1 <> *zero
C                   eval      factor = -1
C                   eval      %subst(rate:pos1:1) = ' '
C                   else
C                   eval      factor = 1
C                   endif
C                   eval      pos1 = *zero
C
C                   dou       pos1 = *zero
C                   eval      pos1 = %scan('.':rate)
C                   if        pos1 > *zero
C                   eval      rate = %subst(rate:1:pos1-1) +
C                             %subst(rate:pos1+1)
C                   endif
C                   enddo
C
C                   evalr     rate = %trim(rate)
C                   dou       pos1 = *zero
C                   eval      pos1 = %scan(' ':rate)
C                   if        pos1 > *zero
C                   eval      %subst(rate:pos1:1) = '0'
C                   endif
C                   enddo
C                   move      rate          ratewk
C                   move      ratewk2       hsamt
C                   eval      hsamt = hsamt * factor
......


> >
> > Hi Folks,
> >
> > We are needing to get data from an RS/6000 to the AS/400.
> > These folks can
> > download the files we need via a report writer into Excel and they are
> > emailing them to us.
> >
> > We can save this information to a CSV file, TXT file, etc.
> >
> > My question is what is the most "painless" way to get this data to our
> > AS/400 ?
> >
> > We are running the Synapse TCP/IP emulation product and it has
> > Upload/Download GUI programs aka CA. I've done this before
> > but it can be a
> > hassle with numbers, etc.
> >
> > And then there is also the FTP option which I would think
> > would be a good
> > option but I have never done much more than uploads of text
> > information with
> > that...
> >
> > So what's the consensus on best way to do this ? We are on V4R5.
> >
> > Thanks !
> >
> > Chuck
> >