OSCAM Ship Data Import Export Tool 

The OSCAM Ship Data Import/Export Tool is an Excel workbook that allows data records to be extracted from an OSCAM Ship database, edited in Excel, and then exported back into an OSCAM Ship database. The tool contains built-in validation checks and formatting to show which inputs are enabled by switch settings. Each data record is represented by a separate worksheet. It is also possible to create new OSCAM data records from either a default data record or using an existing data record as a starting point.

The purpose of the tool is to facilitate the processing of source data for cost estimating and getting the processed input values into an OSCAM data record. Typically, cost estimators will use one or more separate spreadsheets to process and organize their data with appropriate audit trail, and then input data will be entered into a data record via the OSCAM user interface. The Data Import/Export Tool provides all of the facilities of an Excel workbook, as well as formatted worksheets that can represent an OSCAM data record and a mechanism to communicate with OSCAM databases to import data records from the database into worksheets, or export data held in worksheets into an OSCAM database. The tool provides a mechanism for processing of data, audit trail and the ability to directly extract from and write to an OSCAM database, reducing the need for manual data entry.

The import functionality allows an OSCAM database to be selected and shows the records contained in the database. One or more of the records can be selected for import. If any record worksheets exist in the workbook with the same name as a record to be imported then there are options are provided for dealing with the duplicate record names.

The export functionality allows selection of one or more record worksheets and export of those records to an OSCAM database. Options are provided for handling duplicate record names. Validation checks are made on all record worksheets and any that fail validation cannot be exported to an OSCAM database.

Ship Data Import/Export Tool data record worksheet

Data record worksheet showing custom ribbon toolbar and sector outlining    

The Ship Data Import/Export Tool uses a custom ribbon toolbar for any actions related to Import and Export and managing worksheets. The data record worksheets provide a structured representation of all inputs in each input sector. Use of outlining allows ease of navigation through the input sectors. Clear formatting highlights the cells for data entry and OSCAM options buttons are represented by drop-down lists, with appropriate changes to formatting to display the level of detail selected (e.g. Simplified versus Detailed options). OSCAM input long descriptions are captured as Excel comments.

Custom ribbon toolbar

Custom ribbon toolbar

Data validation is performed on data entry through standard Excel validation options. There is further validation that is checked at certain points in case standard validation is bypassed through pasting, data values set through formulae or where data is required to complete a set. This validation highlights the affected input name with a red background, and propogates the red background up through the outlining levels so that you can find the problem rows even if they are hidden by the outlining settings.

Validation check failure highlights problem row and propogates up through the structure

Record worksheet with valiadtion failure

The record worksheets allow standard copying and pasting of values although this can affect formatting and validation, but maintains flexibility that would be denied by restricting paste options. The tool has the ability to repair formatting and validation on record worksheets.

The Import/Export Tool uses a "Control" worksheet to track all record worksheets in the workbook and their validation status. The worksheet has a full set of command buttons that replicate the options on the custom ribbon toolbar. Many actions on the toolbar are also handled using standard Excel approaches, such as deleting, renaming or creating worksheets, and the tool ensures that any extra processing is performed (such as updating the "Control" worksheet) when these actions are done.

One challenge for the Import/Export Tool is to provide and protect structure, formatting, and validation for the record worksheets as well as maintaining flexibility required by the analyst for processing source data and getting processed data into the record worksheets. The record worksheets have many protected cells containing labeling, so copying and pasting large areas of values into the worksheets is not possible. To overcome these restrictions and provide flexibility the Import/Export Tool uses two mechanisms:

Standard Excel worksheets - standard worksheets can be created in the Import/Export Tool which provide full Excel functionality for data handling and processing.

Calculation worksheets - these worksheets have full Excel functionality, but add the mechanism to add any OSCAM inputs to the worksheet via a right-click cell menu option. Template cells are added to the worksheet for the selected inputs and the input names are recorded (in columns A and B). There is then a mechanism to transfer all or a sub-set of the inputs on the calculation worksheet into a selected record worksheet. This allows large amounts of input data to be copied to a record worksheet in one action.

A calculation worksheet allows OSCAM inputs to be added by right-clicking on a cell and selecting "OSCAM: Set as Calculation Output". Any other cells on the worksheet are free for adding source data or formulas.

Selecting option to add inputs to a calculation worksheet

Right-click cell menu on a calculation worksheet to add OSCAM inputs

A form displays all of the available OSCAM inputs and allows any of these to be selected for adding to the calculation worksheet. Inputs are presented in a tree view, reflecting the structure in a record worksheet, or via an interactive search. The form will prevent duplicate inputs being added to the worksheet. Some inputs consist of several rows, which will be kept together when the inputs are added. There are checks for enough blank cells to fit the input templates. Standard individual inputs usually require 3 cells, but some table inputs may require 2 or more rows and up to 101 columns. The form has options for dealing with space conflicts, with the default option being to add blank rows. Inputs can be added as blank cells or can be populated with data values from a record worksheet.

Selection of inputs to add to a calculation worksheet

Excel form to select inputs to add to a calculation worksheet

Inputs that are added to the calculation worksheet will keep their source formatting and validation, and will have any long descriptions captured as comments.

Calculation worksheet with inputs added

Populated calculation worksheet

Selected input data is transferred from a calculation sheet to a selected data record sheet via the Transfer Data toolbar button. This brings up a form that allows the calculation sheet, data record sheet and all or a subset of the inputs on the calculation sheet to have the data transferred. Transfers are done as values, even if formulae are used on the calculation worksheet to calculate the values. Inputs cannot be selected for transfer if they fail validation.  Each record worksheet contains a Tool Audit Reference column that captures the source of the input values in terms of loading from a data record, transfer from a calculation worksheet or direct user edits.

Transfer form for calculation data to a record worksheet

Transfer of calculation worksheet inputs to a record worksheet

It is anticipated that users who have common processing tasks will save copies of calculation sheets as templates so that they can re-use the structure and formulae they have created for other studies, and to share these worksheets with colleagues.

The Ship Data Import/Export Tool has an "Instructions" worksheet with a quick start guide and detailed instructions on the full functionality of the tool.

Instructions worksheet

Instructions worksheet with contents navigation collapsed