Documentation for Format Migration Test File Lotus 123, Release 2.2
1. Overview
A test file was created in Lotus 123, Release 2.2, as a tool for determining which file characteristics are maintained when spreadsheet data files are converted (migrated) from one file format to another. The test file can be used to assess the following:
- whether numeric values migrate properly, without loss of precision,
- whether characters and text cells migrate properly,
- whether worksheet characteristics such as column width are preserved after migration,
- whether cell formats (e.g., text, numeric, date, time) are maintained, and
- whether cell functions (@functions) can be successfully transferred from one file format to another.
The test file allows the assessment of all but one of the 92 cell functions (@functions) available in Lotus 123, Release 2.2. (The @CLEAN function is excluded because it relies on input from software other than Lotus 123.)
Before undertaking these test procedures, you should have at least a fundamental understanding of spreadsheet functions and an ability to check basic file characteristics such as column width without specific instructions.
2. Organization of the Test File
The test file, Testfile.wk1, is a single worksheet with 23 parts or test procedures, each of which corresponds to a particular set of file features or functions. The 23 parts are as follows:
- layout
- column width
- maximum number of rows and columns
- justification
- maximum length of text in a single cell
- defined cell ranges
- minimum and maximum expressible values
- preservation of significant digits
- special formats (e.g., date, currency)
- arithmetic operations
- statistical @functions
- financial @functions
- calendar @functions
- logical @functions
- special @functions (excluding @cell [“filename”] and @cellpointer)
- the @cellpointer function (excluding @cellpointer [“filename”])
- the @cell (“filename”) and @cellpointer (“filename”) functions
- text @functions
- general math @functions (excluding @rand)
- the @rand function
- trigonometric @functions
- database @functions
- character set
Be careful not to insert or delete any columns or rows in the spreadsheet because several of the spreadsheet functions rely upon absolute cell references, i.e., the existence of particular values in particular cells.
3. Evaluation Procedures
The source format is the file format in which the original file (the test file) was created: .wk1 format (Lotus 123, Release 2.2). The source file, in this case, is the original test file.
The target format can be any spreadsheet format other than the source format. The target file is the file created by converting the test file (.wk1 format) into some other spreadsheet format.
To test the accuracy of migration from the source format to the target format, first convert the test file into the target format. This can be done with any spreadsheet program that accepts .wk1 files as input or with a stand-alone file conversion program such as DataViz. If you are using a spreadsheet program to convert the files, you will need to open (import) Testfile.wk1 within the spreadsheet program and save it as a new file in the target format. If you are using a standalone conversion program, follow the instructions provided with the software.
All but one of the test procedures in Testfile.wk1 can be completed without reference to the source file. That is, the target file alone, loaded into the target spreadsheet program, can be used to evaluate the effectiveness of the file migration process. (Unlike the other procedures, “character set” requires a visual inspection of source and target files.)
Layout, Formats, and Related Procedures
The first nine test procedures, from “layout” to “special formats,” are used to determine whether particular worksheet and cell formats are preserved in the target file. In most cases, you can simply check whether the characteristics of the target file match the criteria stated in section 4 of this document, (e.g., whether column N is actually 34 characters wide, as stated, or whether the column widths established in the test file were lost during file format migration).
Arithmetic and @Function Procedures
The 13 procedures from “arithmetic operations” to “database @functions” evaluate particular arithmetic operations and cell functions. For each operation or function, Testfile.wk1 presents
- an expression: a string of text showing the syntax of the function statement as it appears in (2) of this list;
- a computed result: an arithmetic or @function statement that corresponds to (1) and produces a particular numeric result;
- a correct result: the expected result of the function; and
- a set of numeric values, usually found in column H and subsequent columns, that are used as input for the function statement.
If the function works properly after file format migration, then the computed result (2) will match the correct result (3).
The transferability of any @function can, therefore, be evaluated through a simple comparison of the correct and computed results for that function. If the correct and computed values match, then the function is transferable from the source format to the target format. If the correct and computed results do not match, then the function did not migrate properly.
Additional evaluative procedures are required in just a few cases. These are noted in section 4 of this appendix.
Note: The testing procedure used in Testfile.wk1 relies on the assumption that the numeric values used as input (4) and the characters used in the display of the correct result (3) both transfer without error from one file format to another. This assumption is reasonable, since only the standard alphanumeric characters have been used in cells that contain input values or correct results.
Procedure for Comparing Character Sets
The last procedure, “character set,” requires a visual comparison of the source and target files. See section 4 for details.
4. Notes on Particular Evaluation Procedures
Layout
The header and margin settings shown in the Print-Printer-Options menu should match those described here. (See Testfile.wk1 for details.)
Column Width
Ideally, the column widths will match those shown here. (See Testfile.wk1 for details.)
It is possible that the target program will not use the same units to indicate column width as does the source format. (Lotus 123, Release 2.2, states widths in characters; other programs may not.) In that case, the relative width of each column should be the same as in the source file. That is, columns A and O should be 2/16 as wide as a standard column. Columns B and C should be 22/16 times as wide as a standard column, and Columns N and P should be 34/16 times as wide as a standard column.
Maximum Number of Rows and Columns
There should be at least 8,192 rows and 256 columns in the target file. (See cells A8192 and IV1.)
Justification
The word “left” should be left justified within the cell. The word “center” should be center justified. The word “right” should be right justified.
Maximum Length of Text in a Single Cell
The maximum length of text allowed in Lotus 123, Release 2.2, is a specified number of characters (240), not a specified width. The target file format should meet or exceed this length limit.
Specifically, these two cells should each start with “We the People” and end with “Liberty to ourselves and ou”. (The last letter in “our” exceeds the length limit.)
Defined Cell Ranges
To test whether defined cell ranges are preserved in the target file, change one or more of the values in cells B44, C44, and D44. The sum, average, and count values should immediately change to reflect these modifications.
Minimum and Maximum Expressible Values
Expressible values (numeric values valid for display and computation) are shown in the source file as numbers, either in standard format or in scientific notation. Inexpressible values are denoted by a series of asterisks.
The maximum expressible value (column B) should be no less than 1.0E+99. That is, expressible values should appear in column B from row 59 through (at least) row 158.
The minimum expressible value (column E) should be no more than 1.0E99. That is, expressible values should appear in column E from row 59 through (at least) row 158.
In the source file, all rows up to and including the marked rows (1.0E+99 and 1.0E99) contain expressible values. Subsequent rows contain inexpressible values. If any rows below the marked rows contain expressible values in the target file, this indicates that the target file format allows the use of values more extreme than those allowed by the source file format.
Preservation of Significant Digits
The degree of precision available in the source file should be maintained in the target file. Specifically, the numeric values shown in cells N167 through N182 should match the values shown in cells P167 through P182. (These values are presented in columns N and P because columns A through M are not wide enough to display them in their entirety.)
Special Formats (e.g., Date, Currency)
The value in each formatted cell (the “formatted value” column) is 2846.3912. The formatted cells differ only in format, not in content.
If the target file supports the same formats as the source file, then each formatted value (column C) should look the same as the corresponding text cell (column B) does.
Arithmetic Operations
Each computed result (column C) should match the correct result shown in column B.
Statistical @Functions
Each computed result (column C) should match the correct result shown in column B.
Financial @Functions
Each computed result (column C) should match the correct result shown in column B.
Calendar @Functions
For all but the @NOW function, each computed result (column C) should match the correct result shown in column B.
The @NOW function shows the last date and time that any cell was entered or recalculated, not necessarily the current date and time.
If the target software recalculates all values continuously, then the @NOW function will display the current date and time. In most cases, however, it will be necessary to enter or recalculate a value (any value in the spreadsheet) in order to display the current date and time.
To recalculate all the values in the test file and thereby display the current date and time, press F9 in Lotus 123. (Other software programs may use a different key or combination of keys.)
Logical @Functions
Each computed result (column C) should match the correct result shown in column B.
Special @Functions: Excluding @CELL (“filename”) and @CELLPOINTER
Each computed result (column C) should match the correct result shown in column B.
The @CELLPOINTER Function: Excluding @CELLPOINTER (“filename”)
The @CELLPOINTER function shows the characteristics of the currently active cell (i.e., the cell with the cursor). If this function is working correctly, then the computed result in column C should match the correct result in column B when
- the cursor is placed in column F of the same row as the function statement, and
- the worksheet is recalculated. (In Lotus 1-2-3, press F9 to recalculate the worksheet. Other software programs may use a different key or combination of keys.)
To get the proper computed result in cell C437, for example, place the cursor on cell F437 and press F9.
After checking that the computed result in C437 matches the correct result in B437, follow this same procedure for each of the other rows in this section. That is, put the cursor on cell F438, press F9, and check that the computed result in C438 matches the correct result in B438. Then proceed to cell F439, press F9, and so on.
The @CELL(“filename”) AND @CELLPOINTER(“filename”) Functions
Cells B481 and B482 should each show the complete name of the target file, with the path from the hard drive to the file.
This test procedure does not require a comparison of correct and computed results.
Text @Functions
Each computed result (column C) should match the correct result shown in column B.
General Math @Functions: Excluding @RAND
Each computed result (column C) should match the correct result shown in column B.
The @RAND function
To test this function, recalculate the values repeatedly. (Press F9 to recalculate in Lotus 123. Other software programs may use a different key or combination of keys.)
The computed values should change each time but should always be approximately equal to 0.50 (the mean) and 0.29 (the standard deviation).
Individual deviations from these expected values are not a cause for concern as long as the values usually approximate 0.50 and 0.29 after each recalculation.
Trigonometric @Functions
Each computed result (column C) should match the correct result shown in column B.
Database @Functions
Each computed result (column C) should match the correct result shown in column B.
This section can be used to evaluate those database @functions available through the spreadsheet itself. Functions that rely upon input and output forms are not included in the test file, since those functions are generally used as tools for the construction of data files rather than as carriers of data.
Character Set
This section requires a visual comparison of the source and target files to ensure that each character in the source file is accurately represented in the target file.
The character set will show up properly in the target file only if
- the target file uses the same character set as the source file, and
- the @CHAR function works in the target file format.
The characters are listed here by LICS (Lotus International Character Set) number. Each character appears above the corresponding LICS code. It is important to realize that some target formats may support all the characters shown here without relying on the same character numbering scheme. In that case, the character set may migrate successfully even though the original and target files do not match precisely.
