Converting an AS/400 database file to a CSV file

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                   
                                                                               




Back to Basic AS/400 Tips    |    Back to Main Page    |   Contact Info