ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Cpytoimpf

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

  • Cpytoimpf

    I have explored cpytoimpf which you have shared with me from your last posts and a friend of mine have taught me of downloading a physical or logical file using CSV types.

    My objective is to download the components (fields and field attributes) in an excel type of file so that I can open it using MS Excel.

    Code:
    CPYTOIMPF FROMFILE( library/filename )
              TOSTMF( 'directoryname/filename.csv' )
              MBROPT( *ADD ) 
              RCDDLM( *CRLF )
              DTAFMT( *DLM )
              STRDLM( '"' )
              FLDDLM( ',' )
    I used the above command and used get filename.csv in the ftp command.

    Is there a way I could download a pf or an lf directly to an .XLS file (MS Excel file)? Is it mandatory that I always use " for string delimiters? What delimiter shall I use for fields to obtain an .XLS file? Thanks. You are a big help for me. I really appreciate your generosity.
    joan400

  • #2
    Downloading directly from excel

    Check this out
    Code:
    Windows to the iSeries Environment
    Uploading Excel data to an iSeries database file
    by Joyce Leeper and Herb Miller
    
    
    
    Have you ever wished you could upload a spreadsheet directly 
    from Microsoft* Excel to an iSeries database file? Client Access
    Express now provides an enhanced Excel data transfer add-in 
    that allows data to be transferred directly from an Excel 
    spreadsheet to an iSeries database file.
    
    
    Previous versions of Client Access (both Client Access Express 
    and Client Access for Windows* 95/NT*) provided an Excel data
     transfer add-in module. This module, CWBTFXLA.XLL, allowed
     direct download of data from an iSeries database file to an Excel
     spreadsheet. Now with V5R1, Client Access also provides a data
     transfer add-in that can perform direct uploads of spreadsheet 
    data to the iSeries.
    
    
    Requirements
    
    To use this add-in feature, Excel 97 (or higher) and Client Access 
    V5R1 with Service Pack SI01037 (or above) must be installed on 
    the PC.
    
    To confirm the Excel version, from the top of the Excel window, 
    select Help>About Microsoft Excel. The first line on the resulting 
    window displays the installed version. To confirm the Client 
    Access version and service level, go to the Windows toolbar and 
    select Start> Programs>IBM AS/400 Client Access Express>Client 
    Access Properties. Version and service level information are 
    displayed in the second line on the General tab.
    
    Configuring the Data Transfer Add-In
    
    Depending on the order in which Client Access Express and Excel 
    were installed on the PC, Excel may or may not be automatically 
    configured to use the data transfer add-in.
    
    
    If Excel has been automatically configured to use the add-in, a
    new toolbar containing only two icons will be displayed within 
    Excel. The download icon looks like the pages of an open book 
    and the upload icon looks like a small Windows screen displayed 
    on top of a sheet of paper. If the toolbar and icons donâ??t appear
    automatically, take these steps to configure the add-in:
    
    Click on the Excel Tools drop-down menu and select â??Add-Ins.â?? 
    Review the displayed list of add-ins. If â??Client Access Data 
    Transferâ?? appears on the list, check the box in front of this option
    and click â??OK.â?? 
    
    If â??Client Access Data Transferâ?? isnâ??t on the list of add-ins, select 
    the Browse button. This displays a popup window. Click the down
    arrow on the Look In setting and select â??C Driveâ?? (or â??C:â??). Select
    the following directories as they appear in the resulting 
    windows: â??Program Files,â?? â??IBM,â?? â??Client Accessâ?? and â??Shared.â?? 
    
    CWBTFXLA.XLL is the only file displayed in the Shared directory.
    Select CWBTFXLA.XLL and click â??OK.â?? â??Client Access Data 
    Transferâ?? will be added to the list of available add-ins. Make sure
    the box in front of â??Client Access Data Transferâ?? is checked and 
    click â??OKâ?? again. This creates a new Excel toolbar, which displays 
    the data transfer add-in icons.
    
    
    Transferring Data to the iSeries
    Here are the steps to use the add-in upload function.
    
    
    Optionalâ??The spreadsheet cells to be transferred may be 
    selected prior to selecting the data transfer add-in option. Select 
    the first spreadsheet cell to be transferred to the iSeries 
    database file. Press and hold the Shift key while selecting the last
     spreadsheet cell to be transferred. This selects all columns and 
    cells (from the first cell in the first row to the last cell in the last 
    row) that are to be transferred to the iSeries.
    
    
    Data to transfer sectionâ??Select the Transfer Data to AS/400 icon 
    from the Excel Data Transfer toolbar or select â??Transfer Data to 
    AS/400â?? from the Excel Data drop-down menu. If the range of 
    cells to be transferred was pre-selected before opening the data 
    transfer add-in GUI (see preceding paragraph), the Data to 
    Transfer section will be pre-filled with the starting and ending 
    column and row designators. If the range of cells to be 
    transferred wasnâ??t pre-selected, column and row designators can
     be manually entered at this point.
    
    If the spreadsheet to be transferred includes column headings, 
    check the box in front of â??First row is Column Heading(s).â?? Failing 
    to do so generates an error when Client Access attempts to 
    transfer the data.
    
    Transfer request sectionâ??If a transfer request (for this or an 
    identical spreadsheet) has already been saved from within the 
    Client Access Data Transfer to AS/400 GUI screen, select â??Create 
    from Fileâ?? (*.dtt, *.tfr, *.dt, *.rto). Then click the Browse button, 
    locate and select the saved transfer request and select â??Open.â?? 
    The file name of the data transfer request is returned to the add-
    in GUI screen. Click â??OKâ?? to run the saved transfer request.
    
    â??Create Newâ?? is the default setting for the Transfer Request 
    section, so if a new transfer request is to be created, click â??OK.â?? 
    This opens the Data Transfer to AS/400 wizard. Click â??Nextâ?? and 
    select applicable options. The wizard steps through several GUI 
    screens, which require the same information that would normally 
    be entered in the Client Access Data Transfer to AS/400 GUI.
    
    For More Information
    
    Directions on the Client Access Data Transfer to iSeries GUI 
    (including print screens) and details on setting up the Excel add-
    in are available from these Support Line knowledge base 
    documents:
    
    â??Excel Add-in Upload Functionâ?? (KB: 23102637) 
    â??Adding the Data Transfer Add-In to Microsoft Excelâ?? (KB: 17573616)
    Documents:


    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
      Thank you for the tip. I might use it in the future. It really is an interesing add-on. However, I do not have the authority to install a software on my unit right now.

      What I may need is the list of possible values for FLDDLM aside from commas (",") (where CSV "comma separated values" got its name.) I was told that there are hexadecimal values that I may use for FLDDLM so that .XLS file will be accepted. But I do not know where to find them and how to used them in CPYTOIMPF.

      Hope I am making it easier for you to understand. I am sorry if I cannot deliver my explanations/queries in a clearer way.

      Thanks.

      By the way, how do I know (in AS) the type of OS or machine I am connecting to? How do I know that it is a V5R2 or so?
      joan400

      Comment


      • #4
        how to find version

        to find your OS version you can look at the top of any compiled program.


        you can use command

        Code:
         GO LICPGM - Then OPtion 10

        or

        Code:
         DSPDTAARA QSS1MRI

        Now about the CPYTOIMPF ....I have no idea

        I searched pretty much all my resources and can't find a list.
        You will have to experiment....Looks to me like all characters are valid and only length of 1.
        Code:
        Command:
        CPYTOIMPF FROMFILE(EXAMPLE/TABLE1) TOSTMF('/example/streamto') RCDDLM(*CRLF)
        DTAFMT(*DLM) STRDLM('!') FLDDLM('|')
        Final stream file:
        ************Beginning of data**************
        !JOHN !|!A!|!DOE !|!ABC !|!555-0000!|52
        !SALLY !|!Z!|!SMITH !|!DEF !|!555-0000!|25
        !JANE !|!Q!|!PUBLIC !|!XYZ !|!555-0000!|32
        !FRED !|!R!|!SMITH !|!LMN !|!555-0000!|47
        ************End of Data********************
        It mentions something about special values but I have no clue where to find them.


        good luck
        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


        • #5
          You have a few options. But first, note that a CSV file will open directly into Excel, but without formatting options. Some of the other choices are:

          1. Write to Excel file directly from RPG

          2. Write to Excel file directly using Java

          3. Include a VB macro inside the Excel sheet that downloads the file when opened (or on some other event)

          4. Write a VB program that reads the PF file and writes to an Excel file.

          I've found using the CPYTOIMPF and writing a CSV the easiest, if I can live without the formatting. Option 3 is also easy.

          Comment


          • #6


            Jamief!

            Hi, licpgm and dtaara thing for version is really cool. Thanks!


            This to works
            Code:
            Call  QSZRTVPR  (                               +
                              &RcvVar                       +
                              x'00000100'                   +
                              'PRDR0100'                    +
                              '*OPSYS *CUR  0000*CODE     ' +
                              x'00000000'                   +
                            )
            
            ChgVar  &CurRls  %sst( &RcvVar 20 6 )
            By the way, (regarding windows thing) I was able to add the component Client Access Data Transfer in my unit (well, I used another unit). That's sooooo cool! I can hook up in a machine and download the source file directly and view it in Excel.

            With regards to CPYTOIMPF, I'll take your advice in experimenting the delimiters. You are a big help.


            arrow483

            I have yet to learn creating macros. I'm very interested. I am currently having "tutorials" from my friend. I agree with you in using CSV for now...Thank you so much.

            GOD BLESS US ALL!
            joan400

            Comment

            Working...
            X