There are quite many ways to get data from the IBM i to a PC. FTP is a relative simple method to use, however if the file contains packed or binary fields you end up having a PC file containing strange characters. You have to find another, and typically more complicated, method to do it.

Today this changes as I will show you a Windows Command file, that will transfer a physical or logical file from the IBM i and create a PC file in CSV format, where packed and binary fields are converted to a readable format and columns having headers 🙂

The CSV file that the Windows Command file builds, will be in European standard, that is, use semicolon as field separator and the decimal point is a comma:

“Field 1″;”Field 2”;123,45;678,90

The CSV file is ready to be read by, e.g., Execl®, your BI modul (e.g. SAS®) or some other application that requires data in a CSV file.

You can get the Windows Command file, called GetCSVFileFtp.cmd, from here.

The syntax for GetCSVFileFtp.cmd is:

GetCSVFileFtp  library  pf/lf_fil  PC_file  IBM_i  user_profile  password  header

If you run GetCSVFileFtp without parameters, you will be prompted for the parameters.

The CSV file gets the field descriptions from the TEXT() keyword from the DDS/SQL definition as column heading 🙂

How does it work?

The basics of this Windows Command file is that I get the FTP server to execute a series of commands, and the result of these commands are transferred to the PC.

To start with, a series of FTP scripts are executed that verifies that the host you have entered for the parameter IBM_i is indeed an IBM i, that user id and password you have entered are valid on the IBM i. Last but not least, a check is done that the file exists in the library.

The exciting part starts after the initial controls. To get texts for the column headings, the command Display File Field Description (DSPFFD) is run and the result is written to an outfile. This outfile is converted to text format and transferred to the PC. Below is a snip of the part that does just that:

Echo open %iseriesname% >"%scriptfile%"
Echo %user% >>"%scriptfile%"
Echo %pwd% >>"%scriptfile%"
Echo QUOTE TIME 3600 3600 >>"%scriptfile%"
Echo quote rcmd DSPFFD %lib%/%file% OUTPUT(*OUTFILE) OUTFILE(QTEMP/XYZTEMP) >>"%scriptfile%"
Echo quote rcmd CPYTOIMPF FROMFILE(QTEMP/XYZTEMP) TOSTMF('/tmp/%username%') RCDDLM(*CRLF) DTAFMT(*FIXED) >>"%scriptfile%"
Echo quote rcmd DLTF QTEMP/XYZTEMP >>"%scriptfile%"
Echo asc >>"%scriptfile%"
Echo get /tmp/%username% "%filelist%" >>"%scriptfile%"
Echo quote rcmd DEL '/tmp/%username%' >>"%scriptfile%"
Echo Quit >>"%scriptfile%"

Please note that it is the command Copy To Import File (CPYTOIMPF) that does the hard job of converting the outfile from the command Display File Field Description (DSPFFD). Copy To Import File converts the file to text format and in this process the binary and packed fields in the outfile is converted to text format, making them readable for the next part of the Windows Command file.

After the above section of the Windows Command file has run, there is a file on your PC that contains the description of all fields in the file you want to transfer. BUT the fields is organized as one field per record. I need to get the field descriptions and make one record containing the descriptions. The snip below shows the part of the Windows Command file that does the reading of the file containing one field per record and build one record containing all the field descriptions:

REM
REM Convert the Field Description file and make a heading out of it.
REM
if exist "%fldhdr%" del "%fldhdr%"
for /F "usebackq tokens=*" %%A in ("%filelist%") do (
set wline=%%A
set ftext=!wline:~184,50!
REM
REM Below, a trick to avoid CRLF in the file between the writes.
REM
 <NUL set /p= ""!ftext!";" >> "%fldhdr%"
)
Echo.>> "%fldhdr%"

The environment variable %filelist% contains the name of the PC file that contains the outfile from the Display File Fields Description (DSPFFD) command. The line that starts ‘for …’ reads the file %filelist% one record at the time, and for each record the statements in parenthesis is executed.

To get the description for the field in question, a substring is performed on the line read from the file. In position 184 and 50 characters forward, is the description of the field.  If you prefer the actual field name (DDS/SQL) then you must change this line do to the substring from position 136 and 10 positions forward.

This line:

 <NUL set /p= ""!ftext!";" >> "%fldhdr%"

writes the field name to the file %fldhdr% WITHOUT a carriage return and a line feed is inserted to the file. Because of this, I have to add the CRLF manually to the file when the line containing the fields names/descriptions has been written to the file. This is done by simply executing a Echo to the file. Please note the dot right after ‘Echo’:

Echo.>> "%fldhdr%"

A small ‘but’ …

The method that the Windows Command file converts and transfer an IBM i physical or logical file to a PC file in CSV format is quite smart, but compared to other file transfer tools like Client Access®‘s File Transfer, my Windows Command file does not offer the option to select data. It is all-or-nothing. There are ways around this by making a Query, QM Query or and SQL and then transfer the result file.

Another small ‘but’ …

It has turned out, that some files have field descriptions that contains the characters ‘*’, ‘(‘ and ‘)’. This is the case for the output file from the command DSPFFD. There characters gets interpreted by the command processer in Windows. The result is, that the headings becomes quite strange. This is the reason why, I have added the parameter ‘header’. You can select to get the header or not.

Need the CSV file in US format?

If you need the CSV file in US format, that is, the fields are separated by comma and the decimal point is a dot, then you must change the line below in the Windows Command file:

Echo quote rcmd CPYTOIMPF FROMFILE(%lib%/%file%) TOSTMF('/tmp/%username%') RCDDLM(*CRLF) DTAFMT(*DLM) FLDDLM(';') DECPNT(*COMMA) >>"%scriptfile%"

It must be changed to:

Echo quote rcmd CPYTOIMPF FROMFILE(%lib%/%file%) TOSTMF('/tmp/%username%') RCDDLM(*CRLF) DTAFMT(*DLM) FLDDLM(',') DECPNT(*PERIOD) >>"%scriptfile%"

All that is left is to say enjoy this very useful Windows Command file 🙂