brainchime.com Latest Questions

Admin
Asked: 2024-10-10T11:56:49+05:30 In:

How to create csv file in as400?

I have a physical file present in my development library on the IBM I box and I want to create a CSV file out of it and place it in IFS so that from there I can FTP it to the desktop in the CSV format. How do I create a comma-separated values (CSV) file in IFS?

as400ifs

Related Questions

Leave an answer

Leave an answer

Browse
Browse

Choose from here the video type.

Put Video ID here: https://www.youtube.com/watch?v=sdUUx5FdySs Ex: "sdUUx5FdySs".

1 Answer

  1. Admin

    To create a CSV file in as400, we can use the command CPYTOIMPF. But, before creating a CSV file, we should at least know what a CSV file is. CSV file is a comma-separated values stream file. CSV files hold data from IBM i files, such as SQL tables or DDS physical files, which are separated by commas. CSV file data:

    Name,Email,Id,Age,Address,PhoneNumber

    CSV files are mostly used when we transfer data between two different systems. CSV files are supported by several spreadsheet programs, such as Microsoft Excel and Google spreadsheets. Data on IBM i support EBCDIC format and CSV files in an integrated file system (IFS) must contain ASCII data before exporting it to a Windows computer. Let’s see how to create a CSV file in as400 ifs using the CPYTOIMPF command:

    First, create a physical file or SQL table.
    Fill some records into the file or SQL table.

    IDN NAME ADDR1 ADDR2 
4 NAME4 ADDRESS4 "PART2 
4 NAME4 ADDRESS4 "PART2 
4 NAME4 ADDRESS4 "PART2

    Run command CPYTOIMPF from the command line to copy data from the file and stream the file in ifs.

    CPYTOIMPF FROMFILE(TESTLIB/FILE1)
TOSTMF('/home/developer/file1.csv')
MBROPT(*REPLACE) FROMCCSID(*FILE)
STMFCCSID(*PCASCII) RCDDLM(*CRLF)
     Copy To Import File (CPYTOIMPF) 

Type choices, press Enter. 

From file: FROMFILE 
File . . . . . . . . . . . . . > FILE1 
Library . . . . . . . . . . > TESTLIB
Member . . . . . . . . . . . . *FIRST 
To data base file: TOFILE 
File . . . . . . . . . . . . . 
Library . . . . . . . . . . *LIBL 
Member . . . . . . . . . . . . *FIRST 
To stream file . . . . . . . . . TOSTMF > '/HOME/DEVELOPER/FILE1.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 creates a stream file in the IFS home directory “/home/developer/file11.CSV” using the data from the physical file FILE1 in the library TESTLIB copy.

    Work with Links using the command WRKLNK to browse CSV files created in ifs.

                                 Work with Object Links                            
                                                                               
 Directory  . . . . :   /home/DEVELOPER
                                                                               
 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                                             
       file1.csv              STMF                                            
       txtfile1.txt           STMF                                             
       txtfile2.txt           STMF                                             
       txtfile3.csv           STMF

    Take option 5 to display CSV file data.

    Browse : /home/DEVELOPER/FILE1.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 ,"PART"
24,NAME4 ,ADDRESS4 ,"PART"
24 ,NAME4 ,ADDRESS4 ,"PART" 
************End of Data********************

    As you can see, there is a comma between each field. We see blank spaces between separated column values according to each column field size since the Remove Blank parameter on command CPYTOIMPF was set to *NONE.