The Joys of CPYFRMIMPF for FLAT and CSV Files

Using CPYFRMIMPF to use flat text files in the IFS

The Copy from Import File (CPYFRMIMPF) command can be used to copy data from any IFS file into an IBM i (DB2) Table. It handles very simple delimited file conversions like CSV files and also straight flat text files. The Copy to Import File (CPYTOIMPF) does the exact same thing in reverse.

For those of you that are new to IBM i Systems and/or programming in general — let’s cover the basics:

What is a Flat File?

A flat file, or fixed-format file, is the definition of data that is stored and accessed sequentially in a database table or IFS file. All the data is stored in one long string of information. For RPG Programmers, this is like a program-described physical file. The fields occupy assigned positions within each line of input, and there is no external description.

Flat files rely on the program that reads the data, to know where each data element (field/column) starts and ends.

Ingredient       Quantity   Unit    Category   Notes
Flour            500        grams   Baking     All-purpose
Sugar            200        grams   Baking     White granulated
Eggs             3          units   Dairy      Large
Butter           100        grams   Dairy      Unsalted
Olive Oil        2          tbsp    Pantry     Extra virgin
Garlic           4          cloves  Produce    Minced
Tomatoes         3          units   Produce    Ripe
Chicken Breast   2          units   Meat       Boneless skinless
Salt             1          tsp     Pantry     Sea salt
Black Pepper     0.5        tsp     Pantry     Freshly ground

But what if the data does not have padded blank spaces so we don’t know where one value ends and another starts?

IngredientQuantityUnitCategoryNotes
Flour500gramsBakingAll-purpose
Sugar200gramsBakingWhitegranulated
Eggs3unitsDairyLarge
Butter100gramsDairyUnsalted
OliveOil2tbspPantryExtravirgin
Garlic4clovesProduceMinced
Tomatoes3unitsProduceRipe
ChickenBreast2unitsMeatBonelessskinless
Salt1tspPantrySeasalt
BlackPepper0.5tspPantryFreshlyground

This is where comma separated files, known as a CSV, have become an industry standard.

What is a CSV file?

Comma Separated Value (CSV) shows the same information with each field (aka sql column) separated from the next by a comma. Clear delineation of where each data element (field/column) starts and ends.

Ingredient,Quantity,Unit,Category,Notes
Flour,500,grams,Baking,All‑purpose
Sugar,200,grams,Baking,White granulated
Eggs,3,units,Dairy,Large
Butter,100,grams,Dairy,Unsalted
Olive Oil,2,tbsp,Pantry,Extra virgin
Garlic,4,cloves,Produce,Minced
Tomatoes,3,units,Produce,Ripe
Chicken Breast,2,units,Meat,Boneless skinless
Salt,1,tsp,Pantry,Sea salt
Black Pepper,0.5,tsp,Pantry,Freshly ground

The beauty of CSV files is they are clear to understand and open in spreadsheet applications like Excel:

I know this is very obvious process for us IBM-i programmers.

But what if you are new the IBM-i system?

Let’s talk through the absolute basics, to help you IBM-i newbies

First, take a slurp of delicious Early Gray, look out of the window, pet the dog, take a breath and let’s get back to basics. We will need to do a few things:

• Create an IBM-i file to hold the imported data
• Ask is this a one-time or a repeatable process?
• Assuming it might happen again – lets write a simple CL program to do it, so we can do it again in the future.

The DDS might look like this:

CSV File:

      A          R INGREC
      A            DATA           140A

Flat File:

      A          R INGREC
      A            INGREDIENT     40A
      A            QUANTITY       10A
      A            UNIT           10A
      A            CATEGORY       20A
      A            NOTES          60A
      A          K INGREDIENT

Or if you prefer a more flexible (varying length) SQL Table version of the same:

FLAT and CSV File in one statement:

set schema 'NICKLITTEN';

create table FLATFILE (DATA varchar(140));

create table CSVFILE (
      INGREDIENT varchar(40),
      QUANTITY varchar(10),
      UNIT varchar(10),
      CATEGORY varchar(20),
      NOTES varchar(60)
    );

Next up, let’s use ACS to simple upload both files from our PC into our IBM-i System IFS. In this case I am uploading to an IFS Folder called ‘/home/nicklitten’

Now we have both those files in the IFS we can use the CPYFRMIMPF command to copy the IFS data straight into our fat files. This might look something like this:

CPYFRMIMPF FROMSTMF('/home/nicklitten/ingredients.txt')  
           TOFILE(NICKLITTEN/FLATFILE)                   
           MBROPT(*REPLACE)                              
           RCDDLM(*CRLF)                                 
           DTAFMT(*DLM)                                  
           STRDLM(*NONE) 

and for the CSV File like this:

CPYFRMIMPF FROMSTMF('/home/nicklitten/ingredients.csv')    
           TOFILE(NICKLITTEN/CSVFILE)                      
           MBROPT(*REPLACE)                                
           RCDDLM(*CRLF)                                   
           DTAFMT(*DLM)

NOTE: If you want to exclude the row headers use this additional parm:

RMVCOLNAM(*YES)

IBM-i will look at the file data, figure out the delimiter is a comma, and import the data to the file. IBM-i data now looks like this:

Easy right?

If you want to see a control language program doing the same thing, so its repeatable, check out this lesson or enjoy watching me burble away, while very hungover:

That’s it! Easy Peasy Lemon Squeezy.