Data Load Tool
The Data Load Tool allows you to load data from an existing ProVal database or Excel workbook into an Access database (*.mdb or *.accdb) for use with ProAdmin. The Data Load tool can also automatically create ProAdmin Data Dictionary fields and a Database Linkage, so you can immediately run calculations after setting up the plan rules. Finally, you can use the Data Load Tool to update the existing data in an Access database. For example, you might want to update your data with data corrections (e.g., DOB changed from 2/3/1997 to 3/2/1997).
Name can be a phrase of any length, including spaces, but must be unique among your saved Data Loads.
The Source File section of the dialog box allows you to pick the ProVal or Excel database file to be loaded, identify the source field that contains the Person ID, and use a selection expression to restrict the source data you want to load. If you select a ProVal database, the Data Load Tool sets the location of the ProVal client files and defines the Default Person Id using the data in the ProVal Database file. If you select an Excel workbook, you must pick a Default Person ID from the list of the fields (i.e., column headings) in the workbook.
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 current 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.
If the source file is a ProVal database file, the Client Directory... button is available. 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.
The Default Person ID Field specifies the field that contains a unique value for each person in the ProVal database or Excel worksheet. This field can be character or numeric, and is used to define keys in the Access database.
If the source file is Excel, Worksheet is available to select the worksheet from the workbook that contains the data to be loaded. ProAdmin will choose a default worksheet for convenience, but it can be changed if desired. The worksheet names in the workbook are displayed with a trailing "$" in the name. All data must be loaded from a single worksheet.
The Selection Expression allows you use the fields in the source database to restrict what data will be loaded into the database. For example, if the field SSN is a numeric field in the ProVal database, then you could use SSN > 0 to only load records whose SSN is a positive integer; if SSN is character, then you could use 0 < LEN(SSN) to only load records with an SSN. Similarly, if the Excel worksheet had a column (field) named Status that contained the labels Active and Rehire (among other status labels) then you could use Status #IN ('Active', 'Rehire') to limit the types of records loaded. If you leave the selection expression blank, all data in the data source will be used.
Database Linkage allows you to select an existing entry from the Database Linkage library or have the Data Load create a new Database Linkage when the data is loaded. The linkage will be used to join the ProAdmin Data Dictionary fields with the ProVal or Excel data source file containing the data. If you choose an existing Database Linkage, the name of the Access Database file referenced by the linkage will be displayed in a null field below the Linkage name. If you choose <Create Database Linkage>, text fields will become available to specify Name of new Database Linkage and Access Database. ProAdmin will automatically generate a proposed name for the new Database Linkage, but it can be changed. You will need to supply a names for the Access database to hold the loaded data.
The Target Fields section of the dialog box identifies the source database fields that will be loaded into the Access database. There are six (6) columns of data, any one of which can be used to sort the information. This section is also expandable by clicking on the triangle to the left of the "Target Fields" title. The first column has an asterisk (*) if a field is not fully defined, or is blank otherwise. The second column has an A if the field needs to be added to the ProAdmin Data Dictionary, or is blank otherwise. The ProAdmin Field column contains the name of the ProAdmin Data Dictionary field into which the data will be loaded. The ProAdmin Type column identifies the field structure: date, numeric, coded, or character, including, where applicable, if the field is an effective date array (eff arr) or a start/stop array (s/s arr). The Source Field column contains the name of the field (or column if Excel) holding the source data, and the Source Type column indicates the type of data in the source file: date, numeric, coded or character.
Once a Source File has been selected and a Default Person ID field has been identified, you can click on the Map Fields... button to select which fields from the source file are to be used and indicate which ProAdmin Data Dictionary fields will hold the source data. Clicking on the Map Fields button opens up the Column Mappings dialog box. The left column lists the Source Fields contained in the ProVal database or Excel Workbook file. These fields are not editable. The purpose of this dialog box is to map the source fields to ProAdmin (Target) Data Dictionary Fields displayed in the right column. You may choose to <ignore> the source 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 (by default using the name of the source field), 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.
Once target fields have been selected, additions to the ProAdmin Data Dictionary will be identified by an A in the second column of the Target Fields section and any incomplete fields mapping (i.e., coded fields which have not been reconciled) will be identified using an asterisk in column 1. You may view the mapping and edit it by double clicking on the target field name or highlighting the name and clicking on the Edit Field... button. , opening the Edit a Target Field dialog box (see details below).
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 Source Field. Fields can be toggled individually, or the buttons can be used to add all, delete all, or toggle on/off existing selections.
Clicking on the Preview Fields... button opens up the Preview Target Data dialog, which allows you to review the data to be loaded, field by field, for a limited number of data load records from the source database. No data changes can be made within this step, but it provides an opportunity to view the reasonability of the "loaded" data for several records within the source database. The left side of the dialog shows the Target Fields and is sortable, listing the ProAdmin Field names and their corresponding ProVal Field or Excel Column. The rights side of the dialog is the Target Data Preview, showing the data to be loaded for the selected field. To preview data for a field, select it's name on the left and the date on the right will change accordingly. The data shown represents the data that will be written to Access. For ProVal datga, RecID is always shown first, 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 generated effective, start, and stop dates.
Once target fields have been mapped (using the Map Fields button) clicking on the target field name (or the Edit Field button), opens the Edit a Target Field dialog box:
The Target (ProAdmin Data Dictionary field) section displays the Field name and Type of the ProAdmin Data Dictionary field.
If you are adding this field to the Data Dictionary, you'll see [Add to Data Dictionary] after the Field name, and the Field Attributes button will be available. Clicking on the Field Attributes button allows you to change the ProAdmin Data Dictionary field name, description, field type, formatting style, array type, and/or codes and labels (as appropriate). The new field will have to be added to the Data Dictionary before any data can be loaded. You can wait until later to do this, or it can be done from this dialog by clicking on the Add to data dictionary now button.
The Source section allows you to define details such as which source field contains the field Value, and Start Date, Stop Date and Effective Date information for array fields, These array dates can each be specified either as an Input Field (i.e., a source 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 (that is being loaded in this Data Load).
The Source section also allows you to Reconcile Coded Labels if the ProAdmin Data Dictionary field is a coded field. If you click on the Reconcile Coded Labels button, the Coded field mapping dialog allows you to Match the coded field Value based on either Codes (exact matches only) or Labels. If matching is based on labels is selected, choose whether to Require exact matches (ignoring case) or Allow similar matches. The last column of the Map database codes and labels to ProAdmin 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 source code/label. Any new entry will be bracketed in the display. The grid displays the Source Values for the field, the Source Code (only if the source field is numeric or a ProVal coded field) 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 define 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.
If you need to add completely new codes and labels, you either need to go back to the Field Attributes button on the Edit a Target Field dialog (only available if adding a new ProAdmin Data Dictionary field) or edit the field in the Data Dictionary.
The Source 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 (only if ProVal) 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 source database if less.
The Processing Parameters section allows you to control the data loading process. There are several options for the conditions under which the process will abort or errors will be generated, and at what point the entire process should be aborted because of the number of errors.
If the Access database file already exists, you can choose to:
If an error occurs during the Data Load, abort and allows you to either:
If data already exists for an individual, you can choose to:
Note that if you choose to replace the existing datain the database, it is considered updating the data existing in the database. If there is no existing data for someone, the new data is loaded into the database (using INSERT INTO).
If creating or replacing the database, you may either
You may Quit loading data after xx errors. If this field is left blank, the data load will continue to process regardless of how many errors are encountered.
If you check the box to Track data changes in the Access database, any changes to the data in the Access database made using the Data Load Tool (i.e., from loading or updating data using the Data Load Tool) will be tracked via the ProAdmin created table [ztblTrackDataChanges]. Details about the potential contents of this table are available here.
Once all the target fields have been mapped and there are no errors, the Load data button (at the top) becomes active (unghosted) and you can load the data. When you click the Load data button, a progress dialog pops up and indicates how many errors have occurred and how many records have been processed. When the data load completes, the data load results will summarized, including various processing statistics, elapsed time, 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 the changes to the database and if applicable, Database Linkage and/or Data Dictionary. If you save the changes, (creating a new Database Linkage, if appropriate; adding any new fields to the Data Dictionary, if appropriate), the Data Load entry is saved you will automatically exit the entry. At this point, the database is ready for use by ProAdmin