Little-known feature of the CPYFRMIMPF command
Learn how to better use CPYFRMIMPF to ease the aggravation when importing delimited data from other systems.
 |
|
 |
|
| 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.
