Copy an IBM i File to a CSV comma separated file

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 FILEFMT 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 1967-06-25014 
 ****** 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 1967-06-25 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 ","1967-06-25",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 ","1967-06-25",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 thats a story for another blog….

NOTE: There is also a rather neat way of writing data directly as CSV files from within our RPG programs using RPG OPEN ACCESS. 

What is the RPG Open Access Handler all about? What is open Access Rational® Open Access: RPG Edition provides a way for RPG programmers to use…Jan 8 2014www.nicklitten.com

Replace IBM i Native File Access with SQLI found this excellent article by Birgitta Hauser, Software and Database Engineer, Toolmaker Advance…Jul 9 2017www.nicklitten.com