When viewing CSV data written by the Roos Instruments tester, the tests which pass and fail are not immediately obvious. The CSV (or comma-separated variable) data format captures the numerical values but loses all formatting information in a worksheet. There is an easy way to replace that formatting, however, when using a spreadsheet package such as Microsoft Excel or OpenOffice Calc. (OpenOffice is a free open-source office suite available for many platforms. See http://www.openoffice.org)
First shape the data
Data written from the "Details Worksheet" of the RI tester may store the tests in rows or columns, depending on which view was active at the time the file was written. Data files written from the "CSV Logfile" feature of the test executive, however, are always stored with the tests in columns and the devices in rows. If your testplan has more than 255 tests, it is important to rearrange the data so the tests are in rows instead of columns. Thre exists an OS/2 utility which can "transpose" a CSV data file which is documented here.
Here is the original Details Worksheet as displayed in the RI System Software:
When viewed with a text editor, you can see the structure of the CSV logfile produced by the tester. Each tested part is represented by one row of data and the tests are arranged in columns:
After processing with the utility SWAPCSV.CMD, the CSV data are transposed. Now the tests appear in rows and each part is represented by a column:
Process the CSV file with a spreadsheet
Excel and OpenOffice both offer a feature called "Conditional Formatting." We will use this feature to color the spreadsheet such that failed tests are shown in red and passed tests in black, just as on the Details Worksheet view of the RI System Software.
Begin by opening OpenOffice Calc or Excel (shown below) and reading the CSV file. If you used SWAPCSV.CMD you will need to rename the transposed file from "CSVT" to "CSV" as Excel won't recognize the file unless it has the CSV extension. After opening the file, all entries appear as a single color, black:
Select the first data item as shown above. To selectively color the data which represents failed tests, choose the menu "Format/Conditional Formatting..." When the dialog opens, fill it in as shown below. (Remember to click the "Format..." button and choose the red foreground color.)
Here is the function decomposed:
|=AND( )||The following boolean tests will be "AND"ed together|
|$D8<>$E8...||Tests with no limit (blank cells) will fail this test|
|,OR( )||The following two tests will be "OR"ed together and "AND"ed with the test above|
|F8>$D8||Data values greater than the upper limit will fail this test|
|F8<$E8||Data values less than the lower limit will fail this test|
After applying this conditional format to one cell, select the cell and copy it to the clipboard. Then select all of the data cells and choose "Edit/Paste special..." from the menu. Paste only the "Formats" as shown below, to leave the data values intact:
After this step is complete, the data format shows red numbers for failing tests just as on the RI System Software display. When you save this worksheet, save it in the native format of OpenOffice Calc or Excel. If you save it back as CSV, it will lose all of the formatting and revert to data only.