Often I find it easier to analyze the contents of a file using Microsoft's Excel than using the IBM i based tools. I take the file, or table, and copy it to my folder in the IFS, and then copy it from there to my PC. A colleague asked how did I do this so quickly, and having showed him how I do, he recommended that this would be a good subject for a post in this blog.

I am surprised how few developers I talk to build their own set of programs to automate tasks they perform every day. Too many times I see these people type in command names, prompt with F4, and fill in the same parameters time and again to do the same task. I admit I don't want to do that, if I need to perform the same task more than just a few times I will create a program containing all the commands I need. Then I can call the program with just a few parameters, and the program will do everything. If I want to copy a file to the IFS I just need the file and library, the rest will always be the same.

I could call the program with the file and library as parameters, but I prefer to have a display file to make sure that the file and/or library entered are not mistyped.

Below is the source of a stripped down version of the display file I have:

01 A DSPSIZ(24 80 *DS3) 02 A PRINT 03 A ERRSFL 04 A CA03(03 'F3=Exit') 05 A R REC01 06 A 1 32'Copy object to IFS' 07 A 3 3'File . . . .' 08 A ZFILE 10A B 3 16 09 A 50 ERRMSG('File not found in + library') 10 A 4 5'Library .' 11 A ZLIB 10A B 4 18 12 A 51 ERRMSG('Library not found') 13 A 23 3'F3=Exit' 14 A COLOR(BLU)

Line 3: Why would I not use the display file's own error subfile to display any messages? Let the operating system do the work to display the errors.

Line 8: The field for the file name.

Line 9: If the file entered does not exist in the library then indicator 50 will be set on and this message will be displayed in the error subfile.

Line 11: This is the field for the library name.

Line 12: If the library entered does not exist then indicator 51 is set on and this message will be displayed in the error subfile.

I don't think I need to say more about this display file as I assume you are familiar with the rest of the code within it.

For what I want to do here a CL program is best. I am going to divide the program's source into three sections to make it easier to describe here. Let me start with the definitions:

01 PGM 02 DCLF FILE(CPYTOIFSD) 03 DCL VAR(&STREAMFILE) TYPE(*CHAR) LEN(40) 04 DCL VAR(&LOOP) TYPE(*LGL) VALUE('1')

Line 2: This is the definition for the above display file.

Line 3: This variable will contain the name of the IFS folder and file.

Line 4: This variable is to condition a Do-loop.

The next section is for the display file and the validation of the entered file and library names.

05 DOWHILE COND(&LOOP) 06 SNDRCVF 07 IF COND(&IN03) THEN(RETURN) 08 CHGVAR VAR(&IN50) VALUE('0') 09 CHGVAR VAR(&IN51) VALUE('0') 10 CHKOBJ OBJ(&ZLIB) OBJTYPE(*LIB) 11 MONMSG MSGID(CPF0000) EXEC(DO) 12 CHGVAR VAR(&IN51) VALUE('1') 13 ITERATE 14 ENDDO 15 CHKOBJ OBJ(&ZLIB/&ZFILE) OBJTYPE(*FILE) 16 MONMSG MSGID(CPF0000) EXEC(DO) 17 CHGVAR VAR(&IN50) VALUE('1') 18 ITERATE 19 ENDDO 20 LEAVE 21 ENDDO

Line 5: The start of the Do loop, that ends at line 19.

Line 6: As there is only one record format in the display file I can just use the SNDRCVF command without the record format name.

Line 7: Alas, there is no indicator data structure in CL, so I have to use the number indicator for the F3 key.

Lines 8 and 9: These are the indicators I will be using for flagging errors. Without the indicator data structure I have to use the numbers rather than have indicators with meaningful names.

Line 10: Check if the library exists. I check the library first rather than the file, if the library does not exist neither does the file.

Lines 11 – 14: A Monitor message group. If there is an error when the command is executed indicator 51 is turned on, and I iterate to the top of the Do loop.

Line 15: As I know the library exists I can check for the file.

Lines 16 – 19: This Monitor message group is pretty much the same as the previous one, this time indicator 50 is turned on.

Line 20: If there were no errors I leave the Do loop, and continue to the next section of the program.

22 CHGVAR VAR(&STREAMFILE) VALUE('/MyFolder/' || + &ZLIB |< '_' || + &ZFILE |< '.csv') 23 DEL OBJLNK(&STREAMFILE) 24 MONMSG MSGID(CPFA0A9) 25 CPYTOIMPF FROMFILE(&ZLIB/&ZFILE) TOSTMF(&STREAMFILE) + 26 FROMCCSID(37) + 27 STMFCCSID(*PCASCII) + 28 RCDDLM(*CRLF) + 29 ADDCOLNAM(*SYS) 30 ENDPGM

Line 22: I want the file to be copied to the IFS folder MyFolder with the name library_file.csv . Therefore I need to build the string to contain that in a variable, using the concatenation shortcuts.

Line 23: Rather than "replace" I prefer to try and delete a file with the same name from the folder first. There are several different commands I can use to do that, I have preferred using DEL as I think it obvious what it does.

Line 24: This Monitor message stops the DEL from erroring if the file is not present.

Lines 25 – 29: I use the Copy To Import File command, CPYTOIMPF , to copy my IBM i file to the IFS. I have found that with the IBM i partitions I work with are set to be American English and I do not use the FROMCCSID , line 26, the characters in the IFS file are rubbish. Depending on your IBM i you may not have to do this. I want my IFS to be a PC compatible ASCII file, line 27. The end of record needs to be *CRLF , carriage return followed by line feed, line 28. And I want the column headings to be the names of the fields from the copied file.

When I call this program the IBM i file is copied to my IFS folder. I open Operation Navigator, open File Systems, Root, find my folder and double click on it to open. I drag-and-drop the file to a folder on my PC. As a CSV, comma separated variable length, file I can open it in Microsoft's Excel and save it as a Excel spreadsheet. I can then use all of Excel functions to analyze the data within.

Rather than use Operations Navigator I could map a Window's Network Drive to my folder and then use that to get to my copied file. I will explain how to map a network drive to a folder in IFS in another post.

Posts in this trilogy:

This article was written for IBM i 7.3, and should work for some earlier releases too.