April 15

2 comments

Copy an IBM i File to a CSV comma separated file

By NickLitten

April 15, 2014

csv, file, IFS

Ever since the IBM i system was a young lad, its been able to happily talk to just about any other operating system out there – Windows, Linux, Apple, AIX and so many more. However, one of the things that has remained the same through the evolution of system (from AS400 years, thru iSeries years upto our current IBM i) is the layout of the file system on our machine. IBM i has its own database (previously known as DB2 or uDB) and it stores files in a simple format, breaking down each file into records (SQL:ROWS) and fields (SQL:COLUMNS).

Copying those files to other systems is super easy using CSV file conversion, courtesy of the IBM i command CPYTOIMPF (Copy to Import File)

Example of PHYSICAL FILE to CSV FILE

CSV File conversion starts with a native IBM i file that might look like this:

Physical file to csv file
FMT PF .....A..........T.Name++++++RLen++TDpB......Functions+++++++++++++++++++++++++++ *************** Beginning of data ********************************************** 0001.00 A R RCDFMT 0002.00 A FIRSTNAME 30A COLHDG('First Name') 0003.00 A SURNAME 30A COLHDG('Last Name') 0004.00 A DOB L COLHDG('Date of Birth') 0005.00 A SHOESIZE 3S00 COLHDG('Shoe Size') ****************** End of data *************************************************
Which when its compiles and I enter a sample line of data. it might look like this:
 Display Physical File Member 
File . . . . . . : IBMIFILE Library . . . . : QGPL 
Member . . . . . : IBMIFILE Record . . . . . : 1 
Control . . . . . Column . . . . . : 1 
Find . . . . . . . 
*...+....1....+....2....+....3....+....4....+....5....+....6....+....7... 
Nicholas Litten 1900-01-01014 
 ****** END OF DATA ****** 

As you can see its flat, unformatted, data. Boring huh?

We can look at the same data from the IBM i Command line using SQL and it looks like this:

Line ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+...
 First Name Last Name Date of Birth Shoe Size
000001 Nicholas Litten 1900-01-01 14 
****** ******** End of report ******** 

But, assuming we want to send this data to another computer, possibly using a completely different operating system and database then a simple (industry standard) is to use a CSV file which looks like this:

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0....+....1
"Nicholas ","Litten ","1900-01-01",14 
 ****** END OF DATA ****** 

or perhaps we want to include the field (SQL:Column) names like this:

*...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8....+....9....+....0
FIRSTNAME,SURNAME,DOB,SHOESIZE 
"Nicholas ","Litten ","1900-01-01",14 
 ****** END OF DATA ******

CSV FILE CREATION IS EASY WITH IBM i

We can copy our IBM i physical file into a CSV File in two simple ways

  1. as another physical file or
  2. as a IFS (Integrated File System) file — ie: just like a windows file.

The IFS is used to share native IBM i data across your windows network and have it look exactly like you are accessing a Windows Server. A file appears in your windows network and you copy it to your desktop top, open it with Excel, do anything you like with it… all the time not realizing that you are actually using a file directly on an IBM i Server

Copy to CSV formatted IBM i File

First we need to create the file that will hold the CSV data. Obviously as we are adding quotes and commas to the data, make allowances for the increase in record size and add at least 3 bytes for each field in the file

CRTPF FILE(MYLIB/CSVFILE) RCDLEN(500)

Just use a standard good old fashioned IBM i Command:

CPYTOIMPF FROMFILE(original-file) TOFILE(MYLIB/CSVFILE) DTAFMT(*DLM)

That creates the file in your library as CSV (Delimited)

You can obviously copy that to any other WIndows/Linux/whatever server on the network, or we could just create it straight as a CSV file on the IFS share (this means we dont need to create MYLIB/CSVFILE)

Copy to CSV Formatted IBM i IFS file

This basically says “copy this file into the IFS and create it as a CSV layout please”

CPYTOIMPF FROMFILE(original-file) TOSTMF('/some-ifs-folder/csvfile.csv') RCDDLM(*CRLF)

It doesnt get much easier than that!

Once the file is living on your IFS folder it can be access by users on the network, zipped up, emailed, uploaded to facebook or anything you like.

If you have code page problems then try specifying the code page using additional parameters. For example, we could say “copy the file and convert from it’s codepage to a standard PC ASCII format in the IFS” by typing something like this:

CPYTOIMPF FROMFILE(NLITTEN/QRPGLESRC) TOSTMF('/home/nlitten/csvfile.csv')
 MBROPT(*REPLACE) FROMCCSID(*FILE) STMFCCSID(*PCASCII) RCDDLM(*CRLF)

Copy to CSV Formatted file directly on any other networked Windows Server

This is my preferred technique and says “convert this file into CSV layout and place it directly on this server on the network”

CPYTOIMPF FROMFILE(original-file) TOSTMF('/QNTC/your-server-name/some-folder/csvfile.csv') RCDDLM(*CRLF)

Mounting a QNTC share to point at any windows share on your local network is straightforward using the MOUNT command. But that’s a story for another blog….

  • Nick, Love your blog and bio. You straightened me out a couple months ago pointing out that
    copying a file from the iSeries into the IFS was case sensitive. I’ve been doing a lot of that
    lately, successfully, so thanx! But now I’m at my wit’s end! I need to copy into IFS (and then
    email) an Excel doc. The problem is that the “cpytoimpf” command drops my “.csv” suffix!
    I don’t get it! I’ve found several blogs/help sites, even an article from Mid-Range Computing
    on usage of the “cpytoimpf” command, and I follow all those examples to a ‘T’! But I CANNOT
    get my IFS document to include the “.csv” suffix. Which seems to be mandatory for my email
    command to work. When I use suffix “.xls”, no problem. When I use suffix “.abc”, no problem.
    In fact, it appears that I can name my IFS file/doc ANYTHING I like. EXCEPT a name that ends
    in .csv. When I use suffix “.csv” the .csv gets chopped off! Every time! Without fail! Any idea
    what’s going on? MUCH THANX for any light on this problem. I’ve been banging my head
    on this for a month! rstratton@ethorn.com

  • Nick – When I get my .csv file it is gibberish – I am running a query and placing it into an outfile. Then I am doing your cpytoimpf to copy into IFS as a .csv file. When I go look at it in IFS – it is not in a readable format. What do I check next?

    Thanks!

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips

    >