CPYFRMIMPF And Fixed Data

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:

1. Fixed-length character fields may contain any character without the nuisance of special treatment. In CSV files, character fields require special handling of the characters used as field separators and character-field delimiters.
2. It is easier for some programs to generate files of fixed-length values than it is for them to generate CSV files.

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.

1. A database column (field) name.
2. The beginning position of the data in the import file.
3. The ending position of the data in the import file.
4. The position of the null indicator in the import file.

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.

• Leading blanks are ignored on each line of the FDF.
• Blank lines are ignored.
• You may include comment lines by starting them with a double hyphen. Comments have to go on lines by themselves. That is, you can’t add comments to the end of a line that defines a field.
• The column (database field) name must be the short name (system name)–not the alias–and you must key it in capital letters.
• You may add a line containing only the word *END after the list of fields. However, you don’t have to.
• Since the system ignores any lines that follow the *END line, you have another place to add comments.
• You don’t have to include all the fields in the database file in an FDF. CPYFRMIMPF will load the fields you tell it to and fill the fields you don’t mention with default or null values as appropriate.
• The order of fields in the FDF does not matter. You don’t have to list fields in order by position in the database table or position in the import file.
• If you use the *COL special value to indicate that the import is to load the fields in order, you must code *COL in capital letters.
• The only acceptable values for null indicators are capital Y and capital N. CPYFRMIMPF will not complain if you put some other value there, but it will treat anything other than Y and N (including lowercase “y”) as a capital N.
• A numeric value may include a leading minus and a decimal point. Thousands separators are not permitted.
• Don’t code too many zeros before the decimal point. For example, trying to load -000012 into a database column that allows only five digits before the decimal point will make the copy cancel, even though the significant digits don’t exceed the maximum value. Go figure.
• If a null indicator is Y and data is provided for the column, the data is ignored.
• If a null indicator is N and the data is blank, the system fills the column with the default value.
• If you specify a Errors Allowed (ERRLVL) value of n, then n + 1 bad lines will stop the copy, but the good rows before the nth bad line will be copied. Confused? It just means that you may get a partial copy before the command cancels.

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.

RELATED STORY

What’s New With CPYFRMIMPF And CPYTOIMPF?

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot