DataExport is ready for V6R1/V7R1 of i5/OS, starting with version 1.36
1.44.0
2013-12-18
The minimum OS/400 compatibility is now V6R1
1.43
2013-11-13
Updated SLK handler to correctly handle generating files with no rows.
1.42
2011-11-02
Fixed problem that the DBTODELIM command ALWAYS did a *REPLACE, no matter what user entered. *ADD only works properly with *CSV and *TBL.
1.40
2010-08-23
Removed prompt control for optional parameters in DBSQLIFS command. All parms now show.
1.39
2010-08-07
When exporting to XLS with DBTODELIM, we now make sure the output XLS file gets fully closed after it is created. This was preventing copying of Excel files after creation.
1.38
2010-07-31
Updated DBFD command to calculate record length correctly on temp files. This should prevent field text and column heading info from getting truncated when exporting files with field text or column heading info via the DBEXPORT or DBSQLIFS commands. DBFD should never be called standalone.
Added DBLABEL command to allow users to set the field text and column heading 1, 2 and 3 info from the matching field names within another table.
Added LABELFROM parameter to the DBSQLIFS command to allow the extracted SQL temp file to have its field label values set so that column heading and field text can be exported correctly. Field labels do not normally get set when exporting to a physical file via SQL. This parameter will only update fields from a single field reference file so the basic assumption is that the SQL SELECT on the DBSQLIFS command is using a single table.
1.37
2009-02-20
Updated DBEXPXLS2 command to use the user ID field passed in the USERID parm instead of the current job user. This was causing user security problems when exporting a file to XLS format.
1.36
2007-05-14
Fixed CPYTODBF so that accented characters and several others are converted correctly. Most-used characters that were affected are !, ?, |, [, and ]. Object affected is module CPYTODBFR.
1.35
2006-02-22
Updated DBEXPXLS2 to have a switch to not delete STDOUT files from QTEMP for debug purposes when Java failures occur.
1.34
2006-02-08
Changed CPYTOSLK1 so that numbers in text fields will be handled as text. This was done by setting column formats in the SYLK file.
- All character fields are set to text
- Floating point fields and packed & zoned fields with decimals have custom formats with the number of decimals
- Binary fields and packed & and zoned fields without decimals are left alone
- Date fields are formatted as mm/dd/yyyy
- Time fields are formatted as hh:mm:ss
- Timestamp fields are formatted as mm/dd/yyyy hh:mm:ss.000
Column widths are set along with custom formats. The width is based on the possible display length of the data and field name. Picture formats are added to accomodate the numeric and date/time data types. Excel serial values are used for date/time fields.
Added processing for 8-byte integers. Variable-length field processing is fixed so that the 2-byte length is no longer included. This is also fixed in CPYTOHTM1, CPYTOXLS1, and CPYTOXML1.
1.33
2005-09-26
Activated DBF support. The conversion is to dBase III format, which has limits of 128 fields, maximum record length of 4000 (including a deleted flag), 1 billion records, 254 characters maximum for character fields, and 19 characters maximum for numeric fields, including sign and decimal point. Date fields are always 8 characters.
The physical file to be converted must fall within the following limits, which reflect the dBase limits:
- At least 1 and at most 1 billion records
Data types supported are:
- Character (fixed and varying)
- Packed
- Zoned
- Date
- Timestamp (date portion only)
- Maximum lengths
- Character => 254 characters
- Packed & zoned
- With decimals => 17 digits
- Without decimals => 18 digits
- Maximum calculated record length => 3999 bytes
- Maximum number of fields => 128
- No NULL-capable fields
Character fields will be the same size in the dBase file. Numeric fields will be 2 longer than the number of digits for fields with decimals, 1 longer for those without decimals.
1.32
2005-09-16
ADDED DBEXPXLS2 command to replace DBEXPXLS. DBEXPXLS2 allows users to select to include or not include field text and column headings when converting to Excel format. If user has been using DBEXPXLS, they must switch to DBEXPXLS2 and run the following two iSeries commands before using: This gets rid of the old java class file.
erase '/rjsjava/rjsexport/exportdbtoxls1.class'
This restores the new java class file.
rjsexport/dbjavaenv
1.31
2005-05-30
Implemented new DBEXPXLS command to export database files to Excel XLS format based on an SQL statement. This command calls a java program to do the read of the iseries database and writes the results to an Excel spreadsheet. Requires Java JDK version 1.4
Implemented new VFYJDK command to verify if the Java JDK 1.4 is loaded on the iSeries.
Added the CHKOBJIFS command to the library.
1.30
2005-03-19
Fixed DBSQL/DBSQLIFS so that statements longer than 550 characters do not fail. Added SQL syntax checking. Added SQL naming convention, sort sequence, and national language handling.
1.29
2005-03-07
Updated SLK format to correctly set values when the PAIRQUOTE parameter is used and there happens to be leading spaces in the data. Updated CSV format to correctly set values when the PAIRQUOTE parameter is used and there happens to be leading spaces in the data.
1.28
2005-02-17
Updated SLK format to accurately reflect record counts in the SLK file header so if a user hits Ctrl-End in Excel, the cursor goes to last record of data instead of going to record 65535.
1.27
2005-02-01
Fixed XLS output so that blank columns end up having empty cells.
1.26
2005-02-01
If PAIRQUOTES parm is set to *YES for SLK output, we will pair up any semicolons found in the data. Semicolons cause errors opening SLK files with MS Excel if they are not paired up. This should resolve any issues with opening SLK files.
1.25
2005-01-24
Updated DBTODELIM output formats to improve performance. Formats updated: SLK, HTM, XML. Added character to all HTML cells whenoutputting to HTML. This will insure that data cells are outlined correctly when no data is found for the cell. Resolved SLK output problem. Was not outputting all double quote characters around text fields. This was causing errors when opening the SLK files with MS Excel. This error must have been inadvertently introduced when testing performance improvements in late 2004.
1.24
2005-01-20
Added PAIRQUOTE parm to pair up double quotes when CSV files are created. Also added parm ADDSINGLE to add single quotes to front of text fields. Cmds changed: DBTODELIM, DBSQLIFS
1.23
2004-11-23
Updated XLS format to handle date, time and timestamp fields correctly. Field values were being set equal to the last text value that was being put out instead of the correct date/time/timestamp field contents. Also tested VARCHAR field to see if field length bit was coming over in VARCHAR text fields left justified at the begin of data. Code seemed to be working as expected.
1.22
2004-11-08
Initial implementation of XLS output type in DBTODELIM and DBSQLIFS commands.
NOTE: Minimum OS/400 release is V5R1.
1.21
2004-08-15
Created data area DBRECLEN to set default work file record length when extracting data. Some users were using *FLDTEXT and extracting column headings where the data was longer than the physical file record length, thus cutting off the headings when using *FLDTEXT as the heading option.
1.20
2004-06-15
Added replace from/to parameters to replace characters that are not valid when parsing a CSV file. The most notable of character replacements would be replacing a double quote character (") with a single quote (') to keep MS Word or Excel from parsing a delimited record incorrectly.
1.19
2003-05-07
Added TRIMSPACE parm to DBTODELIM command to allow leading spaces and negative signs to not be output when the *TBL format is selected. This was added by customer special request because they had an odd formatting requirement.
Added TRIMSPACE parm to DBSQLIFS command to allow leading spaces and negative signs to not be output when the *TBL format is selected. This was added by customer special request because they had an odd formatting requirement.
1.18
2003-02-27
Date, time, timestamp fields are now interpreted as character fields.
1.17
2003-02-15
Added *FLDTEXT, *FLDNAME, *COLHDG1, *COLHDG2, *COLHDG3 support for the SYLK output format column headings.
1.16
2003-02-12
Added SYLK output format.
Added BETA code for dbase output. ** Note ** DO NOT USE dbase output yet. Numeric field lengths truncate and negative signs are lost.
1.15
2002-09-18
Added zero suppress option to allow users to pad all numerics to their entire length if required. By default this setting will suppress all zeros as it always has.
1.14
2002-09-01
Added help panel groups to DBSQL, DBTODELIM, and DBSQLIFS
1.13
2002-07-23
Added DBUPG command to upgrade settings to new library if installed
1.12
2002-01-28
Changed QLISTLEN default value to 88.
1.11
2001-09-20
Hard coded DBFFDTMP location to be searched for always in library QTEMP. Since this is a temp file, hard coding this to QTEMP is appropriate.
1.10
2001-07-27
Added CHGAUT to DBTODELIM command to set ifs output file authority after a conversion.
Added CHGAUT to DBSQLIFS command to set ifs output file authority after a conversion.
1.09
2001-07-25
Create DBSQL query command to run an SQL statement and export the data to a display, printer or AS/400 OUTFILE.
1.08
2001-06-12
Fixed DBTODELIM command to now create a minimum file size of 1024 bytes for the output file. Files with small record lengths had the potential of getting truncated during conversion because we are dynamically sizing the work record lengths to maximize the file conversion performance.
1.07
2001-05-25
Fixed delimited output to not put blank spaces between delimiters on CSV output. This issue was introduced in V1.03 due to the fix.
1.06
2001-05-25
Added switch to output last record delimiter on the DBTODELIM command. This includes dbstrip1 program creation.
Changed TTLBGCOLOR to TTLFGCOLOR for commands DBTODELIM, DBSQLIFS and CPYTOHTML. Now the parameter name is correct.
1.05
2001-05-19
Added LPAR access code checking option.
1.04
2001-04-20
Updated DB001C program. This version has new work file record logic which won't create an 8196 char record unless needed. The work record will be file record length * 2. This should fix the issue of work files getting very large when they shouldn't.
1.03
2001-04-15
Fixed potential problem when outputting CSV files. If a field is 0 bytes and we're not outputting quotes around the field, we make sure to output a blank to make sure the comma or whatever delimiter gets correctly written out program changed: CPYTODEL1 - RISC/CISC.
1.02
2001-02-17
Added XML and html output types.
Note: XML and html can currently only be used with V4R2 and above. Field text, name, COLHDG 1,2,3 can now be used for CSV headings.
Created RJSEXPORT main menu.
1.01
2000-08-29
Create DBSQLIFS command to run an SQL statement and export the data to an IFS file.
