CRTCSVDTA - Create .csv data from iSeries files

Details

EDTFLDMAP, CRTCSVDTA & CRTCSVSTMF

These three commands should help you create .csv files from any [iSeries|i5|AS/400] file with a little more control than the likes of CPYTOIMPF. Not everyone has shiny new databases with all date & time fields explicitly defined. Dates are more usually held in packed or zoned numeric fields, and may well be in a non obvious format. Where I work most dates are held as a Lilian derivative, being the number of days since the beginning of 1978. It's very handy for calculating durations, but not so good for presentation. Also CPYTOIMPF doesn't trim trailing spaces in character fields, which some find annoying. CRTCSVDTA does trim trailing spaces, so your files are more compact and spreadsheet friendly.

Unless you have one-off programs for creating .csv files, these fields end up as just another number. 123 doesn't look much like a date until you realise it's the 23rd January 2000 in YYMMDD format - with suppressed zeros. I wanted a routine that was generic - just point it at a file and get .csv output (ready for CPYTOSTMF to dump to the IFS), and that let me define how fields should be formatted.

This has resulted in three commands: EDTFLDMAP lets you define how fields should be formatted, focusing mainly on date fields, and stores the choices on file. CRTCSVDTA uses the formatting rules (if specified) to generate a tailored .csv flat file. You don't need to use EDTFLDMAP if you don't need anything more than a regular .csv, but it's there for when you do Finally there is CRTCSVSTMF which is just a wrapper around CRTCSVDTA and CPYTOSTMF, which handles creating the temporary flat file that CRTCSVDTA requires. You'll have more flexibility in your programs if you use CRTCSVDTA and CPYTOSTMF seperately, but CRTCSVSTMF is handy for a one step conversion on the command line.

Limitations: This should have no problems with character, packed & zoned decimals, date, time or timestamp values. Apart from my testing on CRTCSVDTA I've never come across floating point fields on the AS/400, so I've had to make some assumptions based on what I seen in the manuals. I'm a bit more comfortable with binary values, but there could still be some bugs. I have no idea how to treat graphic or hex fields, so haven't catered for them - they will be silently dropped from the file. Varying length character fields are now supported, but *NULL values in fields will break the program. The only way I can get round this is to rewrite the conversion engine to use the C APIs to read the files directly, rather than the traditional RPG F specs and READ opcode. Don't expect this any time soon - though hopefully I'll get there eventually. The program is set up to handle a maximum output length of 5000 characters, a maximum character field size of 999, and no more than 999 fields. If your files exceed this you can get your hands dirty and tweak the code appropriately. If you can give me some pointers (or better still some code patches) on handling these field types please do so.

Update: Although I haven't got *NULL support integrated yet, I do have a work-around. CvtNulFld is a simple command to convert Null containing fields to a suitable non-null default (zero, blank, etc). Run it over a copy of your chosen file (as it converts the data in place, not to an *OUTFILE) and CRTCSVDTA will be quite happy

One other issue that's been brought to my attention is the use of # and @ symbols in field names. They don't translate well to other languages so it may be necessary to swap them for something more appropriate. I've now removed all # characters in this utility. On a similar note, you may use something other than a period (.) as the decimal point in your numbers. If that's the case, then change the value of DecSym in the first line of the ProcNbr subroutine to what's appropriate for your language. Thanks for the head's up on that one, Esa

Screenshots

EDTFLDMAP just takes a qualified file name as a parameter EDTFLDMAP FILE(*LIBL/DBG1860W) for the above example

This is the main screen, showing details of the fields in the chosen file. If you've used field selection in DBG links you should recognise it Just type a 2 to set the rules for each required field.

Field names with the Use rule flag (see below) set to Y(es) are highlighted.

---

This is the Rules window showing the details of the chosen field. The element of the window are as follows

• Use rules: Type Y(es) to have CRTCSVDTA use the rules set here, otherwise the field will be formatted with the defaults.
• Zero fill: The opposite of Zero suppress. The default action is to trim leading zeros like EDTCDE Z
• Negatives: Set the formatting of negative numbers. The default is *FLOAT - a floating minus sign eg,. -123. You can also have *MINUS for a trailing minus eg,. 123-, *CR for a trailing CR eg 123CR, *BRKTS for accountant style encompassing brackets eg,. (123), or select *NONE for no formatting at all.
• Field prefix: Specify a field prefix to be added to character fields. This is most useful for fields that contain leading zeros that would be treated as numbers and have the leading zeros stripped by spreadsheet applications. For Excel and OpenOffice.org, using an ' (apostrophe) as the prefix works well.
• Date/Time: Indicates the field is a date or time value. Use for Date, Time or numeric fields. If set to Y(es) then the export options are available.
• Internal: Define the format of the date or time value from one of *CYMD, *DMCY, *MDCY, *SYMD, *YMD, *DMY, *MDY, *CYM, *YM, *MY, *LILIAN, *HMS & *HM. Genuine date data type fields have an implied internal value of *CYMD, and time fields of *HMS.
• Export as: The same values for Internal are valid as Export formats. With the exception of the century value (see Window year below) dates and times can be converted to a less detailed format but not vice versa - you can't get to a *DMY from *YM. Likewise for *HMS from *HM. Note: *SYMD is the Synon (Cool:2E) 7 digit format with a single digit to represent the century.
• Edit date/time: If Y(es) (which it defaults to) then the chosen separator will be used to format the field, like EDTCDE Y. If not selected your value will appear as just a number, though it may have been swapped about by the export option.
• Separator: Use the appropriate symbol eg,. / or - for dates and : or . for times. You can use a blank if required.
• Window year: Use to determine the century for dates without it. Any year value less than the Window year is assumed to be 20nn, otherwise it's 19nn.
• Lilian 1: The start date for Lilian based dates. Genuine Lilian dates start from the beginning of the Gregorian calendar (15th October 1582, as I've now been told - thanks Mark :), but your start point may be different. Only relevant if you select *LILIAN as Internal or Export as.

---

The CRTCSVDTA command - Parameters

• FROMFILE - The file containing the data you want convert to .CSV format. It's used read only.
• TOFILE - The flat file with the .csv data generated by CRTCSVDTA.
• FROMMBR - The member in the FROMFILE to copy from.
• TOMBR - The member in the TOFILE to output to.
• MBROPT - Add or replace records in the output file. If add is chosen, then the output file must have been created by an earlier CRTCSVDTA.
• COLHDG - Include an initial title record in the file with the Field names, field text, field column headings, or omit title record
• FLDDLM - Field delimiter; defaults to a comma (obviously) but could be any other character
• RPLFLDDLM - Replace occurences of the FLDDLM character found embedded in character strings. Use to avoid punctuation in description fields (addresses, product text etc) from causing parsing errors when the .csv is read.
• STRDLM - String delimiter; defaults to double quotes. The character that is wrapped around string values. Not all applications require this, so could be left blank.
• RPLSTRDLM - Used as RPLFLDDLM to help prevent reading problems

---

The CRTCSVSTMF command - Parameters

• SOURCE - The file containing the data you want convert to .CSV format. It's used read only.
• MEMBER - The member in the SOURCE file to copy from.
• TARGET - The streamfile to create.
• COLHDG - Include an initial title record in the file with the Field names, field text, field column headings, or omit title record
• FLDDLM - Field delimiter; defaults to a comma (obviously) but could be any other character (e.g. Hex value for Tab)
• RPLFLDDLM - Replace occurences of the FLDDLM character found embedded in character strings. Use to avoid punctuation in description fields (addresses, product text etc) from causing parsing errors when the .csv is read.
• STRDLM - String delimiter; defaults to double quotes. The character that is wrapped around string values. Not all applications require this, so could be left blank.
• RPLSTRDLM - Used as RPLFLDDLM to help prevent reading problems. Leave blank if STRDLM is blank.
• STMFOPT - Add or replace records in the output streamfile. If *ADD is chosen, then the streamfile must already exist.
• STMFCODPAG - Streamfile code page. See CPYTOSTMF for more details.
• ENDLINFMT - End of line character. See CPYTOSTMF for more details. Use *CRLF for Windows systems, *LF for Unix/Linux/!*BSD sytems and *CR if destined for a Macintosh.

---

Using the one-step CRTCSVSTMF command - with a nice progress meter.

---

The final output from CRTCSVDTA. Notice the date in *CYMD format around column 100.

Download

crtcsvdta.zip (30Kb) - or get this and all my other utilities in a save file from the download page

This is packaged in Craig Rutledge's XMLGEN xml installer. See installation instructions for details. You must have already installed DBG/400 as they share a number of common files & programs