ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

automated download of PF to Excel file with Client Access

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • automated download of PF to Excel file with Client Access

    This DOS .bat file automates download of order header and detail extract files from As/400 and writes them in Excel format on PC server using Client Access. A detailed log is written to history.log.

    PHP Code:
    rem This batch file transfers 2 files (Order Header Extract and Order Detail Extract)
    rem for Acme Fireworks Company
    rem Created by
    W_E_COYOTE
    rem Date
    November 232006
    rem pour service voir Pierre

    rem 
    use cwblogon.exe to force a username/password temporarily into cache so script can run fully unattended. (egin PC job scheduler)

    C:\Progra~1\IBM\Client~1\cwblogon.exe server /U usr /P Psw

    rem
    perform download of Header Records with client access batch download program RXFERPCB.EXE
    echo 
    echo @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> 
    history.log

    echo Start Header file...... %date% %time% >> history.log
    rem
    Delete file before download. (In case server application is still holding fileyour @#%@#%@#% is covered by log file)
    delete F598019.xls >> history.log
    C
    :\Progra~1\IBM\Client~1\RXFERPCB.EXE F598019.dtf USR PSW >> history.log
    echo End Header file........ %date% %time% >> history.log
    echo --------------------------------------------------------------------- >> history.log

    rem
    perform download of Header records with client access batch download program RXFERPCB.EXE
    delete F598119
    .xls >> history.log
    echo Start Detail file...... %date% %time% >> history.log
    C
    :\Progra~1\IBM\Client~1\RXFERPCB.EXE F598119.dtf USR PSW >> history.log
    echo End Detail file........ %date% %time% >> history.log

    echo @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> history.log 

  • #2
    Re: automated download of PF to Excel file with Client Access

    rep power of 666 Marty I had no idea you were "HIM" !
    All my answers were extracted from the "Big Dummy's Guide to the As400"
    and I take no responsibility for any of them.

    www.code400.com

    Comment


    • #3
      Re: automated download of PF to Excel file with Client Access

      Hi I tried the above script ....

      the only chnage i made is
      C:\Progra~1\IBM\CLIENT~1\RFROMPCB.EXE
      F598019.tto username password 1>>history.log

      Rason being I dont have RXFER in my client acccess folders. hence i used RFROMPCB.EXE

      But i am not getting the excel sheet.
      History log shows the below error

      F598019.dtf is not a valid transfer to iSeries request file.
      Transfer failure - exiting.

      But i checked the .dtf file and its perfect and using that .dtf client access is able to transfer the file.

      Even i tried with .tto file but no luck.

      Any suggestions ???
      Thanks,
      Giri

      Comment


      • #4
        Re: automated download of PF to Excel file with Client Access

        Sorry I should use RTOPCB.EXE ...

        Let me try that ...
        Thanks,
        Giri

        Comment


        • #5
          Re: automated download of PF to Excel file with Client Access

          Yes its working ...

          But one doubt.
          Even thought the log says "User ID and password successfully stored in iSeries Access for Windows cache".

          Client access is prompting for entring user name and password ..
          How to avoid this??? Is this some sort of firewall setting ???
          Thanks,
          Giri

          Comment


          • #6
            Re: automated download of PF to Excel file with Client Access

            You avoid it by using RXFERPCB instead of RTOPCB. The RXFERPCB command allows you to specify userid and password as parms.

            If you don't have it (wonder why?) try installing CA and specify file transfer.

            Comment


            • #7
              Re: automated download of PF to Excel file with Client Access

              I think when security is high concern, RXFERPCB will not be instaled.
              It has to do something with the CA installation and we are suing client system.
              Thanks,
              Giri

              Comment


              • #8
                Re: automated download of PF to Excel file with Client Access

                OK... here's ONE way ... you decide if it's for you or not...

                Create a txt file like so... \UserID.TXT

                PHP Code:
                ..
                Server    :MyServer
                UserID    
                :MyUserID
                Password  
                :MyPassword 
                Then, throw this into your DOS Path somewhere (Modify as needed for USERID.TXT location)

                PHP Code:
                @Echo Off

                REM 
                *----------------------------------------------*
                REM  08/21/06 -- CPE
                REM 
                REM  Get the UserID 
                and Password from a Hidden Text
                REM   file in the root folder called USERID
                .TXT
                REM   
                REM  This allows 
                for use of the job scripts without 
                REM   typing the USerID
                /Password inside the Scripts
                REM 
                REM 
                *----------------------------------------------*

                REM **
                REM ** Do we got a UserID and Password?
                REM **
                If 
                NOT Exist \UserID.TXT Goto GotNoFile

                REM 
                **
                REM ** Get UserID
                REM 
                **
                For /
                %%V IN (SERVER USERID PASSWORD) Do (

                    
                Find /"%%V" \UserID.txt tmp.A

                        
                Echo "%%V"

                        
                For /"tokens=1-3 delims=:" %%a in (tmp.A) do (

                           If /
                %%V EQU SERVER (
                             
                Set xSERVER=%%b
                           
                )
                           If /
                %%V EQU USERID (
                             
                Set xUSERID=%%b
                           
                )
                           If /
                %%V EQU PASSWORD (
                             
                Set xPASSWORD=%%b
                           
                )
                       )

                    )
                )


                Erase tmp.A  nul
                Goto Exit

                :
                GotNoFile

                Echo;
                Echo *******************************
                Echo *** 
                UserID/Password Missing *** 
                Echo *******************************
                Echo;

                Exit /
                B 99 
                Then, create yourself some sort of script like the following (adjusting to your needs)

                PHP Code:
                @Echo Off

                Call GetDate
                     Set xHistLog
                =\Logs\History-%dYYMMDD%.log

                Call GetUserID

                REM  
                REM Log Onto Server
                REM
                    
                "C:\Program Files\IBM\Client Access\"cwblogon.exe %xSERVER% /U %xUSERID% /P %xPASSWORD%

                REM
                REM  Perform download of Header Records with client access batch download 
                REM    program RXFERPCB.EXE
                Echo; 
                Echo @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> %xHistLog%
                Echo %Date% - %Time%                                                       >> %xHistLog%
                Echo UserID:  %UserName%                                                   >> %xHistLog%
                Echo Begin Header File .....                                               >> %xHistLog%
                Echo @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> %xHistLog%
                Echo;                                                                      >> %xHistLog%

                REM Delete file before download. 
                REM   (In case server application is still holding file, 
                REM    your @#%@#%@#% is covered by log file)

                     Delete F598019.xls >> %xHistLog%
                     
                "
                C:\Program Files\IBM\Client Access\"RXFERPCB.EXE F598019.dtf %xUSERID% %xPASSWORD% >> %xHistLog%

                REM Perform Download of Header records with client access batch 
                REM     download program RXFERPCB.EXE

                    Delete F598119.xls                                                     >> %xHistLog%

                Echo; 
                Echo @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> %xHistLog%
                Echo %Date% - %Time%                                                       >> %xHistLog%
                Echo UserID:  %UserName%                                                   >> %xHistLog%
                Echo Begin Detail File .....                                               >> %xHistLog%
                Echo @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> %xHistLog%
                Echo;                                                                      >> %xHistLog%

                      "
                C:\Program Files\IBM\Client Access\"RXFERPCB.EXE F598119.dtf %xUSERID% %xPASSWORD%   >> %xHistLog%

                Echo @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> %xHistLog%
                Echo %Date% - %Time%                                                       >> %xHistLog%
                Echo;                                                                      >> %xHistLog%
                Echo                   **** END of PROCESSING ****                         >> %xHistLog%
                Echo @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ >> %xHistLog% 
                Try it .. you might like it!

                The nice part about this is that if you put the GETUSERID.bat script in your \Windows (\WinNT) folder, you can call this from ANY script you setup...and only have to modify/change your userID/password in ONE spot for all scripts.

                ....Or not...

                -Rick


                PS: If you're interested, I have scripts for:

                GETDATE: This assigns values for DAY, MONTH, YEAR and YYMMDD values
                SetIPVARS: This breaks down the IP address of the device and assigns each octet and some other stuff for use in
                Scripts... Ie... Mapping, Pinging, etc...

                Just let me know
                Last edited by FaStOnE; December 15, 2006, 03:43 PM.

                Comment


                • #9
                  Re: automated download of PF to Excel file with Client Access

                  Good solution FastOne!! I would not have thought that one up!



                  Another quick (and somewhat related) DOS question....

                  Does anyone have a syntax to rename or copy a file, appending system %date% & %time%. This would be included in a .bat file such as the above.

                  eg: from myfile.txt to myfile_20061221_1130.txt

                  Comment


                  • #10
                    Re: automated download of PF to Excel file with Client Access

                    Here's a snippet that might give you some direction ...

                    PHP Code:
                    Echo;    >> %ErrLog%
                    Echo -- 
                    Prefix Processing --  >> %ErrLog%
                    For /
                    %%A IN (..\PollPrefixs) Do (

                       If 
                    Exist %%A* (

                          Echo    *
                    Found: %%A
                          
                    Echo    *Found: %%A    >> %ErrLog%

                          If /
                    %%A NEQ PODDAT (

                             If /
                    %%A NEQ PODTIF (

                                %
                    ZipPrg% -aq %%A.%ejSTORE%.%ejDATE%.zip %%A*.%ejSTORE%.%ejDATE%
                                
                    Move /%%A*.%ejSTORE%.%ejDATEArchive

                             
                    )

                          )

                       )


                    In a nutshell. I have a script that generates the ejSTORE and ejDATE Session Variables. That way I can manipulate them as needed later (like above).

                    Does this help?

                    -Rick
                    Last edited by FaStOnE; December 21, 2006, 03:57 PM.

                    Comment


                    • #11
                      Re: automated download of PF to Excel file with Client Access

                      Look at the ND utility on this page. http://home.att.net/~short.stop/freesoft/filutil1.htm
                      It might be what you're looking for

                      Comment


                      • #12
                        Re: automated download of PF to Excel file with Client Access

                        Speaking of GETDATE ... here's the script if you want to try it.

                        This particular version I modified to create an ARCHIVE date (Current date minus 30 days). This works with file names that have the mmmmyydd format somewhere in it so that you can go back and delete based on FILE NAME .. not creation date.

                        PHP Code:
                        @Echo Off

                        ::*********************************************************************************
                        ::
                        ::  
                        Script:       GetDate.bat
                        ::  Description:  Break out the date into Environment Vars
                        ::
                        ::  
                        Written By:   Rick
                        ::  Date Written01/2006
                        ::
                        ::  
                        Called By:    Whomever needs it
                        ::
                        ::  
                        Parameters1=(blank) -- Generates the values as of Today 
                        ::              1=ARCH -- Generates the values as Today minus 30 days
                        ::
                        ::*********************************************************************************

                        ::
                        :: 
                        Get the DATE details
                        ::
                        Set dMM=%Date:~4,2%
                        set dDD=%Date:~7,2%
                        Set dYY=%Date:~10,4%

                        If /
                        {%1EQU {ARCH} (

                           If /
                        %dMMEQU 01 (

                              
                        Set dMM=12
                              Set 
                        /"dYY=%dYY%-1"

                           
                        ) Else (

                             If /
                        %dMMequ 02 Set dMM=01)
                             If /
                        %dMMequ 03 Set dMM=02)
                             If /
                        %dMMequ 04 Set dMM=03)
                             If /
                        %dMMequ 05 Set dMM=04)
                             If /
                        %dMMequ 06 Set dMM=05)
                             If /
                        %dMMequ 07 Set dMM=06)
                             If /
                        %dMMequ 08 Set dMM=07)
                             If /
                        %dMMequ 09 Set dMM=08)
                             If /
                        %dMMequ 10 Set dMM=09)
                             If /
                        %dMMequ 11 Set dMM=10)
                             If /
                        %dMMequ 12 Set dMM=11)

                          )

                        )

                        Set dYYMMDD=%dYY%%dMM%%dDD%

                        rem Echo Month-%dMM%
                        rem Echo Day  -%dDD%
                        rem Echo Year -%dYY%
                        rem Echo;
                        rem Echo Date -%dYYMMDD%

                        :: 
                        :: 
                        Get the TIME details
                        ::
                        Set tHH=%Time:~0,2%
                        Set tMM=%Time:~3,2%
                        Set tSS=%Time:~6,2%

                        ::
                        :: 
                        Leading Zeros anyone??
                        ::
                        If /
                        %tHHLSS 10 (
                           
                        Set tHH=0%tHH:~1,1%
                        )
                        If /
                        %tMMLSS 10 (
                           
                        Set tMM=0%tMM:~1,1%
                        )
                        If /
                        %tSSLSS 10 (
                           
                        Set tSS=0%tSS:~1,1%
                        )
                        Set tHHMMSS=%tHH%%tMM%%tSS%

                        ::
                        :: 
                        Just in case you want to SEE the results... UNComment the next 2 lines
                        ::
                        rem ECHO Today is -- %Date%
                        rem ECHO Today is -- Year: [%dYY%]  Month: [%dMM%]     Day: [%dDD%] -OR- [%dYYMMDD%]
                        rem ECHO  Time is -- Hour: [%tHH%] Minute: [%tMM%] Seconds: [%tSS%] -OR- [%tHHMMSS%]

                        Set xMM
                        DISCLAIMER: Depending on version of DOS used (Windows XP, WinNT, Win2000) the "Set dMM=%Date:~4,2%" etc might have to be adjusted. Some version put the 3 byte Month first, some put just the xx/xx/xxxx etc in it. If you remove the "REM" statements, you can validate the values you're getting and "adjust" them as needed.

                        As always .. if you have any questions, please let me know...

                        -Rick
                        Last edited by FaStOnE; December 21, 2006, 03:53 PM.

                        Comment

                        Working...
                        X