Generating CSV file in IFS using CPYTOIMPF command in IBM i(AS400)

Generating CSV file in IFS using CPYTOIMPF command in IBM i(AS400)
Generating CSV file in IFS using CPYTOIMPF command in IBM i, Comma Separated Values (CSV), CPYTOIMPF , database file member, save file, Input/output (I/O) operations, source member, stream file, Current Working Directory, Stream file,ibmi,as400,iseries,systemi, working with ifs,as400 and sql tricks, as400 tutorial, ibmi tutorial, working with ifs, working with integrated file system, CPYTOIMPF in as400, copy to import file in ibmi, copy to import file in as400, copy pf to source member using cpytoimpf in ibmi as400, copy PF to flat file using cpytoimpf in ibmi as400, copy pf to stream file in ifs using cpytoimpf in ibmi as400,Generating CSV file in IFS using CPYTOIMPF command in IBM i(AS400),Generating CSV file in IFS using CPYTOIMPF command in AS400,iseries,systemi, comma separated values files,csv file in as400, csv in ibmi,comma separated values file in ibmi, comma separated values file in as400,Copy to Import File,comma-delimited stream file, csv stream file,IFS directory ,off load data from the IBM i to the IFS,IFS stream file are separated by commas,
Generating CSV file in IFS using CPYTOIMPF command in IBM i

Introduction to CSV file in IFS

A Comma Separated Values (CSV) stream file contains columns data of the SQL Table or Physical file (PF) and are separated by commas. The data may look like...

Name,Email,Id,Age,Address,PhoneNumber

These files are often used for exchanging data between different systems.CSV files can be used with any spreadsheet program, such as Microsoft Excel or Google Spreadsheets.

The Data on the IBM is in EBCDIC and we want the data on Integrated File system (IFS) in ASCII.

How to generate CSV file in IFS using CPYTOIMPF command in IBM i

First, we'll use the Physical file we created here to generate the CSV file in IFS using CPYTOIMPF command.

Data contained in file 'PF20'

RUNQRY *N PF20
                                                          Display Report                                                            
                                                                                                  Report width . . . . . :     100  
 Position to line  . . . . .                                                                  Shift to column  . . . . . .          
 Line   ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+...10                        
        IDN   NAME                  ADDR1                 ADDR2                                                                     
 000001   4   NAME4                 ADDRESS4              "PART2                                                                    
 000002   4   NAME4                 ADDRESS4              "PART2                                                                    
 000003   4   NAME4                 ADDRESS4              "PART2                                                                    
 ****** ********  End of report  ********
  • Execute following CPYTOIMPF command to copy from physical file PF20 in library EASYCLASS1 to stream file
  •                          Copy To Import File (CPYTOIMPF)                       
                                                                                   
     Type choices, press Enter.                                                    
                                                                                   
     From file:                       FROMFILE                                     
       File . . . . . . . . . . . . .              > PF20                          
         Library  . . . . . . . . . .              >   EASYCLASS1                  
       Member . . . . . . . . . . . .                *FIRST                        
     To data base file:               TOFILE                                       
       File . . . . . . . . . . . . .                                              
         Library  . . . . . . . . . .                  *LIBL                       
       Member . . . . . . . . . . . .                *FIRST                        
     To stream file . . . . . . . . . TOSTMF       > '/HOME/EASYCLASS/TODAY.CSV'   
                                                                                   
     Replace or add records . . . . . MBROPT         *ADD                          
     From CCSID . . . . . . . . . . . FROMCCSID      *FILE                         
     Stream file CCSID  . . . . . . . STMFCCSID      *STMF                         
     Stream file authority  . . . . . STMFAUT        *DFT                          
     Record delimiter . . . . . . . . RCDDLM       > *LFCR                         
     Record format of import file . . DTAFMT         *DLM  
     String delimiter . . . . . . . . STRDLM       > *NONE                         
     Remove blanks  . . . . . . . . . RMVBLANK       *NONE                         
     Field delimiter  . . . . . . . . FLDDLM         ','                           
     Null field indicator . . . . . . NULLIND        *NO                           
     Decimal point  . . . . . . . . . DECPNT         *PERIOD                       
     Date format  . . . . . . . . . . DATFMT         *ISO                          
     Time format  . . . . . . . . . . TIMFMT         *ISO                          
     Order by . . . . . . . . . . . . ORDERBY        *NONE                         
                                                                                   
                                                                                   
                                                                                   
                                                                                   
                                                                                   
                                                                                   
      ...                                                                          
     Add column names . . . . . . . . ADDCOLNAM      *NONE                          
    

    This takes the data in physical file PF20 in library EASYCLASS1 copy to stream file in IFS home directory /HOME/EASYCLASS/TODAY.CSV.

     Selection or command                                                          
     ===>                                                                          
                                                                                   
     F3=Exit   F4=Prompt   F9=Retrieve   F12=Cancel   F13=Information Assistant    
     F23=Set initial menu                                                          
     All records copied from file PF20 in EASYCLASS1.                              
    

    Work with Links to browse IFS stream file.

    WRKLNK
                                 Work with Object Links                            
                                                                                   
     Directory  . . . . :   /home/EASYCLASS                                        
                                                                                   
     Type options, press Enter.                                                    
       2=Edit   3=Copy   4=Remove   5=Display   7=Rename   8=Display attributes    
       11=Change current directory ...                                             
                                                                                   
     Opt   Object link            Type             Attribute    Text               
           openfile2              STMF                                             
           openfile3              STMF                                             
           pf21                   STMF                                             
           testdir                DIR                                              
           testfile1              STMF                                             
           today.csv              STMF                                            
           txtfile1.txt           STMF                                             
           txtfile2.txt           STMF                                             
           txtfile3.csv           STMF                                             
                                                                            More...
    

    Display data in IFS stream file.

     Browse : /home/EASYCLASS/TODAY.CSV                                                        
     Record :       1   of       3 by  18                      Column :    1     97 by 131     
     Control :                                                                                 
                                                                                               
    ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9.
     ************Beginning of data**************                                               
    4   ,NAME4               ,ADDRESS4            ,"PART2                                      
    4   ,NAME4               ,ADDRESS4            ,"PART2                                      
    4   ,NAME4               ,ADDRESS4            ,"PART2                                      
     ************End of Data********************

    Now, you can see every field is separated by a comma. Remove blank parameter on command CPYTOIMPF was set as *NONE therefore, we observe blank spaces between separated columns values as per each column field size.

    Post a Comment

    © AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain