The Record Layouts command describes the format of text files. They are used when importing or exporting data between a text file and a ProVal database.

The types of Record format (or layout) available are:

• Fixed Width record layouts describe fixed format text files, in which a given field value is found in the same position in every record.

• Delimited record layouts describe text files, in which field values are separated by a designated character (for example, csv files, in which fields are typically separated by commas in the U.S.); select either the Comma, Semicolon, Tab, Space or Other option, as appropriate. If Other is selected, enter the special character (for example, an ampersand) that separates the fields.

• Excel record layouts describe Excel spreadsheet files, for purposes of importing data only; you cannot export directly to a spreadsheet file (instead export to a delimited file and resave as an Excel file).

If you switch formats for a record layout, ProVal will try to deduce reasonable parameters for the new format.

The fields that have been included in the record layout will appear in the box in the middle of the screen. Click the Field name to modify the parameters for the field in the record layout. To include additional fields, click the Add button. Either action leads to the Field Parameters dialog box, in which you provide information to describe the field’s representation in the text file.

To rearrange the fields in the record layout, click the Reorder button. This action leads to the Reorder dialog box, which contains a grid used for rearrangement.

Clients updated to ProVal version 3.05 and new clients contain the sample Record Layout “Import simulated full yield curves”, which can be used to import into a ProVal database, from an external file, data needed to run a Custom type of Capital Market simulation. See Simulated Full Yield Curves for more information.

 

Field Parameters dialog box

The name of the Field appears at the top of the box. The drop down list contains the names of all fields from the current project’s Data Dictionary that have not been included in this record layout. If you wish to include one of these fields, simply select it from the list. ProVal then displays the field Type immediately below the Field parameter. If you wish to include a field that is not in your Data Dictionary, click the New button to define it.

If you are editing a fixed width record layout, identify the columns in which a field appears. For each field, ProVal has pre-set the Starting Column to immediately follow the last column of the preceding field in the record layout; if that is not correct, then simply type in the correct starting column. If the field is not a date field, then enter either the Ending Column or the Field Width. ProVal will complete the other parameter for you. If the field is a date field, then you do not enter either of the latter two parameters, because the width of the field is determined by the date format.

If you are editing a delimited record layout, identify the Position of each field within the record. For each field, ProVal has pre-set the Position to immediately follow that of the preceding field in the record layout. If that is not the case, then enter the correct position.

Regardless of record format, any field you add to an existing record layout immediately follows the last existing field, unless you change its location in the record layout.

The remaining parameters on the screen will be available only if they pertain to fields of the type being added.

Numeric fields can, optionally, use a scaling factor. Values being imported will be divided by the scaling factor; values being exported will be multiplied by the scaling factor. For example, if there is a scaling factor of 100, then the number 123456 will be imported as 1234.56. If you wish to leave the numbers unchanged when they are imported or exported, simply leave this parameter blank. This parameter also affects the number of decimal places for exported values. If a scaling factor has been entered, then the exported value will have no decimal places, regardless of the actual scaling factor value. Thus if the scaling factor text field contains, for example, the value “2”, then 1234.56 will be exported as 2469. If there is no scaling factor, then the number of decimal places is determined by the field’s formatting style in the Data Dictionary.

For coded fields, complete the Mapping of File Values and Labels grid. This establishes a mapping between the way fields are represented in the import/export file and the display of their codes within the ProVal database. The Label column will initially contain each possible entry for display of the field from the Data Dictionary. In the File Value column, enter the characters that represent that code in the import/export file. It is possible to have several different representations of a single code value in your import/export file. In this case, you would enter each File Value code in a separate row of the grid but enter the same Label for the code in each row. The grid can be expanded (by pressing the Enter key or the “down” arrow key when the cursor is in the bottom row) to include as many rows as you need.

The Match case option determines whether ProVal should, for example, treat “a” differently from “A” when these characters are encountered in the “in file” codes. For case-sensitive treatment, check the box.

The Date format parameter pertains only to date fields. Pick from the drop down list the manner in which this date field is represented in your text file. If the date format shows years with only two digits, then additional parameters are required to specify the century. Three choices are available. The first choice interprets all dates as being in the 1900’s. The second and third choices interpret some dates as being in the 1800’s or 2000’s, respectively.

The OK and Cancel buttons function in their normal manner. The Erase button removes the field from the record layout. It does not delete the field from the Data Dictionary or from anywhere else in ProVal.

 

Reorder dialog box

For a fixed width record layout, the grid displays the Order, Field name, Width, Start column and End column of each field. The grid also identifies, by denoting as “<gap>”, any columns not associated with a field in the record. The order of a field can be changed by entering the desired order number in the Order column, to the left of the Field name. The field is then reinserted in the new position; the field that previously occupied that position and the fields that occupied positions that follow on the record are shifted downward in the grid. The Start and End columns of the affected fields are recalculated based on the new order and the existing width of each field. The width of a field can be changed by entering the desired width in its column, to the right of the Field name. The End column for that field, as well as the Start and End columns of fields that follow on the record, are recalculated based on the new width. A field can be deleted by selecting it and either pressing Ctl+Delete or clicking the right mouse key and selecting the Delete Row command.

For a (comma) delimited record layout, the grid displays only the Order of the field and the Field name. The order can be changed and fields can be deleted in the same manner as for a fixed width layout.