Little-known feature of the CPYFRMIMPF command

Tim Granatir

When you're importing delimited data to the iSeries, such as a comma-delimited spreadsheet or data from a Unix...

or Windows NT system, the CPYFRMIMPF command is a huge timesaver. But even with this command, people still complain about null data, errors or formatting problems. Many are not aware that the CPYFRMIMPF command is also capable of helping with some of those types of problems. People overlook this capability because they don't know the functionality is there, and that's probably because of how the command does prompting. This tip highlights of few of the problem areas this command can help you with.

The problem comes in right here on this example command:

Copy From Import File (CPYFRMIMPF)                       
                                                                                
 Type choices, press Enter.                                                     
                                                                                
 From stream file . . . . . . . .                                               
                                                                                
 From file:                                                                     
   File . . . . . . . . . . . . .   Test1         Name                          
     Library  . . . . . . . . . .     search400   Name, *LIBL, *CURLIB          
   Member . . . . . . . . . . . .   *FIRST        Name, *FIRST                  
 To data base file:                                                             
   File . . . . . . . . . . . . .   test2         Name                          
     Library  . . . . . . . . . .     search400   Name, *LIBL, *CURLIB          
   Member . . . . . . . . . . . .   *FIRST        Name, *FIRST                  
 Replace or add records . . . . .   *ADD          *ADD, *REPLACE, *UPDADD       
 Record delimiter . . . . . . . .   *EOR          Character value, *ALL...      
 Record format of import file . .   *DLM          *DLM, *FIXED                  
 String delimiter . . . . . . . .   '"'           Character value, *NONE        
 Field delimiter  . . . . . . . .   ','           Character value, *TAB         
 Decimal point  . . . . . . . . .   *PERIOD       *PERIOD, *COMMA               
                                                                        More... 
 F3=Exit   F4=Prompt   F5=Refresh   F12=Cancel   F13=How to use this display

You'll notice the More… at the bottom of this command. To get to this point of the command, you have had to hit the enter key 3 consecutive times. If you hit enter at this point, the CPYFRMIMPF will proceed to attempt to copy with the rest of the command defaults. I've talked to several people who have used this command extensively and have never noticed that a second screen was available. It's just too easy at this point to hit the enter key instead of the roll key. If you hit your roll key, and then hit your enter key a couple times, you'll end up with a screen that looks like this:

Copy From Import File (CPYFRMIMPF)                      
                                                                              
Type choices, press Enter.                                                    
                                                                              
Decimal point  . . . . . . . . .   *PERIOD       *PERIOD, *COMMA              
Date format  . . . . . . . . . .   *ISO          *ISO, *USA, *EUR, *JIS...    
Time format  . . . . . . . . . .   *ISO          *ISO, *USA, *EUR, *JIS, *HMS 
Copy from record number:                                                      
  Copy from record number  . . .   *FIRST        Number, *FIRST               
  Number of records to copy  . .   *END          Number, *END                 
Errors allowed . . . . . . . . .   *NOMAX        Number, *NOMAX               
Error record file:                                                            
  File . . . . . . . . . . . . .   *NONE         Name, *NONE                  
    Library  . . . . . . . . . .                 Name, *LIBL, *CURLIB         
  Member . . . . . . . . . . . .                 Name, *FIRST                 
Replace or add records . . . . .   *ADD          *ADD, *REPLACE               
Replace null values  . . . . . .   *NO           *NO, *FLDDFT       

You'll notice Replace null values prompt at the bottom of the screen. If you change this to *FLDDFT, when it encounters a null field, it will replace the values in your TOFILE with something acceptable to the 400 (blanks for alphanumeric fields and zeros for numeric fields). If you've ever tried to import a comma delimited spreadsheet, it seems like invariably a null field shows up somewhere in your imported data. Although the iSeries can be made to accept and process null fields by default, it doesn't expect them or handle them very well.

You'll also notice the date and time format fields. If your TOFILE has fields setup as date and time fields, you can tell this command the format of those fields and it will do a validity check to ensure that you are importing valid dates and times.

Another thing you'll notice is the error record file parameter. If you specify a file name in these fields, any error on your import will be copied to this file. If a file were to be specified with the defaults as shown in this example, the CPYFRMIMPF will import all the records it can but copy off the errors to a separate file for later analysis.

Hopefully this helps ease some of the aggravation when importing delimited data from other systems.

---------------------------
About the author: Tim is vice president of Technical Services at Interlink Technologies in Maumee, Ohio, where he serves as chief architect for their warehouse management system. He has worked in the banking, insurance, healthcare and distribution industries in various positions, including programmer/analyst, systems analyst and DP manager. Tim has worked on IBM midrange platforms since 1983.

==================================
MORE INFORMATION
==================================

• There's a problem with the CPYFRMIMPF command
  A user needed help getting some records in a CSV file written to an error file. Nothing he did seemed to work. Fortunately, site expert Tim Granatir had an idea.
• .ZYocaTpWzdc.0@.ee84636/1700>Transferring data from an Excel spreadsheet to the iSeries
  "Widge" asks, "What's the best method for transferring an Excel spreadsheet to a PF on the 400. I have tried to download an existing file from the 400 and editing this in the spreadsheet, but when I try and send it back to the 400 the error message states the file must be an .fdf file." Several other users offered their preferred methods, including using the CPYFRMIMPF command, Client Access and a couple other products.
• Transferring spreadsheet data with blank cells to DB2
  If you have to import a spreadsheet to your iSeries that is in .CSV format, you can use CPYFRMIMPF. However, this command will not work if any of the fields in the .CSV file are blank or zero. To get around this limitation, Search400.com member Francis Lapeyre shows you how to get around this limitation in this Hall of Fame tip.