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:
- Making copy a file to the IFS easier (this post)
- Create and share IFS folder with Windows
- Re-enable disabled IFS user profile
This article was written for IBM i 7.3, and should work for some earlier releases too.
You can also use ACS, version 1.1.7 or higher to download the IFS file to your PC. Just click on the Action menu in the 5250 screen and select Integrated File system. Enter a path to the file in the directory, right click on the file and select download.
ReplyDeleteHaving yet to encounter a shop that allows mapping an IBMi IFS folder to a PC, I added emailing the IFS file instead.
ReplyDeleteAlso, I use a timestamp in the csv filename to avoid conflicts with multiple concurrent users attempting the same thing.
Q: Just curious: what's with the DEL rather than the *REPLACE?
Good stuff, keep it up!
My experience runs counter to yours. All of the IBM i shops I have worked with will allows users to be mapped to IFS folders. The folders are secured using their IBM i profiles.
DeleteI have written about emailing files from the IFS before. The programs I write for the users all use this approach.
Sometimes I need to generate a very large CSV. If the file is large enough it will be rejected by the mail server. Therefore, for my own work I do copy files to IFS folders, map the network drive, copy to my PC, etc.
The idea of using the timestamp as part of the file name is a good one.
As for why I use the DEL command, see my answer to Jay Vaughn comment below.
On the 5250 screen you can select "execute SQL-Scripts".
ReplyDeleteThen go to the Options-Menu and select "always" for download activation.
Then run a select-command to find your data.
Right click the result window and select download. Enter a path in the directory and click OK. At the end of the download the system ask you if you want to open the file in Excel.
Curious why you choose DEL over the cpytoimpf parm replace *yes?
ReplyDeleteMany, many, many years ago an IBMer told me to never use the replace option on any of their commands. He advised me to always use delete/clear as that way I can tell if someone is using the object before the copy is performed.
DeleteBasically, for no other reason than I like to do it this way. The replace option will work fine in this scenario.
Hi Simon
ReplyDeleteIt seems me that you missed "if" in the following your phrase ( I placed if triangular brackets there
):
" 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"
Saludos, estoy usando el comando para crear un archivo plano en una ruta IFS y me queda en codificacion UTF-8 pero necesito que quede como ANSI, como puedo hacer esto?
ReplyDeleteTry STMFCCSID(*STDASCII)
Deletesometimes it makes sense to have the authority of the new file match the directory it is in. STMFAUT(*INDIR)
ReplyDeleteI have created a flat file in qtemp everytime I run the program with ccsid as 37. I did program to write data to that file with semicolon separated. Then i am using cpytoimpf command with fromccsid as *file and stmfccsid as *stmf and i tried *pcascii too..
ReplyDeleteI did map network drive to take the file from PC.
But the issue here is when i open CSV file from PC, it shows all data as unreadable data .
I checked attributes for ifs path and file in the system and it shows as ccsid as 37. But data in ifs in system looks correct. What could be the issue.kindly help here.
The times I have seen this is where the partition's CCSID is defined as 65535.
DeleteThe STMFCCSID should be given as "*PCASCII", and the FROMCCSID as "37", or whatever the CCSID is for the language you use.
Check the partition's default CCSID in the system values too.
I use a very similar batch process to extract data from an ERP application DB and FTP the extracted data to other servers within our intranet/network...
ReplyDeleteI like this method you've outlined for an ad-hoc method when you just want to share a file... The ADDCOLNAM(*SYS) is going to save me some grief and extra work when I want to include column heading on a spreadsheet...!
I always learn something new when I read your posts...!
Thank Simon...!
I am so grateful for your blog.Really thank you!
ReplyDeleteDP-080: Querying Data with Microsoft Transact-SQL