CPYFRMIMPF And Fixed Data
September 25, 2013 Ted Holt
It seems that every time I see Copy from Import File (CPYFRMIMPF) mentioned in a Web forum, the question concerns CSV files. CPYFRMIMPF also handles files of fixed-length fields. Such files have certain advantages over CSV files, and there’s more to using them than the IBM documentation tells you.
Don’t get me wrong. CSV files are great. I use them often. But consider that fixed-length data has its advantages. Two that come to mind are:
In other words, whether you should use delimited data or fixed-length data depends on each situation.
With that said, here’s how to import fixed-length data into database tables (physical files).
First, visit the IBM i Information Center. It provides good documentation for CPYFRMIMPF. I won’t repeat it all here. Instead, I’d like to add a little information that that site does not tell you and give you an example that you can work from.
A fixed-format import file is like a program-described physical file. The fields occupy assigned positions within each line of input, and there is no external description. To tell the system which fields occupy which positions requires a field definition file (FDF).
Each row of a field definition file FDF consists of four values.
This format reminds me slightly of RPG Input specifications, which I have rarely used since 1988 when I left a S/36 shop for a S/38 shop. However, the field definition entries are free-format. Just separate the four values with white space.
Here are more things that you should know about FDFs.
It’s time for an example. First, here’s a database table (physical file) in which to load the data.
create table offlineord ( CustomerNumber for column CustNbr dec (5,0), CustomerName for column CustName char (25), ItemNumber char (6), QuantityOrdered for column OrderQty dec (5,0), DockDateFrom for column DockDtFrom date, DockDateTo for column DockDtTo date, Comments varchar(50) )
Next, a file to contain the data to import. This can be any of several types. In working up this example, I used a file in the root system of the IFS. (The scale in the first line is not part of the stream file. I include it here for your benefit only.)
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+ 22222Rufus T. Firefly AB8101 122013-10-01N YTop priority 33333Quincy Adams Wagstaff DK7130 10 Y2013-10-31N 44444Otis B. Driftwood EW6414 1 Y YRush! 55555Hugo Z. Hackenbush TL8989 122013-10-14N2013-11-04N
Next, a field definition file, which I saved in member OFFLINEORD of source physical file MYLIB/FDFSRC.
-- Sales orders offline load file CUSTNBR 1 5 0 CUSTNAME 6 30 0 ITEMNUMBER 31 36 0 ORDERQTY 37 41 0 DOCKDTFROM 42 51 52 DOCKDTTO 53 62 63 COMMENTS 64 99 0
And last, the CPYFRMIMPF command.
CPYFRMIMPF FROMSTMF('offline-orders.txt') TOFILE(MYLIB/OFFLINEORD) RCDDLM(*ALL) DTAFMT(*FIXED) RMVBLANK(*NONE) FLDDFNFILE(MYLIB/FDFSRC OFFLINEORD)
CPYFRMIMPF is a great tool that saves us immense amount of time by handling the sordid details of copying data to a database table. As with any tool, the better you understand it, the more work you will accomplish with it.