Open topic with navigation

Harris Import Utility

This utility is launched by navigating to X:\Inetpub\WWWRoot\Mill\Tools\Harris.exe using the Microsoft Windows Explorer, and double-clicking that file. It executes in its own window - not from within the standard interface.

Many institutions contract with the Harris Publishing Company to produce an alumni or constituent directory. Typically, sites who do so will create an export table of data to send to Harris, according Harris' requirements. This may be done using the standard capabilities of the Millennium Reporter to create an appropriate export file, or any other database tools that will perform the task appropriately. Harris staff then uses that data to generate mail or telephone questionnaires to gather updated information from the constituency. This information is returned to the institution from Harris in a text file. At this point, the Harris Import Utility within Abila Millennium is used to update or create Abila Millennium rows based on the information in the text file that is returned from Harris. Harris Publishing will deliver this file in a format that is appropriate for use by this utility, but only if you communicate that request to them and provide them with your specifications.

You may choose from three optional behaviors in the way that the utility will import data into Abila Millennium rows:

• It can be set to overwrite existing Abila Millennium data with the data that is imported.
• It can be set to compare the dates associated with each set and only overwrite data in an existing Abila Millennium row if the Harris date appears to be more current.
• It can be set to create new rows within the data table and never overwrite existing data with the imported data.

These options may be set on a table by table basis, for the eight data tables that may receive data via this utility: Address, Attribute, Basic Data, Comments, Death, Education, Employment, and Names.

Using these options and the additional procedures that are described below, the Harris Import Utility allows you to map or crosswalk the data that is received to the appropriate locations in the Abila Millennium database.

Utility Classification

The utility includes the option of overwriting existing data, creating new rows, or comparing the dates for the imported data and the existing data and keeping the most current data. Therefore it is classified as a system administrator tool.

Specifications for the Harris Text File

Harris Publishing is more than willing to produce a text file to return to you, according to your requested specifications. The specifications that are necessary for the proper execution of the Abila Millennium Harris Utility are listed here. You must request these specifications from Harris - you cannot assume that a text file will automatically be delivered to you in a format that will work correctly with this utility unless you make your precise requirements known.

• The file must be in standard ASCII format. The fields in the file must be comma delimited (each field is separated by a comma). They may or may not have quotes.
• Dates must be formatted, "YYYYMMDD".
• Phone Numbers should be formatted according to your institutional preference. This data will be inserted into the Abila Millennium row exactly as shown in the Harris text file.
• Harris offers options for the way the records are returned. You should choose either Option 1 (all records, all elements), or Option 2 (updated records with all elements)
• Harris will automatically include a set of Field Change Indicators at the end of each record, consisting of Y's and N's to indicate which of the fields in the record have changed. The utility will check for the presence of these indicators and will not run unless they are present in the expected location, and that the number of indicators matches the number of fields that precede them.
• You should specify to Harris that fields that were coded originally (lookup table driven fields) should be re-coded, and those codes must conform to the Abila Millennium lookup table codes. If you attempt to import codes that are not present in the lookup tables, the utility will consider that data a foreign key and will produce an error message each time it encounters such data.
• If your institution uses them, the utility will accept a ZIP Code + 4 field without additional formatting.
• You must request that the century be reapplied to the year, for example, 98 must be shown as 1998.
• You should request that the prefixes, Miss or Mr. not be removed as is Harris' customary practice.
• You should request that the first name and middle name be placed in separate fields.

Once the file has been returned to your office, it must be placed in a directory that is accessible to the workstation where the utility will be run. Harris also returns a file layout with the text file and that layout information must also be accessible to the user who is running the Harris Import Utility.

Using the Harris Import Utility

In addition to the specifications for the import file to be used, there are some prerequisites and several operations involved in setting up the Harris utility for use at your institution. The following describes and discusses these procedures. Following the descriptions, you will be given a set of instructions for using the Harris Import utility.

Workstation and Login

Some of the standard Abila Millennium utilities make use of certain Microsoft applications and associated files. While we ensure that the required applications are present and registered on the IIS machine as part of the installation process, Abila Millennium's thin-client architecture does not install them on every workstation at your institution. Therefore, to ensure success, the use of certain utilities is only recommended via the IIS machine, using the system administrator's login and password. The Harris Utility is one such utility.

The Harris Utility is run outside of the interface you use to access Abila Millennium, in the utility's own application window. It must be activated by locating the program file (harris.exe) in the Tools directory, using the Windows Explorer or other appropriate tool, and then double-clicking the program name.

You will first be prompted to login to the Abila Millennium Database with ODBC, specifying Web SQL (or another Data Source Name that you may have set up). This utility does not use the SQL Views for the Abila Millennium data tables. Therefore, you must be the database administrator (sa or MILLORA User ID) or a Millennium system administrator in order to execute this utility. If Oracle is your database platform, then you must also have ALTER SESSION permissions on the database in order to change the database date format that will be used by this session of the utility.

Application Window

Following a successful log on, an application window for the Harris Import utility will open, showing the File menu and a Function tool bar that initially displays the Open File button. You may use either the Open item on the File menu or the Open button to navigate to the location of and then open the text file from Harris. Once a file is opened, the application will check its structure. If it does not match the utility's expectations, you will be given an error message.

When an appropriate file has been correctly opened, the utility screen refreshes the menu bar and the function tool bar.

The Menu bar will contain the items, File, and Data. The File drop down menu includes the items, Open, Begin Import, and Exit. The Data drop down menu includes the names of the eight Abila Millennium data tables that might be affected by the imported data.

The Function tool bar begins with graphical file buttons for the eight tables that might be affected by the imported data. Next is the Start button that is used to begin the run of the utility. Finally, there are two list boxes, labeled ID and Date. The ID and Date fields must each be mapped to a specific field from the Harris file in order for the utility to run successfully.

Data Table Sub-Windows

The function of the Harris Import utility is to transfer data from the Harris text file to Abila Millennium rows. Harris uses its own field names in its text file so you must tell the utility which Abila Millennium fields will receive which Harris fields of data. This is done in sub-windows that are specific to each of the Abila Millennium tables that may be involved in the Harris Import utility. These are produced by clicking the table buttons on the Function tool bar.

The sub-window includes a title bar which identifies the data table. Below the title bar are radio buttons which allow you to choose one of three behaviors for the utility. Some of these options are not available for some of the tables, as noted below.

• Replace Fields takes the data in the Harris file and automatically puts it into place in the matching Abila Millennium row, overwriting (erasing) any data that may have been in that Abila Millennium field prior to the run of the utility.
• Check Dates/Replace Fields looks at the date for the Harris record and the last change date for the matching Abila Millennium row and only replaces the Abila Millennium data if the Harris date is more current.
• Create New Address (or Name, Job, or School) will use the Harris data to create an additional row in the Abila Millennium table, based on the Harris data. This option is not available for Basic Data or Death since they cannot have multiple rows for a single constituent. In addition, this method is the only option that is available for the Attributes and Comments table and so no radio buttons appear in the sub-windows for those tables.

Field Mapping

Below the radio buttons, you will see two large list areas that show the available fields for the two files.

The Field Mapping area lists the fields from that Abila Millennium table, that are available to accept data from the Harris file. When the window is first presented, the fields will be set as "unassigned". When you click on a field, it will highlight.

The Harris Input Fields area lists the fields from the Harris file that may be assigned to the Abila Millennium field that is highlighted. These fields are not descriptively named, but simply numbered. Therefore, you must have access to the file layout that Harris also provides. The Harris Input list will automatically scroll to, and highlight the field that is currently assigned to the highlighted Abila Millennium field. You may change this assignment by clicking any other Harris field. In addition to the numbered fields, you will also see the "unassigned" designation which allows you to change an assignment that you have already, made back to unassigned (omitting that Abila Millennium field from the action of the utility).

One exception to the Harris Input Fields area behavior occurs if you choose the Name Type, Address Type, or Job Status field from the respective Abila Millennium table sub-windows. In those cases, the Abila Millennium lookup table values (the first twenty five entries in those tables) for the Abila Millennium field will be listed in the Harris Input Field area.

Conditional Field Assignments

The Harris utility will also create data for certain Abila Millennium fields based on a formula or conditional statement that you construct, and then apply, using the data in the Harris file. Conditional statements may be part of the settings for the following data tables:

Address

Conditional statements may be applied to the activation of the Address Utilizations (aka Mailcodes) for the Address row that is being updated or created. There are four text boxes shown below the field mapping lists in the Address sub-window. Each must be filled in, in order to construct the conditional statement. The first list box shows the names of the fields in the Harris text file. Second is a data box in which you must type the data that must be found in that field in order for the conditions to be met. Third is a list of the numbers (1-20) for the Abila Millennium Address Utilizations (Mailcodes). Last is the list of options for the possible settings for the Utilization (ON or OFF).

For example, if Harris asked in their questionnaire whether the constituent wants to receive the Alumni Magazine at home or at work and coded that response in their field 18 as either H or B, (and if the Abila Millennium Utilization 4 is used for the Alumni Magazine, and if the current Address sub-window is mapping to the Address Type 'a' Home), then the conditional statement would read, "If field 18 = H turn Mailcode 4 ON.

When the statement is complete, it must be Added to the large list area just below. That area will hold multiple conditional statements for various Utilizations.

Attributes

Conditional statements are used to determine whether the utility will or will not create a new Attribute row based on data in the Harris file. There are two variables to set for the conditional statement. The first is identified in a list box showing the Harris field names. The second text box is used to type in the data that must be present in that field in order for the Attribute row to be created.

For example, if field 8 in the Harris file tells whether the constituent bought a directory (Y) or not (N), then a statement might read, If Field 8 = y then create. The utility would then create a new Attribute row with the data that you enter below the conditional statement for each constituent whose Harris data meets the conditional statement.

Comments

Conditional statements are used to determine whether the utility will or will not create a new Comment row based on data in the Harris file. There are two variables to set for the conditional statement. The first is identified in a list box showing the Harris field names. The second text box is used to type in the data that must be present in that field in order for the Comment row to be created.

If the condition is met, then the utility will create a new Comment row for the constituent, using the data that you enter below the conditional statement.

Death

Conditional statements are used to determine whether the utility will or will not create a Death row based on data in the Harris file. There are two variables to set for the conditional statement. The first is identified in a list box showing the Harris field names. The second text box is used to type in the data that must be present in that field in order for the Death row to be created.

If the condition is met, then the utility will create a Death row for the constituent, unless one already exists. You may also choose to automatically set the Address Locators for this constituent to 'deceased' when a Death row is created.

Table Driven Field Assignment

Important! The Utility does not check for data validity prior to the run. If the data in the Harris file does not match the lookup table, Abila Millennium will give you a foreign key error message for each instance of invalid data.

Abila Millennium expects to find the code from a valid lookup table entry in lookup table driven fields (not the value). If you are assigning a Harris field to such a field, you must be sure that the field represents the coded form of a lookup table entry. This is a specification that you must request from Harris of the text file that they deliver to you.

When entering an Attribute Type or Comment Type for a new row that is to be created based on a conditional statement, you must enter a valid lookup table code. This data is entered as free text, but you must enter a valid code. If you enter data in these text boxes that cannot be located in the lookup table, Abila Millennium will generate a foreign key error message.

Matching Abila Millennium Records

If you have chosen either the Check Date/Replace Fields or the Replace Fields behavior, the utility will attempt to match the information from the Harris file to an existing Abila Millennium record using the following set of rules:

• Harris Name information will match an existing Abila Millennium Name row if the ID Numbers and Name Types match. If no matching Abila Millennium Name row is found, the utility will use the Harris information to create a new Name row.
• Harris Address information will match an existing Abila Millennium Address row if the ID Numbers and Address Types match. And, if the Address Locator is mapped and the 'Harris' Locator is unchanged (change indicator = 'N'), then the Address Locators must match as well. If a matching Abila Millennium Address row is not found, the utility will use the Harris information to create a new Address row.
• Harris Employment (job) information will match an existing Abila Millennium Employment row if the ID Numbers and the Jobs Status match.
• Harris Education (school) information will match an existing Abila Millennium Education row if the ID Numbers and the School Institutions match. If no Abila Millennium Education row matches, the utility will use the Harris information to create a new Education row.

Sub-Windows

In some cases, you may want to map different fields from the Harris file to more than one row in the same Abila Millennium data table.

For example, say that Harris sends data for a home address in fields 10-16, a business address in fields 17-23, and a student address in fields 24-30. You would want to map those fields to three different Address rows in Abila Millennium, each with an appropriate Address Type. To do so, the utility allows you to open multiple sub-windows for some of the data tables. In this example, you would use one sub-window to map the Harris fields 10-16 to the appropriate Address fields and set the Type equal to 'a' (Home). In another Address sub-window, you would map fields 17-23 to the appropriate Address fields and set the Type equal to 'bus' (Business). In yet another sub-window, you would map fields 24-30 to the appropriate fields and set the Type equal to 'sch' (School). (Your lookup table entries may use different codes or values.) Obviously, this behavior does not apply to the tables which permit only one row per constituent (Basic Data and Death).

A sub-window for a data table is accessed by clicking the table button in the Function tool bar. Additional sub-windows for the same data table are also accessed by clicking the table button again, while the first is still displayed.

This table shows the sub-window limits for each data table.

The Window item in the menu bar may be used to arrange the windows or the icons, if you have minimized them.

If you click on a table button for a different data table, all of the open sub-windows for the first table will automatically close and be saved in their current state. If you re-access the first table, all of the sub-windows will be presented again, with the same definition they had previously.

The Cancel button on each of the sub-windows closes that particular window without saving the definition.

When the utility application window is closed (either manually or by the completion of the run of the utility), all field assignments are erased and returned to "unassigned".

Running the Utility

When you have made all of the field assignments and created all the conditional statements that you plan to use, click on the Start Button , or click Begin Import on the File menu to run the utility.

While the utility is running, you will be given a status window. The Updated Records will tell you the tables and the number of rows per table that have been updated, the flood bar will give you a percentage of completion, and the Newly Created Records area will keep a listing of how many new rows were created in each table.

If the utility encountered blank ID fields in the Harris file or if it could not find a matching ID in the Abila Millennium table, an error report will be generated and automatically placed in the same directory where the Harris.exe program file is located.

Using the Harris Import Utility

The following instructions assume that you have requested the specified format from Harris Publishing, have received a text file and layout, and placed the file in a location accessible by the workstation and known to the user who will run the utility. For more information about any procedure, see the hyperlinked subject.

1. Use the Microsoft Windows Explorer or other tool to locate the Harris.exe program file and double click on it to launch the utility.
2. As prompted, log on to your Abila Millennium database through an ODBC connection. Select Web SQL (or another data source you may have set up) as the data source. Use either the database administrator (sa or MILLORA ) or a Millennium system administrator User ID and password.

3. From the Harris utility window, click Open on the File menu or click the Open button on the Function tool bar to locate and open the Harris text file. If you attempt to open a file that does not have the anticipated structure - the number of field change indicators does not match the number of fields, for example - you will be given an error message and you must specify another file in order to proceed.

   Note: Any time this main application window is closed, the utility will "reset" itself and any settings, mappings, or conditional statements that had been defined to that point will not be remembered the next time the utility is re-opened.

4. Click the ID list box in the Function tool bar. Select the field name from the Harris file that contains the Constituent ID Numbers. This information is mandatory.
5. Click the Date list box in the Function tool bar and select the field name from the Harris file that contains the Date. This information is mandatory.
6. Click the button corresponding to one of the Abila Millennium data tables. The sub-window that displays will vary according to the kind of data table button you choose.
7. At any time in the process of setting the variables in a sub-window, you may either click Cancel or the Close Window button in the upper right corner to close that sub-window without saving any of the settings that currently appear there. This Cancel command affects only the current sub-window and its variables.
8. If the resulting sub-window includes them, use the radio buttons to choose the desired behavior for the utility in terms of overwriting , checking dates, or creating new rows.
9. If the sub-window includes the field mapping lists, click on an Abila Millennium field name in the list to the left to highlight it. Using your layout of the Harris file for informational purposes, scroll to locate the appropriate Harris field name in the list to the right (the data that corresponds to the selected Abila Millennium field name that is highlighted). Click the Harris field name.
10. Repeat the previous step until all desired Harris fields are mapped to the appropriate Abila Millennium columns. To remove an assignment for a field name, click on the word, "unassigned" in the Harris listing.
11. If the sub-window includes text boxes for the creation of conditional statements, tab to the first of those text or list boxes and enter or choose the appropriate data for the conditions under which an Address Utilization should be assigned, an Attribute row will be created, a Comment row will be created, or a Death row will be created (according to the kind of data table with which you are working.
12. When all variables are set as you want, do not close the sub-window and do not Cancel manually (unless you do not want to save those particular settings). You must allow the utility to close the sub-windows in order to save the settings. You may access an additional sub-window for the same data table by clicking that data table button in the Function tool bar. If so, the first sub-window does not close, but you may minimize it if you want. Use the additional sub-window to create specifications for another data row in the same table, following steps 9 through 13, above.
13. To save the settings in all open sub-windows, access a sub-window for a different data table by clicking that data table's button. If you do so, all open or minimized sub-windows for the previous data table are automatically closed and saved by the utility. All of the variable settings that were in place at that time will be remembered (as long as the main application window remains open), and those variables will be used when the utility runs. Repeat steps 9 through 14 for the new sub-window(s).
14. You may return to a data table's sub-windows that you have already defined by clicking the data table button. All currently defined sub-windows will be reopened and you may edit them as you want. When all specifications have been defined for all data tables, the utility is ready to be run.
15. To run the Harris Import utility, click on the Start Button or click Begin Import on the File menu to immediately begin processing. As the utility runs, a progress message window will display.
16. When the run is finished, you may want to drag the Successful Completion message to the side without clicking OK and take note of the number of Abila Millennium rows that have been updated in each data table and the number of new rows that were created in each data table. This done, acknowledge the message by clicking OK in that pop-up window. Both the message window and the main utility window will close.
17. If an error was encountered, an error txt file will have been created in the same directory where the utility program file was located.

Top of Page