The number one request from end users today seems to be, "Can I get this data into Excel?" I won't argue the validity of this request; however, I think most of the time they want data in Excel format because they don't realize that the iSeries can show them their data in a subfile or list and can order that data any way they want. I suppose it seems easier for them to simply ask for it in Excel and then sort/filter it as they see fit.
Of course, creating a true Excel format file is somewhat impractical for an end user shop. If you're a multi-million-dollar software company and can afford it, your best choice is to create a tool that converts data directly into .XLS files, but the vast majority of us out there just don't have the time or budget to do take this approach.
The second best choice for creating Excel files from iSeries database files is to convert them into Data Interchange Format (DIF). DIF is a published format for exchanging data between spreadsheet and database files. A DIF file is oriented toward row and column data, similar to that of a spreadsheet. Because Excel and other programs can rotate rows and columns, the DIF specification uses the term vector to mean row and tuple to mean column.
DIF files contain two sections: a file header and a data section. The header section describes file and cell attributes, whereas the data section contains data.
The only bad thing about DIF files is that each cell of a spreadsheet occupies two or more lines (records) in the DIF file, so you end up with very narrow files that are incredibly lengthy. And, ironically, while DIF format does give you the most capability, the specification is a bit archaic and is generally not recognizable by most applications.
If you need to convert data to Excel and want the spreadsheet described in detail, use the DIF format.
The third choice for Excel format is comma-separated values (CSV) files. CSV files are textual in nature and separate each field in a record using a comma. In addition, text fields are normally quoted unless they contain no embedded commas or quotes themselves; however, I find it best to always quote text fields.
Unlike DIF files, CSV files place the entire record on one line in the CSV file. So if you have a 200-record database file, you should end up with a 200-line CSV file.
Converting iSeries data to CSV format is pretty easy today. Several third-party tools, such as ASC's SEQUEL product, can output directly to CSV and to Excel format. SEQUEL probably gives you the best solution for moving data to Excel format, and if you already own SEQUEL, you should be familiar with its interface. If you aren't, see your SEQUEL user manual for details or go online and click on the Products link and then the SEQUEL link.
The IBM-supplied CPYTOIMPF is a free option for converting a typical database file to CSV format. But CPYTOIMPF is slooooow.
People tell me that on multiprocessor iSeries systems, the speed of the CPYTOIMPF command is substantially improved. If any of my readers can verify that this is true on a multiple-CPU system, I would appreciate it if you'd report back to me via the forums discussion associated with this article. But for the vast majority of us, I recommend using CPYTOIMPF only when nothing else is available.
The RPG xTools' CPYTOCSV command also creates CSV files for Excel or other applications. It stores these CSV files on the IFS. End users who have a mapped network drive to that IFS directory can open the CSV file directly into Excel. In fact, double-clicking on the CSV file is usually all that's necessary to launch Excel with the file loaded into it.
The CPYTOCSV command runs 6 to 20 times faster than CPYTOIMPF, and when you need to convert lots of records to CSV format, every minute counts.
To copy an iSeries database file to the IFS in CSV format, the following RPG xTools command may be used:
In this example, the regional sales file, RGNSALES, is converted to the CSV file named SALESDATA.CSV in the /HOME/COZZI directory on the IFS. The first row of data in the output file will be a list of field names from the database file. This is the "header row" used by the spreadsheet to title the individual columns.
The RPG xTools CSV support also allows you to open directly from and write directly to the IFS in CSV format. And it does the conversion from the RPG input buffer to the CSV format for you. This means that you could easily add CSV output as an option to any new or existing RPG IV program.
RPG xTools provides CSV support at three levels to give you control and flexibility:
- CL command--The top level is, of course, the CPYTOCSV CL command. It copies a whole or partial file to CSV format. You can specify the starting record number and the number of records to copy.
- RPG IV call-level--Within RPG IV, you can call the CvtToCsv() procedure to convert a data structure that contains the input values from a database file into CSV format. You simply specify the file name that contains the format of the data you want to convert, and the subprocedure converts it to CSV for you.
- RPG I/O level--Going a step further, you can open a CSV file on the IFS, read from your own database file, and write to the opened CSV file on the IFS. This provides I/O level support for CSV files (technically speaking, it is only O--output--support, not I/O).
I recently combined the call-level CSV support with a CGI program to allow output directly into Excel from the CGI program. This way, Excel can be launched as a plug-in to the Web browser. By writing the correct CGI header to the browser and then sending the CSV data to the browser, I gave the end user the option to open up the result directly into Excel. Needless to say, this was well-received. An example CGI program that sends data directly into Excel from a CGI request is listed in Figure 1.
|
Figure 1: Use this example CGI program to write directly into Excel.
To call this program, you would need to have your Web server configured and running, and the end user would need to enter the following into the Web browser:
Replace mydomain.com with either your own domain name or the internal IP address of your iSeries.
While XLS and DIF are good choices, CSV seems to be the de facto standard for providing iSeries end users with the ability to process data with MS Excel.
Bob Cozzi is a programmer/consultant, writer/author, and software developer. His popular RPG xTools add-on subprocedure library for RPG IV is fast becoming a standard with RPG developers. His book The Modern RPG Language has been the most widely used RPG programming book for more than a decade. He, along with others, speaks at and produces the highly popular RPG World conference for RPG programmers.
Bob Cozzi is a programmer/consultant, writer/author, and software developer. His popular RPG xTools add-on subprocedure library for RPG IV is fast becoming a standard with RPG developers. His book The Modern RPG Language has been the most widely used RPG programming book for more than a decade. He, along with others, speaks at and produces the highly popular RPG World conference for RPG programmers.
MC Press books written by Robert Cozzi available now on the MC Press Bookstore.
 |
||
 |
|
RPG TnT Get this jam-packed resource of quick, easy-to-implement RPG tips! List Price $65.00 Now On Sale
|
|
||
 |
|
The Modern RPG IV Language Cozzi on everything RPG! What more could you want? List Price $99.95
Now On Sale
|
LATEST COMMENTS
MC Press Online