You may find yourself in a situation where you want to run a Query on a file, save the
information to a database,and then convert that information to a Comma Separated Variable (CSV)
file. This is especially true when you need the data to be used in a "spread sheet" format
like Excel.
The simplest way to do this is to copy the information from the data file to your newly created
CSV file.
Enter this command:
CPYTOIMPF FROMFILE(*LIBL/DTAFIL) TOFILE(USER999/CSVPC)
Hit Enter, and then again 3 times and you have now created a Comma Separated Variable (CSV) File.
Remember that you need a 'destination file' before you can use this command. To create a
'destination file', you will need to use the Create Physical File command. To create a file
named CSVPC that is 200 characters in length, type in the command:
CRTPF FILE(USER999/CSVPC)
Hit F4 to prompt the command, and type in the record length you need, for Member,
be sure to name it CSV.
Create Physical File (CRTPF)
Type choices, press Enter.
File . . . . . . . . . . . . . . > CSVPC Name
Library . . . . . . . . . . . > USER999 Name, *CURLIB
Source file . . . . . . . . . . QDDSSRC Name
Library . . . . . . . . . . . *LIBL Name, *LIBL, *CURLIB
Source member . . . . . . . . . *FILE Name, *FILE
Record length, if no DDS . . . . > 200 Number
Generation severity level . . . 20 0-30
Flagging severity level . . . . 0 0-30
File type . . . . . . . . . . . *DATA *DATA, *SRC
Member, if desired . . . . . . . > CSV Name, *FILE, *NONE
Text 'description' . . . . . . . *SRCMBRTXT
More...
F3=Exit F4=Prompt F5=Refresh F10=Additional parameters F12=Cancel
F13=How to use this display F24=More keys
Then hit F10 for Additional Parameters, and type in *NOMAX in the
"Initial number of records" field.
Create Physical File (CRTPF)
Type choices, press Enter.
Additional Parameters
Source listing options . . . . . *SRC, *NOSRC, *SOURCE...
+ for more values
System . . . . . . . . . . . . . *LCL *LCL, *RMT, *FILETYPE
Expiration date for member . . . *NONE Date, *NONE
Maximum members . . . . . . . . 1 Number, *NOMAX
Access path size . . . . . . . . *MAX1TB *MAX1TB, *MAX4GB
Access path maintenance . . . . *IMMED *IMMED, *DLY, *REBLD
Access path recovery . . . . . . *NO, *AFTIPL, *IPL
Force keyed access path . . . . *NO *NO, *YES
Member size:
Initial number of records . . > *NOMAX 1-2147483646, *NOMAX
Increment number of records . Number
Maximum increments . . . . . . Number
More...
F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display
F24=More keys
Now you have created a 'destination file' and can convert an AS/400 database to a CSV file.
To view the file you have created, simply type in this command:
DSPPFM USER999/CSVPC
Display Physical File Member
File . . . . . . : CSVPC Library . . . . : USER999
Member . . . . . : CSV Record . . . . . : 1
Control . . . . . __________ Column . . . . . : 1
Find . . . . . . . ________________________________
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+...
"R","04","LEC OFF-NET ",
"C","05","NON-NUMERIC DATA IN NUMERIC ",
"R","06","INVALID CREDIT LEC ",
"R","08","LEC MASTER FILE ERROR ",
"C","09","LEC NOT IN TPM FILE OR LEC MST",
"C","20","INVALID RECORD ID ",
"C","21","ORIGINATING NPA INVALID PER BC",
"C","22","ORIGINATING NPA/NXX NOT ON TPM",
"C","23","ORIG NUMBER = TERMINATING NUM ",
"R","24","SENT PAID CALL/ORIG OCN <> BIL",
"L","32","ORIGINATING NXX INVALID ",
"R","33","ORIGINATING NPA INVALID ",
"R","34","ORIGINATING NUMBER LENTH INVAL",
"L","35","ORIGINATING NUMBER INVALID ",
"R","36","ORIGINATING STATE INVALID ",
More...
F3=Exit F12=Cancel F19=Left F20=Right F24=More keys