 |
| 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 ********
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.