In an earlier post I showed how it was possible to copy a file to the IFS in CSV format using CPYTOIMPF command. Unless the recipient of the file knows what the columns are they are going to come back to you asking what they are.
So what can you do to provide column headings?
IBM i 7.1 added the ADDCOLNAM to the CPYTOIMPF command. If you press the Help or F1 you will find there is no help available for this parameter, but there is on the developerWorks website. It describes how there are three possible values:
- *NONE - Do not include the column headings, this is the default.
- *SYS - Use the System names, i.e the COLHDG fields in the DDS of the file.
- *SQL - Use the SQL column names.
Below is an example:
CPYTOIMPF FROMFILE(MYLIB/TESTFILE) TOSTMF('/myfolder/testfile.csv')
STMFCCSID(*STDASCII) RCDDLM(*CRLF)
ADDCOLNAM(*SYS)
|
That is great if your DDS file or SQL table has good names, like INV_NBR or ACCT_BAL, but if the file has those old fashioned names, like C4INV# or C4BAL$, the users are going to remain confused.
After thinking about this I came up with what I consider to be a better solution:
- Retrieved the column heading fields from the file.
- Copy the column headings to the desired file in the IFS.
- Copy the data to the same file in the IFS.
In this scenario I am going to copy the contents of the file TESTFILE from MYLIB to the IFS folder MYFOLDER as a CSV file.
I will describe in detail an example programs I coded using the new RPG all free, and I will give at the bottom of this post another using pre-all free code. Both contain SQL statements, therefore, their source type is SQLRPGLE.
I am not going to show how I defined the file and variables, you will be able to see that when I show the whole program.
The program is a RPG program that uses QCMDEXC to execute commands. Lines 10–13, below, shows that I will be calling the program QCMDEXC using a procedure.
10 dcl-pr qCmdExc extpgm ; 11 *n char(256) options(*varsize) const ; 12 *n packed(15:5) const ; 13 end-pr ; |
And as I am going to be calling QCMDEXC multiple times I have put it in a procedure, ExecuteCommand lines 61-66.
61 dcl-proc ExecuteCommand ; 62 monitor ; 63 qCmdExc(String:%len(String)) ; 64 on-error ; 65 endmon ; 66 end-proc ; |
The first thing I have to do it to delete all the work files used by this program, line 18 below, and then call the procedure to execute it. I next need to delete the file I want to create in the IFS, which is the variable inToFile. I move the DEL command to the variable String, and then execute it.
18 String = 'DLTF QTEMP/@*' ;
19 ExecuteCommand() ;
20 String = 'DEL OBJLNK(''' + %trim(inToFile) + ''')' ;
21 ExecuteCommand() ;
|
I use the DSPFFD command for my input file, file name is in the variable inFile and the library is contained in inLib, and direct the output to an outfile, @DSPFFD in the library QTEMP.
22 String = 'DSPFFD FILE(' + %trim(inLib) + '/' + %trim(inFile) +
') OUTPUT(*OUTFILE) OUTFILE(QTEMP/@DSPFFD)' ;
23 ExecuteCommand() ;
|
With the DSPFFD output file built I can now open the file and read it.
24 open @DSPFFD ;
25 dow (1 = 1) ;
26 read @DSPFFD ;
27 if (%eof) ;
28 leave ;
29 endif ;
30 if not(FirstTime) ;
31 Data = %trimr(Data) + ',' ;
32 else ;
33 FirstTime = *off ;
34 endif ;
35 if ((WHCHD1 + WHCHD2 + WHCHD3) = ' ') ;
36 ColumnHeadings = %trimr(ColumnHeadings) + WHFLDI ;
37 else ;
38 ColumnHeadings = '"' ;
39 if (WHCHD1 <> ' ') ;
40 ColumnHeadings = %trimr(ColumnHeadings) +
%triml(WHCHD1) ;
41 endif ;
42 if (WHCHD2 <> ' ') ;
43 ColumnHeadings = %trimr(ColumnHeadings) + ' ' +
%triml(WHCHD2) ;
44 endif ;
45 if (WHCHD3 <> ' ') ;
46 ColumnHeadings = %trimr(ColumnHeadings) + ' ' +
%triml(WHCHD3) ;
47 endif ;
48 endif ;
49 ColumnHeadings = %trimr(ColumnHeadings) + '"' ;
50 Data = %trimr(Data) + ColumnHeadings ;
51 enddo ;
52 close @DSPFFD ;
|
Line 30 – 34 are executed to insert a comma before each column to make the CSV. But the comma only needs to be inserted after the first column and not after the last column.
Line 35 checks that there is anything in the column heading fields, if not then the field name is used.
Lines 38 – 47 put the three column heading together, for example to take the three column heading fields 'Invoice', 'balance', and 'amount' and make “Invoice balance amount. The last double quote is added on line 49.
Line 50 the consolidated column headings are added to the field Data, which is being used as the CSV’s row/record for the headings.
When I finished reading all of the records from the DSPFFD outfile file I need to close it, line 52.
While testing I found that I would have less issues with CCSID mapping if I output the headings to a SQL table rather than a file. This could be caused by the CCSID of the IBM i I was using and the files I used. Below is the SQL code I used to create the table:
53 exec sql CREATE TABLE QTEMP/@OUTFILE
(HEADINGS CHAR(5000)
NOT NULL WITH DEFAULT) ;
|
Now to insert the CSV row contained in the field Data into the table.
54 String = 'INSERT INTO QTEMP/@OUTFILE +
VALUES (''' + %trim(Data) + ''')' ;
55 exec sql EXECUTE IMMEDIATE :String ;
|
With the table filled it is then time to copy it to the IFS using the CPYTOIMPF command:
56 String = 'CPYTOIMPF FROMFILE(QTEMP/@OUTFILE) +
TOSTMF(''' + %trim(inToFile) + ''') +
MBROPT(*REPLACE) +
STMFCODPAG(*PCASCII) +
RCDDLM(*CRLF) +
STRDLM(*NONE)' ;
57 ExecuteCommand() ;
|
And then the data from the file I wanted to copy to the IFS:
58 String = 'CPYTOIMPF FROMFILE(' + %trim(inLib) + '/' +
%trim(inFile) + ') +
TOSTMF(''' + %trim(inToFile) + ''') +
MBROPT(*ADD) +
STMFCODPAG(*PCASCII) +
RCDDLM(*CRLF) +
STRDLM(*NONE)' ;
59 ExecuteCommand() ;
|
For my testing I created a very simple DDS file, see below:
A R TESTFILER
A FLD1 10A COLHDG('First' 'field')
A FLD2 10A COLHDG('Second' 'field')
|
When I ran this program the CSV file in the IFS looked like:
"First field","Second field" 1 ,One 2 ,Two 3 ,Three |
If you know of another or better way to add column headings to a file in the IFS please let me know. Do not add your method in the comments, as the formatting is weird. Use the Contact form, on the right, to send me a message and I will reply with my email address. Thanks
Here is the entire program in RPG all free:
01 ctl-opt dftactgrp(*no) ;
02 dcl-f @DSPFFD disk usropn
extfile('QTEMP/@DSPFFD') ;
03 dcl-s inLib char(10) ;
04 dcl-s inFile char(10) ;
05 dcl-s inToFile char(256) ;
06 dcl-s Data char(5000) ;
07 dcl-s ColumnHeadings char(60) ;
08 dcl-s FirstTime ind inz('1') ;
09 dcl-s String char(5500) ;
10 dcl-pr qCmdExc extpgm ;
11 *n char(256) options(*varsize) const ;
12 *n packed(15:5) const ;
13 end-pr ;
14 C *entry plist
15 C parm inLib
16 C parm inFile
17 C parm inToFile
18 String = 'DLTF QTEMP/@*' ;
19 ExecuteCommand() ;
20 String = 'DEL OBJLNK(''' + %trim(inToFile) + ''')' ;
21 ExecuteCommand() ;
22 String = 'DSPFFD FILE(' + %trim(inLib) + '/' + %trim(inFile) +
') OUTPUT(*OUTFILE) OUTFILE(QTEMP/@DSPFFD)' ;
23 ExecuteCommand() ;
24 open @DSPFFD ;
25 dow (1 = 1) ;
26 read @DSPFFD ;
27 if (%eof) ;
28 leave ;
29 endif ;
30 if not(FirstTime) ;
31 Data = %trimr(Data) + ',' ;
32 else ;
33 FirstTime = *off ;
34 endif ;
35 if ((WHCHD1 + WHCHD2 + WHCHD3) = ' ') ;
36 ColumnHeadings = %trimr(ColumnHeadings) + WHFLDI ;
37 else ;
38 ColumnHeadings = '"' ;
39 if (WHCHD1 <> ' ') ;
40 ColumnHeadings = %trimr(ColumnHeadings) + %triml(WHCHD1) ;
41 endif ;
42 if (WHCHD2 <> ' ') ;
43 ColumnHeadings = %trimr(ColumnHeadings) + ' ' + %triml(WHCHD2) ;
44 endif ;
45 if (WHCHD3 <> ' ') ;
46 ColumnHeadings = %trimr(ColumnHeadings) + ' ' + %triml(WHCHD3) ;
47 endif ;
48 endif ;
49 ColumnHeadings = %trimr(ColumnHeadings) + '"' ;
50 Data = %trimr(Data) + ColumnHeadings ;
51 enddo ;
52 close @DSPFFD ;
53 exec sql CREATE TABLE QTEMP/@OUTFILE
(HEADINGS CHAR(5000)
NOT NULL WITH DEFAULT) ;
54 String = 'INSERT INTO QTEMP/@OUTFILE +
VALUES (''' + %trim(Data) + ''')' ;
55 exec sql EXECUTE IMMEDIATE :String ;
56 String = 'CPYTOIMPF FROMFILE(QTEMP/@OUTFILE) +
TOSTMF(''' + %trim(inToFile) + ''') +
MBROPT(*REPLACE) +
STMFCODPAG(*PCASCII) +
RCDDLM(*CRLF) +
STRDLM(*NONE)' ;
57 ExecuteCommand() ;
58 String = 'CPYTOIMPF FROMFILE(' + %trim(inLib) + '/' +
%trim(inFile) + ') +
TOSTMF(''' + %trim(inToFile) + ''') +
MBROPT(*ADD) +
STMFCODPAG(*PCASCII) +
RCDDLM(*CRLF) +
STRDLM(*NONE)' ;
59 ExecuteCommand() ;
60 *inlr = *on ;
61 dcl-proc ExecuteCommand ;
62 monitor ;
63 qCmdExc(String:%len(String)) ;
64 on-error ;
65 endmon ;
66 end-proc ;
|
Entire program in pre-TR7 RPG free:
01 H dftactgrp(*no)
02 F@DSPFFD IF E DISK usropn extfile('QTEMP/@DSPFFD')
03 D inLib S 10
04 D inFile S 10
05 D inToFile S 256
06 D Data S 5000
07 D ColumnHeadings S 60
08 D FirstTime S N inz('1')
09 D String S 5500
10 D qCmdExc PR extpgm('QCMDEXC')
11 D 256 options(*varsize) const
12 D 15 5 const
13 C *entry plist
14 C parm inLib
15 C parm inFile
16 C parm inToFile
/free
17 String = 'DLTF QTEMP/@*' ;
18 exsr ExecuteCommand ;
20 String = 'DEL OBJLNK(''' + %trim(inToFile) + ''')' ;
21 exsr ExecuteCommand ;
22 String = 'DSPFFD FILE(' + %trim(inLib) + '/' + %trim(inFile) +
') OUTPUT(*OUTFILE) OUTFILE(QTEMP/@DSPFFD)' ;
23 exsr ExecuteCommand ;
24 open @DSPFFD ;
25 dow (1 = 1) ;
26 read @DSPFFD ;
27 if (%eof) ;
28 leave ;
29 endif ;
30 if not(FirstTime) ;
31 Data = %trimr(Data) + ',' ;
32 else ;
33 FirstTime = *off ;
34 endif ;
35 if ((WHCHD1 + WHCHD2 + WHCHD3) = ' ') ; // Column headings
36 ColumnHeadings = %trimr(ColumnHeadings) + WHFLDI ;
37 else ;
38 ColumnHeadings = '"' ;
39 if (WHCHD1 <> ' ') ;
40 ColumnHeadings = %trimr(ColumnHeadings) + %triml(WHCHD1) ;
41 endif ;
42 if (WHCHD2 <> ' ') ;
43 ColumnHeadings = %trimr(ColumnHeadings) + ' ' + %triml(WHCHD2) ;
44 endif ;
45 if (WHCHD3 <> ' ') ;
46 ColumnHeadings = %trimr(ColumnHeadings) + ' ' + %triml(WHCHD3) ;
47 endif ;
48 endif ;
49 ColumnHeadings = %trimr(ColumnHeadings) + '"' ;
50 Data = %trimr(Data) + ColumnHeadings ;
51 enddo ;
52 close @DSPFFD ;
53 exec sql CREATE TABLE QTEMP/@OUTFILE
(HEADINGS CHAR(5000)
NOT NULL WITH DEFAULT) ;
54 String = 'INSERT INTO QTEMP/@OUTFILE +
VALUES (''' + %trim(Data) + ''')' ;
55 exec sql EXECUTE IMMEDIATE :String ;
56 String = 'CPYTOIMPF FROMFILE(QTEMP/@OUTFILE) +
TOSTMF(''' + %trim(inToFile) + ''') +
MBROPT(*REPLACE) +
STMFCODPAG(*PCASCII) +
RCDDLM(*CRLF) +
STRDLM(*NONE)' ;
57 exsr ExecuteCommand ;
58 String = 'CPYTOIMPF FROMFILE(' + %trim(inLib) + '/' +
%trim(inFile) + ') +
TOSTMF(''' + %trim(inToFile) + ''') +
MBROPT(*ADD) +
STMFCODPAG(*PCASCII) +
RCDDLM(*CRLF) +
STRDLM(*NONE)' ;
59 exsr ExecuteCommand ;
60 *inlr = *on ;
61 begsr ExecuteCommand ;
62 monitor ;
63 qCmdExc(String:%len(String)) ;
64 on-error ;
65 endmon ;
66 endsr ;
|
You can learn more about these from the IBM web site:
- developerWorks documentation for ADDCOLNAM
- DSPFFD Display File Field Description command
- CPYTOIMPF Copy to Import File command
- SQL CREATE TABLE
- SQL INSERT
This article was written for IBM i 7.1.
Update
After publishing this post I received an example of how to do the same using SQL, you can read it here.
I have a multi-member physical file called CSV.HEAD in which I put the column headings that I want, separated by commas. Then, I copy CSV.HEAD(myfile) into the IFS before appending myfile. This works very well for send end users the results of queries in the form of a CSV file (a.k.a. spreadsheet).
ReplyDeleteYou could also retrieve column headings from one of the DB2 System Catalog views: SYSCOLUMNS. Great info in QSYS2 tables/views.on all aspects of the database.
ReplyDeleteI did try the SQL SYSCOLUMNS at first. The problem was with the way SQL interprets the Column Headings. Rather than break them out into 3 fields, it interprets them, as 1 very long field. The very long field was just too unwieldy to use.
DeleteAlter Table would allow you to change the headings, but I found long ago that I would want to make the column names in the "from" file have good descriptive names, so the column itself (field) has a usable name. It is a practice that I put the most effort into.
DeleteWow - that looks like a lot of work! What I have done from V5R3 and up is.
ReplyDelete1) Create a header file with 20 fields or so of 16 or 20 characters each
2) STRDFU and insert one record and type in the header names.
- If someone doesn't like the name - use dfu to change it.
3) In the CL to create the .csv file we create or replace the csv file with the header file with the one record.
4) Append to that .csv file the data file with all the data.
Now you could use the automatic column names, but this gives you or your administrator, more flexibility in the column names.
Wow, that sounds like a lot of work! And sounds like a change management and standards nightmare. Now you've some unknown number of tables containing column heading information that need to be maintained and distributed in parallel with your database tables - but there is no formal relationship in the DB to give a clue to anyone but you that these special column heading tables even exist.
DeleteWhy not just use the standard, built-in database support for column headings and/or descriptive text provided by the DDS COLHDG and TEXT keywords, or, better yet, the SQL DDL statement LABEL ON? DB2 has a place for column headings and text labels right in the metadata repository. Tools know how to find and use them automatically when populated, it is a standard in SQL language, and tools that are SQL-aware (or DDS-aware for that matter) know where to go to look for it.
And if you need to change a column heading you just execute the SQL LABEL ON command.
With Simon's (far superior, in my opinion) approach, you store column headings where everyone already knows to look for them, and where they automatically get distributed when a new version of the table is distributed. You create one little reusable module (or stored procedure) and call it whenever you need to generate CSV column headings.
On the lot of work and change issue:
DeleteI get clients that don't like the field name, or header name. Instead of address number or address name they want account name or number. I am doing one this weekend where we script out a file for our client's client. They want the fields to be named policy or plan or whatever - it is up to them. I worked for Federal Government (Canada) Agency where everything had to bilingual. That meant copying the DDS for screens etc. Translate and redo for the screens in French. I can do that for display files and print files (this was a few years back). What I proposed is self contained in the CL that is called. You have to have some documentation in the CL. So CRTCSVA would do one. If you need French or Spanish - this solution gives you complete freedom, from a user perspective to format the csv / spreadsheet headings as they please. I am not messing with the clients system. It works in my clients context, I may not work for everyone.
Tom,
DeleteI understand the requirement. It isn't uncommon to have a need for "flexible" column headings and field labels - especially in systems that need to support multiple languages, or multiple customers (SaaS systems).
The best systems I've seen for managing those requirements did something like this:
Create an external string table containing report headings, screen labels, column headings table, etc, and write a text retrieval module capable of looking up the correct headings that is called by your various applications. As you convert programs to use this system, headings in screens and reports become variables.
There are a lot of advantages to this "single table" approach:
- All of your strings are centralized in one table (really a series of same-name tables in different product or language libraries), which makes string table maintenance very easy to manage through a single program.
- You can handle multi-language requirements by either including a language key column in the string table (but that can lead to a large, poor performing table, especially if you need to support a large number of languages simultaneously on the same system). I prefer to manipulate the library list based on the language associated with a given user or job, and have one string table (or set of string tables) per language. You can do the same thing to allow a relatively simple string table and lookup program to support multiple products or components within a larger application.
- Adding support for new languages is simplified: Clone the string table for that product or component into the correct language library, and translate each string - no hunting around to find a bunch of discrete column heading tables scattered throughout the system, or hardcoded column headings embedded in each program.
- Create a standard module packaged in a service program for getting column names and other strings. You can create a hierarchy of lookups, for example, the string table is first searched by an application-specific key, then by a blank application key (for labels shared by most applications), and if no text is found in the string table, a SYSCOLUMNS lookup is performed for column heading lookups, if desired. Do these lookups one time at program initiation and cache them so you don't look them up each time a screen is reloaded.
- Wrap this string lookup module with a stored procedure, and now even non-native programs can access column names and other strings in a standard way.
Good tip, been there done that....nice to have a great tip like this
ReplyDeleteI think you have a good solution on your site, but it is a lot of work. One of the recommendations in the comments suggested outputing the column headings as the first record in the file. But that won't work with numeric or date fields.
ReplyDeleteWhat I usually do is:
CRTPF in QTEMP - With Column Names I like
exec SQL - to insert recrods
CPYTOIMPF from the QTEMP file
If you want even more descriptive names you can:
CRTPF in QTEMP - With one long field say 1024 bytes
exec SQL - Insert headings as a single character string with commas for the CSV
exec SQL - Insert records as a single character string with commas for the CSV CPYTOIMPF from the QTEMP file
Or you could just use the appTools CPYTOCSV command and specify HEADERS(*COLHDG) and it'll put the headings into the first row. You could also use *FLDNAME or *TEXT or even *NONE. It's free and runs about the same speed or better than CPYTOIMPF but I understand, CPYTOIMPF is on everyone's system. - Bob Cozzi
ReplyDeleteNote: CPYTOCSV is not a native IBM i command. You would need to have the appTools library to use it,
DeleteADDCOLNAM parameter seems to have been added as a PTF for V6R1 as we can see the parameter on CPYTOIMPF on our V6 boxes. Interesting that we've just upgraded one of our boxes to V7R1, tried to compile a CL program for TGTRLS(V6R1M0) and it seems the *PRV compiler support doesn't recognise the parameter! We'll have to get onto IBM about that.
ReplyDeleteI wrote my own tool to create csv and diff files many years ago - I had support for including field names/colhdgs way back in 1997! It still works and runs on good old RPG/400 under the covers! - Nick Falconer
This worked perfectly, except for few files, where the column order no is coming as corrupted data. Can you give some idea. I tried to download the same file using Download from iSeries tool but there also the field is being reported in corrupted format.
ReplyDeleteCheck the CCSID of the column and the file.
DeleteSimon,
ReplyDeleteI just found this program of yours a few days ago and put it into my test library. I noticed after a couple of tests that when the file created via this program is opened in Excel, the last column is always much wider than the heading. The last column heading in my test is only 14 characters in length. However, the column in the spreadsheet is 167 characters wide and will not adjust to the length of the heading by double clicking on the right side of column. I tried a different column length, with the same result. I've tried several different files with the same result. What is going on with this? I was hoping to use this in our processes, but this is concerning.
I have never noticed that happen when I have used this method.
DeleteThe trouble here is we are at that he (IBM i) said, she (Excel) said point where it could be either.
Is this really a XLS or XLSX file? Or is it CSV?
If it is CSV open it using Notepad and see if you have the same issue there.
I failed to mention that I have tried this as both a .csv and as .txt with the same results.
DeleteSimon,
ReplyDeleteI just came across this posting and thought I'd mention something that I've found useful when dealing with *OUTFILEs. In your dcl-f above, you specify extfile('QTEMP/@DSPFFD'). This requires that this file be created for the compile. For several commands that have *OUTFILE capabilities, like DSPFFD, DSPFD, and DSPOBJD to mention some I use frequently, IBM has very nicely provided template files that correspond to whatever command and format you are using. In this case, the template file for DSPFFD is QSYS/QADSPFFD. This may be used in your example by changing your dcl-f as follows:
dcl-f @DSPFFD disk usropn extfile('QTEMP/@DSPFFD')
extdesc('QSYS/QADSPFFD');
extdesc says "use this file to get file structure for compiling" and extfile says "use this file during execution".
I hope you find this helpful. I love your site!
Thanks,
Kevin
Copied and compiled the program and the precompiler failed with message :
ReplyDeleteMSG ID SEV RECORD TEXT
SQL1001 30 31 External file definition for @DSPFFD not found.
On V7R3M0 and not sure with extfile, why would it do that?
Any clues?
thanks
Did you create a "dummy" @DSPFFD before you compiled the program?
DeleteThe program needs the file to be present in your luibrary list to be able to compile.