create XML from RPG read as EXCEL

[GLS400]

Re: create XML from RPG read as EXCEL

Hi All:

I've added numeric editing for decimal positions 0 thru 9. If you have decimals > 9 positions the data is there but can only be seen by using the excel functions [9 will be displayed]. (format..number...decimal...)

Column headings are in blue field names are in red.

You can now process up to 200 columns for each of 3 files.

Inter active existance testing is now being done...
Batch non-existing files are completely bypassed.

Column/Field headings are left/right adjusted if alpha/numeric.

If you need more colors or formulas.....have a go at it.

Best of Luck
GLS

[itp]

Re: create XML from RPG read as EXCEL

Here are some additional ideas on the subject.

http://tinyurl.com/q7v9h

[jamief]

Re: create XML from RPG read as EXCEL

Thats where all this started from Marty....We have now come full circle

[itp]

Re: create XML from RPG read as EXCEL

So true. Nice work guys!
There are after all, many ways to skin a cat!

[gregmonay]

Re: create XML from RPG read as EXCEL

Hi,
I tried downloading the program and compiled successfully.
But when I run it, it only generate the xml for the header. No detail is generated. I did a dspjoblog and get this error:

Host variable FIELD1 not compatible.

i think this is the field for sqldata.
Am i missing something?
I am on v5r2 and of course i have to modify the sql-execs to non-free format.

Thanks,
Greg

[GLS400]

Re: create XML from RPG read as EXCEL

Hi Greg:

I found this:

The nth host variable identified by the INTO clause or described in the SQLDA corresponds to the nth column of the result table of the cursor. The data type of each host variable must be compatible with its corresponding column.

here:

http://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/db2/rbafzmst109.htm

I'm guessing that changed at v5r3 or v5r4.
I hope someone else with more sql experiance can confirm that.

Best of Luck
GLS

[jamief]

Re: create XML from RPG read as EXCEL

I have caused myself this error many times by not matching my FETCH datastructure with my the fields sizes/types from the select statement.

jamie

[emily]

Re: create XML from RPG read as EXCEL

This code is great but is there anyway to attach it and send it in an email?

[jamief]

Re: create XML from RPG read as EXCEL

yes there is ......

http://www.code400.com/forum/showthread.php?t=2047&highlight=email+attachment

[emily]

Re: create XML from RPG read as EXCEL

Fantastic thank you so much!!!

[GLS400]

Re: create XML from RPG read as EXCEL

Hi All:

This version uses SQL to generate the xml-excel file. It works by creating a file in QTEMP (from the SQL) then translating that file to XML in the IFS.

You can copy/paste from the interactive sql editor (STRSQL), the input screen is 16 lines of 68 characters (the same as the interactive sql screen).

Being SQL you can have calculations and multiple files joined.

An alternative is to output the interactive sql to disk and use the previously posted version to create the xml file.

Best of Luck
GLS

[emily]

Re: create XML from RPG read as EXCEL

Well I'm back I've downloaded the programs to write the pf to the ifs as xml which is great and I've got the program to attach a csv file and email it. My problem is I need to either find a way to grab the xml off of the ifs and attach it and email it or is there a way to include field names for the csv file? I have several programs where the user needs the pf but they need to know what each column is else the attach program would be ideal. I also love the create as xml only most of our users don't have access to their ifs. Any ideas????

[jamief]

Re: create XML from RPG read as EXCEL

the examples I posted are used everyday here where I work.
( BTW .. the example I included allows you to sort by any column)

the data is written directly to the IFS....
(there is a place in userid to default home directory)

but since I use temp files they evaporate after program processes.

anyway all of the writting and sending of email take place using RPGLE in
an interactive or batch setting/

some code (gonna have to pick your own corn..)
if you need the procedure let me know......

      *=======================================================================
      * PROGRAM - ARC16
      * PURPOSE - List customers by outside sales#
      * WRITTEN - 
      * AUTHOR  - jamie flanary
      *
      * PROGRAM DESCRIPTION
      *   This program will display customers by outside sales#
      *
      *
      *
      * INPUT PARAMETERS
      *   Description        Type  Size    How Used
      *   -----------        ----  ----    --------
      *
      *
      * INDICATOR USAGE
      *   03 - Cancel current screen and return to previous screen
      *   25 - Rollup
      *   26 - RollDown
      *   30 - SFLCLR
      *   31 - SFLDSP
      *   32 - SFLDSPCTL
      *   33 - SFLEND
      *   70 - control view of phone number
      *   71 - position cursor
      *   72 - highlight line last used
      *   73 - display search field
      *
      *========================================================================
     FSLGSLMNB  IF   E           K DISK
     fSLGSLMNE  if   e           k disk     rename(slgslmnr:byuserid)
     fARC16AD   cf   e             WORKSTN INFDS(INFDS)
     f                                     SFILE(SUB01:RRN1)

       //
       //  entry plist
       //

     d ARC16           pr
     d  insales#                     15  5

     d ARC16           pi
     d  insales#                     15  5
      *
      * Program Info
      *
     d                SDS
     d  @PGM                 001    010
     d  @PARMS               037    039  0
     d  @MSGDTA               91    170
     d  @MSGID               171    174
     d  @JOB                 244    253
     d  @USER                254    263
     d  @JOB#                264    269  0
      *
      *  Field Definitions.
      *
     d company         s              2  0
     d contact         s             22
     d Count           s              4  0
     d CRLF            c                   CONST(X'0d25')
     d cymd            s              7  0
     d diffdays        s              4  0
     d EndScreen1      s              1    inz('N')
     d fromdate        s              7  0
     d fromdate2       s              7  0
     d fromdate3       s              7  0
     d Up              c                   CONST('ABCDEFGHIJKLMNOPQRSTUVWXYZ')
     d ISODate         s               D
     d lastdate        s              7  0
     d lastinv         s              8  0
     d lastsearch      s              4  0
     d lastyear        s              2  0
     d lastrrn         s              4  0
     d LenStr          s              4  0
     d Lo              c                   CONST('abcdefghijklmnopqrstuvwxyz')
     d messagecsc      s             10i 0
     d messagedata     s             80A
     d messagekey      s              4A
     d messagelen      s             10i 0
     d messagefile     s             20    inz('LBIMSG    *LIBL')
     d messageid       s              7
     d openords        s              5  0
     d outbody         s           3000    varying
     d outtype2        s              3
     d outtoname       s             35
     d outtoaddress    s             35
     d outsubject      s             50
     d outlibFile      s             21
     d outattachname   s             35
     d outspoolf       s             10
     d outjobname      s             10
     d outuser         s             10
     d outnumber       s              6
     d outspool#       s              6
     d phone           s             10  0
     d Q               s              1    inz('''')
     d RRN1            s                   like(SCRRN)
     d ReloadSUB01     s              1    inz('N')
     d saleslastYear   s              9  2
     d s2name          s             30
     d salesc          s              9  2
     d salesp          s              9  2
     d saleslr         s              9  2
     d SavRrn          s                   like(SCRRN)
     d ssearch         s             30
     d searchfield     s             30
     d ScreenError     s              1    inz('N')
     d soldto          s              5  0
     d sortby          s              1
     d sqlstmt         s          23000    varying
     d status          s              2
     d start           s              4  0
     d Str             s              4  0
     d stringout       s            512    varying
     d todate          s              7  0
     d todate2         s              7  0
     d todate3         s              7  0
     d Worktitle       s             40
     d worksales#      s              3  0
      *
      * formational data structure  Message subfile
      *
     d                 DS                        INZ
     d STKCNT                  1      4B 0
     d DTALEN                  5      8B 0
     d ERRCOD                  9     12B 0

      //
      //  external called programs
      //

     d $clearmsg       pr                  extpgm('QMHRMVPM')
     d   messageq                   276a   const
     d   CallStack                   10i 0 const
     d   Messagekey                   4a   const
     d   messagermv                  10a   const
     d   ErrorCode                  256

     d $sendmsg        PR                  ExtPgm('QMHSNDPM')
     d   MessageID                    7A   Const
     d   QualMsgF                    20A   Const
     d   MsgData                    256A   Const
     d   MsgDtaLen                   10I 0 Const
     d   MsgType                     10A   Const
     d   CallStkEnt                  10A   Const
     d   CallStkCnt                  10I 0 Const
     d   Messagekey                   4A
     d   ErrorCode                  256A

     d $titlecolor     pr                  extpgm('MSC08')
     d   company                      2s 0
     d   attribute                    1

     d $customerinq    pr                  extpgm('SLS09')
     d   customer                     5a   const

     d openList        pr
     d FetchNext       pr              n
     d closeList       pr
     d FetchNext2      pr              n

     d sqldata       e ds                  extname(aracust)

      // Command Keys

     d Cmd01           c                   const(x'31')                         Cmd-1
     d SearchMore      c                   const(x'32')                         Cmd-2
     d LeaveProgram    c                   const(x'33')                         Cmd-3
     d Cmd04           c                   const(x'34')                         Cmd-4
     d Cmd05           c                   const(x'35')                         Cmd-5
     d Cmd06           c                   const(x'36')                         Cmd-6
     d Cmd07           c                   const(x'37')                         Cmd-7
     d EmailReport     c                   const(x'38')                         Cmd-8
     d Cmd09           c                   const(x'39')                         Cmd-9
     d Cmd10           c                   const(x'3A')                         Cmd-10
     d Cmd11           c                   const(x'3B')                         Cmd-11
     d Cmd12           c                   const(x'3C')                         Cmd-12
     d Cmd13           c                   const(x'B1')                         Cmd-13
     d Cmd14           c                   const(x'B2')                         Cmd-14
     d Cmd15           c                   const(x'B3')                         Cmd-15
     d SetSort         c                   const(x'B4')                         Cmd-16
     d Cmd17           c                   const(x'B5')                         Cmd-17
     d Cmd18           c                   const(x'B6')                         Cmd-18
     d Cmd19           c                   const(x'B7')                         Cmd-19
     d Cmd20           c                   const(x'B8')                         Cmd-20
     d Cmd21           c                   const(x'B9')                         Cmd-21
     d Cmd22           c                   const(x'BA')                         Cmd-22
     d Cmd23           c                   const(x'BB')                         Cmd-23
     d Cmd24           c                   const(x'BC')                         Cmd-24
     d EnterKey        c                   const(x'F1')
     d RollUp          c                   const(x'F5')                         Roll Up
     d RollDown        c                   const(x'F4')                         Roll Down

     d Infds           ds                                                       INFDS data structure
     d Choice                369    369
     d Currec                378    379I 0

     d workdate        ds                  qualified  inz
     d  WholeTomato                   7  0
     d   century                      1    overlay(wholeTomato:1)
     d   year                         2    overlay(wholeTomato:*next)
     d   month                        2    overlay(wholeTomato:*next)
     d   day                          2    overlay(wholeTomato:*next)

     d APIError        ds                  Qualified
     d  BytesP                 1      4I 0 inz(%size(apiError))
     d  BytesA                 5      8I 0 inz(0)
     d  Messageid              9     15
     d  Reserved              16     16
     d  messagedta            17    256

     d $sendemail      pr                  extpgm('MSC52')
     d   intype                       3
     d   intoname                    35
     d   intoaddress                 35
     d   insubject                   50
     d   inlibFile                   21
     d   inattachname                35
     d   inbody                    3000    varying
     d   inspoolf                    10
     d   injobname                   10
     d   inuser                      10
     d   innumber                     6
     d   inspool#                     6
      *
     d $showorders     pr                  extpgm('ARC18')
     d   OutCustomer                 15  5 const
      *
     d $customernotes  pr                  extpgm('ARC19')
     d   OutCustomer                 15  5 const
      *
     d $blanketorders  pr                  extpgm('PCH66')
     d   OutCustomer                 15  5 const
      *

      /copy qpgmsrc,wkr1ctrl
      /copy qprcsrc,TITLE_CP
      /copy qprcsrc,emailad_cp

      *
      /free
              exsr Hskpg;
              exsr $Screen1;

              *inlr = *on;

        //--------------------------------------------------------
        // $Screen1 - parameter screen
        //--------------------------------------------------------
             begsr $Screen1;

             reset  EndScreen1;
              dow  EndScreen1 = 'N';

               if ScreenError = 'N';
                $clearmsg('*' : *zero : *Blanks : '*ALL' : APIError);
               endif;

               *in73 = *off;
               if ssearch <> *blanks;
                *in73 = *on;
               endif;
               write FKEY01;
               write MSGCTL;
               exfmt SUB01CTL;
               $clearmsg('*' : *zero : *Blanks : '*ALL' : APIError);
               reset ScreenError;
               if Currec <> *Zeros;
                RRN1  =  Currec;
                SCRRN =  Currec;
               endif;

               select;
            //
            // F3 pressed end the program F3 = LeaveProgram
            //
                when  Choice = LeaveProgram;
                 EndScreen1 = 'Y';
            //
            // F8 pressed set sort field
            //
                when  Choice = setsort;
                 select;
                  when #FLD = 'S1SL#';
                  sortby = '1';
                  when #FLD = 'S1CNAME';
                  sortby = '2';
                  when #FLD = 'S1OPEN';
                  sortby = '3';           //desc
                  when #FLD = 'S1SCYTD';
                  sortby = '4';           //desc
                  when #FLD = 'S1SLYTD';
                  sortby = '5';           //desc
                  when #FLD = 'S1LASTYEAR';
                  sortby = '6';           //desc
                  when #FLD = 'S1LASTINV';
                  sortby = '7';           //desc
                  when #FLD = 'S1DAYS';
                  sortby = '8';           //desc
                 other;
                  sortby = '2';
                 endsl;
                 exsr $clearsfl;
                 exsr $loadsfl;

            //
            // continue the search
            //
                when  Choice =  Searchmore;
                 exsr $search;
                 if start = 1;
                  clear lastsearch;
                  exsr $search;
                 endif;

            //
            // Email Report
            //
                when  Choice = EmailReport;
                 exsr $emailreport;
            //
            // Enter Key pressed
            //
                when  Choice = enterKey;
                 if c1customer = *blanks;
                  exsr $process;
                 else;
                  if c1customer <> ssearch;
                   clear lastsearch;
                   start = 1;
                   clear count;
                  endif;
                  ssearch =  c1customer;
                  clear c1customer;
                  exsr $search;
                 endif;

                endsl;
               enddo;

             endsr;

        //----------------------------------------
        // $search - customer search
        //----------------------------------------
             begsr $search;


              if lastrrn > *zeros;
               lastsearch = lastrrn;
               lastrrn = *zeros;
              endif;

              if lastsearch > *zeros;
               chain lastsearch SUB01;
               if %found;
                *in71 = *off;
                *in72 = *off;
                update sub01;
                if lastsearch < savrrn;
                 start = lastsearch + 1;
                else;
                 start = 1;
                 clear count;
                endif;
               endif;
              endif;


              for count = start to savrrn;
               chain count sub01;
               if %found;
                searchfield = %xlate(lo:up:s1cname);
                if %scan(%trim(ssearch):searchfield) > *zeros;
                 RRN1 = count;
                 SCRRN = rrn1;
                 *in72 = *on;
                 update sub01;
                 lastsearch = count;
                 leave;
                endif;
               endif;
              endfor;

              if count >= savrrn;
               start = 1;
              endif;
              *in72 = *off;

             endsr;

        //----------------------------------------
        // $process - process subfile
        //----------------------------------------
             begsr $process;

              if lastrrn > *zeros;
               chain lastrrn SUB01;
               *in71 = *off;
               *in72 = *off;
               update sub01;
              endif;

              for count = 1 to savrrn;
               chain count SUB01;
               if %found(ARC16AD);
                select;
                 when s1opt = '1';     // show orders/Quotes
                  $showorders(S1SL#);
                  lastrrn = count;
                  clear s1opt;
                  *in71 = *on;
                  *in72 = *on;
                 when s1opt = '5';
                  $customerinq (%editc(S1SL#:'X'));
                  lastrrn = count;
                  clear s1opt;
                  *in71 = *on;
                  *in72 = *on;
                 when s1opt = '7';
                  $customernotes(S1SL#);
                  lastrrn = count;
                  clear s1opt;
                  *in71 = *on;
                  *in72 = *on;
                 when s1opt = '8';
                  $blanketorders(S1SL#);
                  lastrrn = count;
                  clear s1opt;
                  *in71 = *on;
                  *in72 = *on;
                endsl;
               endif;
               update sub01;
               *in71 = *off;
               *in72 = *off;
              endfor;

             endsr;

        //----------------------------------------
        // $clearSfl - clear the subfile
        //----------------------------------------
             begsr $clearSFL;

              // clear the subfile first

               *in31 = *Off;
               *in32 = *Off;
               *in30 = *On;

               write  SUB01CTL;

               *in31 = *On;
               *in32 = *On;
               *in30 = *Off;

               clear RRN1;
               clear SCRRN;
               clear SavRrn;

             endsr;

        //--------------------------------------------------------
        // $loadsfl- load up the entire subfile
        //--------------------------------------------------------
             begsr $loadsfl;

              if  SavRrn  > *zeros;
               RRN1  =  SavRrn;
               SCRRN =  SavRrn;
              endif;

              sqlstmt = 'select * from arc16work ';

              select;
               when sortby = '1';
                sqlstmt = %trim(sqlstmt) +
                        '  order by  soldto ';
               when sortby = '2';
                sqlstmt = %trim(sqlstmt) +
                        '  order by  s2name ';
               when sortby = '3';
                sqlstmt = %trim(sqlstmt) +
                        '  order by  openords  desc ';
               when sortby = '4';
                sqlstmt = %trim(sqlstmt) +
                        '  order by  salesc desc ' ;
               when sortby = '5';
                sqlstmt = %trim(sqlstmt) +
                        '  order by salesp desc ' ;
               when sortby = '6';
                sqlstmt = %trim(sqlstmt) +
                        '  order by saleslr desc ' ;
               when sortby = '7';
                sqlstmt = %trim(sqlstmt) +
                        '  order by lastinv desc ' ;
               when sortby = '8';
                sqlstmt = %trim(sqlstmt) +
                        '  order by diffdays desc ' ;
              endsl;

              openList();
              dow fetchNext2();

               s1sl# = SOLDTO;
               s1cname = S2NAME;
               s1open = OPENORDS;
               s1scytd = SALESC;
               s1slytd = SALESP;
               s1lastyear = SALESLR;
               s1lastinv = LASTINV;
               s1days = DIFFDAYS;
               s1status = STATUS;

               clear s1diff;
               if s1scytd < s1slytd;
                s1diff = '*';
               endif;

           // get last call date

               exec sql
                select coalesce(max(scdat),0 )
                   into :CYMD
                   from  SLCOUT
                   where SCSL# = : SOLDTO;
               clear s1lastcall;
               if cymd <> *zeros;
                isodate = %date(cymd:*cymd);
                s1lastcall = %dec(isodate:*iso);
               endif;

               RRN1 += 1;
               SCRRN = RRN1;
               write SUB01;
               *in70 = *off;
              enddo;
              closeList();

              *in33 = *on;
              savrrn = SCRRN;

           //
           //  If no records in subfile then do not disply the subfile.
           //
              if SavRrn  = *zeros;
               *in31 = *off;
              else;
               RRN1  = 1;
               SCRRN  = 1;
              endif;

             endsr;

        //----------------------------------------
        // $gatherdata - build the sortable work
        //               file.
        //----------------------------------------
             begsr $gatherdata;

              sqlstmt = 'select * from ARACUST ' +
                        ' where AAOSP =  ' + %editc(worksales#:'X') +
                        ' and aasl# <> 0 and AAACTS <> ' + Q + 'I' + Q +
                        ' and  AAOSP = ' + %editc(worksales#:'X') +
                        '  order by  AASLNM ';
              openList();

              dow fetchNext();
               s2name = title(AASLNM);
               soldto = AASL#;

                // get total open orders
               clear openords;
               exec sql
                select coalesce(count(*),0 )
                   into :openords
                   from oehdord
                   where OHSL# = :soldto and OHPRO7 <> 0 ;

                // get last invoice date
               clear cymd;
               clear lastinv;
               exec sql
                select  coalesce(max(oEIDAT),0)
                   into :cymd
                   from OEEHDHY
                   where OESL# = :soldto;
               test(de) *cymd cymd;
               if not%error;
                isodate = %date(cymd:*cymd);
                lastinv = %dec(isodate:*iso);
               endif;

               contact = title(AASCON);
               phone = %dec(%editc(AASLSAREA:'X') +
                       %editc(AASLSPHON:'X') : 10:0);

             // total year to date sales for customer
               clear salesc;
               exec sql
               select coalesce(sum( ANMAT + ANLAB + ANTAX),0 )
               into : salesc
               from ARNISMH
               where
               anSL#  =  : soldto
               and   ANIDAT  >= : fromdate and ANIDAT  <= : todate and
               ansp = : worksales# and  ANCOMP  <> 0;

             // total LAST year to date sales for customer
               clear salesp;
               exec sql
               select coalesce(sum( ANMAT + ANLAB + ANTAX),0 )
               into : salesp
               from ARNISMH
               where
               anSL#  =  : soldto
               and   ANIDAT  >= : fromdate2 and ANIDAT  <= : todate2 and
               ansp = : worksales# and  ANCOMP  <> 0;

               // get days since last invoice
               clear diffdays;
               if cymd <> *zeros;
                diffdays = %diff(%date():Isodate:*days);
               endif;
                  // sales last year (test)
               clear saleslr;
               exec sql
               select coalesce(sum( ANMAT + ANLAB + ANTAX),0 )
               into : saleslr
               from ARNISMH
               where
               anSL#  =  : soldto
               and   ANIDAT  >= : fromdate3 and ANIDAT  <= : todate3 and
               ansp = : worksales# and  ANCOMP  <> 0;


               // write to workfile
               exec sql
                  insert into arc16work values(
                              :soldto,
                              :s2name,
                              :openords,
                              :salesc,
                              :salesp,
                              :saleslr,
                              :lastinv,
                              :diffdays,
                              :AAACTS
                                      );
              enddo;
              closeList();

             endsr;

        //----------------------------------------
        // $sendmessage - send the program message
        //----------------------------------------
             begsr $sendmessage;

              $sendmsg(messageID   :
                       messageFile :
                       messagedata :
                       messageLen  :
                       '*DIAG'     :
                       @PGM        :
                       messagecsc  :
                       messagekey  :
                       APIError
                                   );

             endsr;

        //--------------------------------------------------------
        // $emailreport - email the report
        //--------------------------------------------------------
             begsr $emailreport;

                exfmt win1;
                if w1email <> *blanks and not*in03;
                                                                     //...........................
                 outtype2 = 'CSV';                                   // can be either PDF or CSV
                 outtoname = @USER;                                  // if userid passed else
                 outattachname = %trim(@user)+'_ARC16';              // caller ID used
                 outtoaddress = %trim(w1EMAIL);                      // to email address
                 outsubject = 'Customer List By SalesPerson';        // email subject
                 outlibFile = 'QTEMP/ARC16WORK';                     // where is the physical file
                                                                     // for PDF spooled file name
                                                                     // needed.
                                                                     // ...........................

            // create the body.... the body text will be broken down
            // in lengths of 100 the size is 3000 that's 30 lines

                 isodate = %date();
                 exec sql
             SELECT DAYNAME(:Isodate) || ', ' || MONTHNAME(:Isodate) || ' ' ||
                 DAY(:Isodate) || ', ' || YEAR(:Isodate)  as longdate INTO
                 :Stringout from SYSIBM/SYSDUMMY1 ;

                  outbody =  'Report Converted to CSV  '    +
                             'created ' +  %trim(stringout) + CRLF +
                             ' Time: ' + %char(%time()) +  '  ' +
                             'Info: ' + %trim(C1SNAME) +  CRLF;

                  $sendemail(  outtype2               :
                               outtoname              :
                               outtoaddress           :
                               outsubject             :
                               outlibFile             :
                               outattachname          :
                               outbody                :
                               outspoolf              :
                               outjobname             :
                               outuser                :
                               outnumber              :
                               outspool#

                                                      );

                endif;
             endsr;

        //--------------------------------------------------------
        // Hskpg - one time run subroutine
        //--------------------------------------------------------
             begsr Hskpg;

              in   dactrl;
              company = DASRVC;
              PGMQ = @PGM;

              isodate = %date();
              workdate.WholeTomato = %dec(isodate:*cymd);
              lastyear = %dec(workdate.year:2:0) - 1;

              fromdate = %dec(workdate.century +
                              workdate.year    +
                              '0101':7:0);
              todate   = %dec(workdate.century +
                              workdate.year    +
                              workdate.month   +
                              workdate.day:7:0);

              fromdate2 = %dec(workdate.century +
                              %editc(lastyear:'X') +
                              '0101':7:0);
              todate2   = %dec(workdate.century +
                              %editc(lastyear:'X') +
                              workdate.month   +
                              workdate.day:7:0);

              fromdate3 = %dec(workdate.century +
                              %editc(lastyear:'X') +
                              '0101':7:0);
              todate3   = %dec(workdate.century +
                              %editc(lastyear:'X') +
                              '1231':7:0);
               if %parms >= 1;
                worksales# = insales#;
                chain (worksales#) SLGSLMNB;
                if %found(SLGSLMNB);
                 c1sname = SGSPNM;
                else;
                 c1sname = 'House';
                endif;
               else;
                if   worksales# = *zeros;
                 chain (@user)  SLGSLMNE;
                 if %found(SLGSLMNE);
                  worksales# = SGSP;
                  c1sname = SGSPNM;
                 endif;
                endif;
               endif;

               workTitle = 'Customers By Outside Salesperson';
               LenStr =
               ((%len(workTitle) - %len(%trim(workTitle))) / 2) + 1;
               %subst(C1TITLE:LenStr) = %trim(workTitle);
               hdprogram = @PGM;

               exec sql
                 drop table  qtemp/arc16work;

               // build the tempory work file so that we can multi-sort
               exec sql
                create table qtemp/arc16work
                (soldto dec(5), s2name char(30), openords dec(5),
                salesc dec(9,2), salesp dec(9,2), saleslr dec(9,2),
                lastinv dec(8,0) , diffdays dec(4,0),status char(1) ) ;

                exec sql label on column qtemp/arc16work(
                 soldto      text is 'Sold To',
                 s2name      text is 'Customer Name' ,
                 openords    text is 'Open Orders' ,
                 salesc      text is 'Current YTD' ,
                 salesp      text is 'Previous YTD' ,
                 saleslr     text is 'Sales Last Year',
                 lastinv     text is 'Last Invoice Date',
                 diffdays    text is 'Days Last Invoiced',
                 status      text is 'Customer Status'
                                                              );

               w1email = %trim(emailad(@USER : 'L'));
               exsr $clearsfl;
               SORTBY = '4';       // sales current YTD
               exsr $gatherdata;
               exsr $loadsfl;

             endsr;

      /End-Free
      *--------------------------------------------------------
      *  openList  - Open a cursor to read file
      *--------------------------------------------------------
     p openList        b

     d openList        pi

      /Free

       exec sql
        declare MyCursor cursor for statement;

       exec sql
        prepare statement from :sqlstmt;

       exec sql
        open mycursor;

      /End-Free

     p openList        e
      *--------------------------------------------------------
      *  fetchNext  - read one record at a time
      *--------------------------------------------------------
     p fetchNext       b

     d fetchNext       pi              n

      /free

       exec sql
        fetch next from mycursor into : sqldata;
         if sqlstt < '02000';
           return *on;
         else;
           return *off;
         endif;

      /end-free

     p fetchNext       e
      *--------------------------------------------------------
      *  fetchNext2 - read one record at a time
      *--------------------------------------------------------
     p fetchNext2      b

     d fetchNext2      pi              n

      /free

       exec sql
        fetch next from mycursor into : SOLDTO,
                                      : S2NAME,
                                      : OPENORDS,
                                      : SALESC,
                                      : SALESP,
                                      : SALESLR,
                                      : LASTINV,
                                      : DIFFDAYS,
                                      : STATUS
                                           ;

         if sqlstt < '02000';
           return *on;
         else;
           return *off;
         endif;

      /end-free

     p fetchNext2      e
      *--------------------------------------------------------
      *  closeOrderList  - Close the OrderHdr cursor
      *--------------------------------------------------------
     p closeList       b

     d closeList       pi

      /free

       exec sql
        close MyCursor;

      /end-free

     p closeList       e

jamie

[emily]

Re: create XML from RPG read as EXCEL

Thanks so much I'll try from this

[emily]

Re: create XML from RPG read as EXCEL

I feel like a complete idiot at this point. Jayme the code you provided was in sql which I understand but the code I copied to send the csv file is in a different code type I'm not familiar with. I can see where the code should go but I have no knowledge of how to do it.

         // ------------------------------------------
          //  Insert the headers for the CSV file
          // ------------------------------------------

          body =
           '--' + boundary + CRLF
          +'Content-Type: text/csv; name="' + attname + '"' + CRLF
          +'Content-Transfer-Encoding: base64' + CRLF
          +'Content-Disposition: attachment;'
          +    ' filename="' + attname + '"' + CRLF
          + CRLF;
          callp write(fd: %addr(body)+2: %len(body));

All I want in reality is the field names from the pf that would work great but I can just type them in the way I saw you doing. In this code how do I say:
unitnum text is "Unit Number"
Status tex is "Status"
in this code type. So sorry to be such a pain but this is the last piece of the puzzle.
Thanks,