IBM i > TIPS & TECHNIQUES > SYSTEMS MANAGEMENT

Using FTP to Exchange Data with CSV Files


Most database and spreadsheet applications can import and export files in a comma-separated value (CSV) format. OS/400* also provides these capabilities, through the use of the Copy to Import File (CPYTOIMPF) and Copy From Import File (CPYFRMIMPF) commands. This allows iSeries data, once converted to CSV, to be transferred via FTP for use in PC-based applications such as Microsoft* Excel and Lotus* 1-2-3.

As its name implies, CPYTOIMPF copies an iSeries database file into an import file. The import file contains the database file data, converted into a readable form using any field and record delimiters you designate. Special numeric fields, such as packed decimal data, which are normally not transferable using FTP, are converted to text fields. This file can be placed in the IFS or in a QSYS library.

Of the two options, placing the file in a QSYS library is the easiest process. Let's examine the steps involved. First, create a file that shares the import file name. For example, this command copies FTPSAMP, a database file, to FTPCSV, an import file previously created with the Create Physical File (CRTPF) command:

CPYTOIMPF FROMFILE(TESTLIB/FTPSAMP)  +  TOFILE(TESTLIB/FTPCSV)

By default, CPYTOIMPF creates a file in CSV format. Double quotes (") are used to identify Character fields, and commas are used to separate fields. (If you choose to create the import file in the IFS, you'd normally specify *CRLF [Carriage Return/Line Feed] for the record delimiters rather than *EOR [EBCDIC End-Of-Record] used in the QSYS libraries.)

This command copies the data from the first  member in file FTPDB into the first member of file FTPCSV and translates it into CSV format:

CPYTOIMPF FROMFILE(QGPL/FTPDB)  +  TOFILE(QGPL/FTPCSV)

The resulting file is stored in QSYS as an EBCDIC text file with the double quotes and commas typically found in a CSV file. In order to use this file on an ASCII system (which includes most PCs and UNIX* and Linux* systems), it must be converted to ASCII. Because the data, including special numeric fields such as packed decimals, has been converted to text, FTP can be used to create a properly formatted ASCII CSV file on the PC. Code Sample 1 shows the FTP commands for a Windows* PC used to copy the file.

 

Because the data, including special numeric fields such as packed decimals, has been converted to text, FTP can be used to create a properly formatted ASCII CSV file on the PC.

Bill Osler is an advisory software engineer in the Rochester Support Center.


comments powered by Disqus
Buyers Guide

Advertisement

Migrating IBM i Directory Server from 5.4 to 6.1

What to expect and how to solve migration problems

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters

Advertisement