The Data Load Tool allows you to load data from an existing ProVal database into an Access database (*.mdb or *.accdb) for use with ProAdmin. The Data Load tool can also automatically create the ProAdmin Data Dictionary fields and Database Linkage, so you can immediately run calculations after setting up the plan rules

Name can be a phrase of any length, including spaces.

Database Linkage allows you to select an existing entry from the Database Linkage library. The linkage will be used to join the ProAdmin Data Dictionary fields with the ProVal data source file containing the member data. If you choose <Create Database Linkage>, ProAdmin will automatically generate a Database Linkage for you.

The Source File section of the dialog box (1) identifies the ProVal database file to be loaded, (2) sets the location of the ProVal client files, and (3) defines the Default Person Id in the ProVal Database file.

Type a file name in the box that identifies the source file for the data load. If no path is specified, the file is assumed to be in the client directory.  Alternatively, use the Browse… button to  navigate to a particular folder and file. If the folder is in the client directory, the source file name will be displayed without a path, or with a partial path as appropriate. 

Use the Client Directory… button to indicate where the ProVal client files are stored. The default selection is to Use the Source File location as the client directory. If the ProVal client files are stored in a different location, you can enter the path to the client files, or you can click on the Browse… button to search for them. 

Default Person ID Field specifies the field that contains a unique value for each person in the ProVal database. This field can be character or numeric, and is used to define keys in the Access database.

The Target Fields section of the dialog box identifies the fields from the ProVal database that will be loaded into the Access database specified by the Database Linkage selection.  Once the ProVal source field has been identified, you can click on the Map Fields... button to select the fields from the source file to be loaded.

Clicking on the Map Fields button opens up the Column Mapping dialog box. The left column lists the ProVal Database Fields contained in the ProVal database file.  These fields are not editable.  The purpose of this dialog box is to map the ProVal fields to ProAdmin Data Dictionary Fields displayed in the right column.  You may choose to <ignore> the field (i.e., the field will not be loaded into the Access database), <add to dictionary> to create a new field in the ProAdmin Data Dictionary, or use an existing ProAdmin Data Dictionary field for the data.  For your reference, the ProAdmin field type is included in braces to the right of any existing Data Dictionary field names.  After confirming the selection by clicking OK, the field mapping will be displayed in the Target Fields section of the main Data Load dialog box.

The Add/Omit button allows you to quickly modify the field selection by adding or omitting multiple fields at once.  There are three (3) columns presented:  a selection box, the ProAdmin Field and the ProVal Field.  Fields can be toggled individually, or the  buttons can be used to add all, delete all, or toggle on/off existing selections.

If any new ProAdmin Data Dictionary fields are to be added, the Field Attribs... button becomes accessible (unghosted).  Upon clicking the button, the Edit Field Attributes dialog box contains a list of the proposed new Data Dictionary fields by Field Name and Description.  Click on a field name to view or modify its definition.  The standard Data Dictionary field dialog box will be presented based on the Field type (which cannot be changed) of the selected field.  You can change the field name, description, formatting style, array type, and/or codes and labels (as appropriate).

Once target fields have been selected, additions to the ProAdmin Data Dictionary will be identified using brackets.  Similarly, any incomplete fields mapping (i.e., coded fields which have not been reconciled) will be identified using an asterisk.  You may preview and edit this mapping by clicking on the target field name, opening the Target Field Mapping dialog box. The Target Field Mapping dialog box has three (3) sections.  

The Target (ProAdmin Data Dictionary Field) section displays the Field name and Type of the ProAdmin Data Dictionary field.

The Source section allows you to define details such as the ProVal Field that will become the field Value, and  Start  Date, Stop Date and Effective Date information for array fields,  These array dates can each be specified either as a ProVal Field or as a specified Fixed Date.  In each case when a Fixed Date is indicated, there is an option for the date to be limited to no earlier (or later) than a specified ProAdmin field.  

The Source section also allows you to Reconcile Coded Labels if the target field has coded labels.  The Data Load - Coded field mapping dialog allows you to Match the coded field Value based on either Codes (exact matches only) or Labels.  If matching on labels between the ProVal and ProAdmin fields is selected, choose whether to Require exact matches (ignoring case) or Allow similar matches.  The third column of the Map ProVal Codes and Labels grid on the bottom of the dialog displays how the reconciliation selections above will be interpreted by the data load tool.  Changes in the match value selections above will automatically be reflected in this section.  Initially you can match by code or label, and then you can override the match by selecting the appropriate code/label.  When there is no match, it's assumed that you want to create a new ProAdmin code/label based on the ProVal code/label. Any new entry will be bracketed in the display.  The grid displays the ProVal Labels for the field, the ProVal Code for each label, and the resultant ProAdmin Label {Code} based on the choices on the top of the dialog.  

If something other than exact matches is selected, or if new values need to be added to the Data Dictionary for the coded field, the Edit Codes/Labels button becomes enabled allowing you to defined New ProAdmin codes & labels.  

The Current ProAdmin codes & labels section displays the Character Label and Code for existing entries in the Data Dictionary. Changes to existing entries cannot be entered on this dialog box, they are displayed for reference purposes only. This is useful as new entries made in the lower section of the dialog box must be unique to existing entries. 

The New ProAdmin codes & labels section displays new numeric codes (not stored in the Data Dictionary) and the character labels associated with them. You can modify the proposed entries for a new Character Label and Code but it must be unique among the new and existing ProAdmin codes. For example, if code 11 is used in the ProAdmin codes and labels, and you try to use it as a new code, you get an error message when you try to leave the dialog.

The ProVal Data Preview section of the dialog allows you to verify that your mappings are appropriate.  It displays the data that will be imported in the first few columns, followed by all the available fields.  RecID and PersonID are always shown first.  Data is shown for a selected Number of Records. which defaults to 100, or the number of records in the ProVal database if less.  

Once the source file location and field mapping tasks have been completed, click on the Load data… button to begin or review the load process. A series of dialog boxes will guide you through the three (3) steps of the data load process.

Load Data: Step 1 of 3 Preview Target Data allows you to review data, field by field, for a limited number of data load records from the ProVal database. No data changes can be made within this step, but it provides an opportunity to view reasonability of the data for several records within the source database. To preview data for a different field, select another Target Field entry. With a target field selected, the Target Data Preview section of the dialog box shows the data associated with the selected target field. This represents the data that will be written to Access. RecID is always shown, but won't be written to Access unless it's the Person ID. If the selected target field is an array field, additional data may be shown which will allows you to validate the effective, start, and stop dates. 

After you click Next, the Load Data: Step 2 of 3 dialog will be displayed.  This step 2 is designed to (1) provide instructions for record selection, (2) name the new database and database linkage (if applicable), and (3) define handling of duplicate records and errors. 

The first option is to enter a  Selection Expression for ProVal Database (blank means all records). The data load will use this logical expression to select a subset of the records from the ProVal database to be loaded into an Access database. If the text box is left blank, all records will be loaded.  

If you have chosen to <Create Database Linkage> as part of this Data Load, Step 2 of 3 is where you enter the path and file name for the MS Access Database to be created or updated by this load process. If you don't enter a path, then the ProAdmin client directory is assumed. You may also change the default name for the new database linkage in the Create new Database Linkage text box.

The Processing codes section defaults to what are intended to be the most useful choices for dealing with duplicate records and errors, but provides additional options. For example, if the MS Access database file already exists, the default processing selection is to Quit data load. Alternatively, you may choose to either Append to file, which will add the new records to the existing ProAdmin database, or Replace the existing ProAdmin database entirely with the new data (and possibly a new table/field structure).

For the If an error occurs during the Data Load, abort and parameter, the default entry is, Reset database. This selection will abort the load and reset the database back before any data was loaded. If you change the selection to the alternate option of Don't reset database and an error occurs during the data load, the load will be aborted, but the database will not be reset. This will leave the data loaded into the database up to the abort, which may be helpful for troubleshooting. 

For the If data already exists for an individual parameter, the default selection is to Abort the Data Load. Alternatively, you may choose to Skip the person with the problem (no data written for that person) or Skip the problem and continuing writing data for that person. 

You may indicate a maximum error limit for processing; the data load will Quit loading data after ___ errors.  If this field is left blank, the data load will continue to process, regardless of how many errors are encountered.  

For the If creating or replacing the database parameter, the default choice is to Ignore database relationships (e.g., Primary & Foreign Keys). If selected, the data load will not create primary or foreign keys. Alternatively, you may choose to Create database relationships, which will have the data load try to create primary or foreign keys.

After clicking Finish, the data load will be processed, generating the Data Load Results. These results will summarize the processing statistics, including elapsed time of processing and record counts. Processing messages as well as Warning and Error messages encountered will be listed. Print or Copy these results for future reference. When you close the processing statistics, you are given the opportunity to save these changes. If you save the changes, the Data Load entry is saved you will automatically exist the entry. At this point, the database is ready for use by ProAdmin