Original author: John Boldrick
Date created: December 6, 2004
Importing Data Into Your METRIX Database
Is This the Right Job for You?
Importing data into METRIX is a complex process. It takes time, patience and practice to learn. Several kinds of problems can arise, and when you don't get the results you expect, it is not always easy to figure out what went wrong.
The first thing to determine is this: is it worth your time to learn this process, or would your time be spent better elsewhere? Some questions you should ask yourself to help you decide:
- Are you already very comfortable working with your computer, particularly with Microsoft Excel and preferably Access?
- Will you need to import large amounts of data frequently, or only once, at your initial installation?
- Do you understand the concepts of relational database design, such as how to normalize data, what a primary key is, and how to map out parent and child tables?
- Will you be the person responsible for making METRIX run?
- Would your organization be willing to use consultant assistance to make METRIX work effectively?
If you don't think you will really need to know how to import data on your own, or if consulting help is an option, you may want to consider having somebody else do it. In describing the import process, we will assume that you are organizing your data in Microsoft Excel spreadsheets, using the default METRIX table-link fields.
Getting to Know Your Data
You will import your data into METRIX from tables in Microsoft Excel spreadsheet form. Usually, your data won't be ready for import without some preparation. The first step is to figure out what your data model is. This depends on how complex your data and your METRIX database are.
Let's determine which METRIX tables you will be working with. Information about contacts?the people and organizations themselves?go in the Contacts table. Information about specific locations?i.e. home and office addresses and phone numbers?go in the Contact Locations table. Interactions each go into their own tables, and the Interaction Code Definitions go into yet another table. Custom interactions involve a different process (more on this later).
If you have only contacts with a single location for each, you can import your data from a single table. If you have contacts with multiple locations, you will need at least two tables, and if you are also importing one or more interactions, this will add at least two more tables. Try to match your data with a data model from the following list:
- Contacts with one location
- Contacts with multiple locations
- Contacts with multiple locations & standard interactions
- Contacts with multiple locations & custom interactions
Organizing Your Data
For now, the simplest thing to do is to put your data into one big table. Use the template that came with METRIX (FILENAME.xls) to create it. Put each individual piece of data you have into a single column in this table: first name, last name, office phone number, home zip code, etc. Each row will hold this information for a single contact. This is the right time to get rid of duplicate records and to make sure that you have all the information for each contact record together. It may help to separate organizations, families and individuals, and alphabetize each category by first and last name. Data field lengths: should template indicate maximum field lengths? Easier to fix early than later.
Contacts with a Single Location
Let's try a simple import of a set of contacts with only one location. Take a look at the spreadsheet (FILENAME.XLS). Here are some contacts, with office location data for each. Notice that even though all of the data are on a single sheet, we are using the field called LinkFieldNumber_1; METRIX will divide your data between two tables, so this field has a unique value for each data record to keep the contacts and locations together.
Notice the field PCL, for "Primary Contact Location." Only one of a given contact's locations can be primary. If the answer is yes, the field will contain a "1," and if not, a "0" (Ones and zeros are used for "yes" and "no" answers in the database). In this case, there's only one location, so the answer is yes.
The field ContactLocationName must have one of the following values: Home, Office or Other. ContactRecordType holds one of the following values: Individual, Family or Organization. These fields allow METRIX to display these contacts correctly.
GroupLabelLine1 is used for the name of a Family contact. ContactName should hold the names of Organization contacts; leave blank for all others, as this field will be filled automatically in the import process. This datasheet is ready to go; the contacts are all lined up with their locations, and each has a unique identifier in LinkFieldNumber_1. Open METRIX and click on Admin. Select Import Data. This is the Import menu. Let's look first at Manage Links. Here is where you tell METRIX which fields you are using to link your tables. In this case, the Parent table is Contacts and the Child table is Contact Locations. If LinkFieldNumber_1 is not already the link column, change it now. You can ignore the other table links. Click Close.
Now select Import External Data. Message window. Browse to the Excel file that has your data, and click OK. Select the worksheet you want to import. Click OK, and enter your password. Now you are looking at the Import Field Mappings dialogue. This is where you tell METRIX where to find the data for each field in the database. If you have kept the field names from the METRIX data template, as we have in this case, it's easy to map your fields: click on Match Column Names, then Match All Source Columns, then click Match Columns. You should see the names of the source data fields appear in the Source column of the dialogue, alongside their matching fields in the Destination column.
Now click the Pre-Import Error Check button at lower left. There shouldn't be any import errors in this example, but if there were, they would be logged to a text file and a message box would tell you where to find it. The error log would tell you the field and record of each problem, and then it would be up to you to determine what the problem is. Common problems are a data-type mismatch or a violation of the field's maximum length.
If no errors were found, you can click the Import button. Your data will first be imported to the Preview Area, a temporary holding-pen where you can make sure that you have the right number of fields and otherwise check your results.
Back at the Import menu, click Preview Contacts. You can now scroll through your imported contact records with the record selector at bottom left. Close, and also take a look at Preview Contact Locations.
Now you are ready to put your data into the database. Click Move Data to UR Tables. You will get a message box telling you to back up your data before moving new data in; this is always a good idea, and if you haven't done this yet, now would be the time. Click OK. Your new data is now in METRIX. Close the Import menu, and select Contacts from the main menu. You should now see your data.
Contacts with Multiple Locations
Now let's try an import with multiple locations for each contact. Look at the spreadsheet (FILENAME.XLS, tab WORKSHEETNAME). This is how your data will look all in one sheet. Note that there are two sets of addresses, phone numbers etc., each with its own ContactLocationName and PCL field. These are the sets of location data. It may help you to stay organized if you do as we have done here, and color-code your column headers, so that you have one color for contact data and another color for each location. In this case, the Office location will always be the primary location, except when a contact has only Home data. We still have a single LinkFieldNumber_1 field for all the data.
Copy the entire spreadsheet and paste it into a blank sheet in the same workbook. Do the same thing again, so you have three identical worksheets. Name the sheets "Contact," "Office" and "Home." In the Contact sheet, delete the columns with Location data, leaving only the Contact data and LinkFieldNumber_1. In Office, delete everything but LinkFieldNumber_1 and the Office location data. In Home, do the same for the Home data.
You now have three sheets of data, which METRIX will hook up using the link field, as in the previous example. You may have figured out already that we could have done this with two tables instead of three, since we used a single table for contacts and one location, but doing it this way illustrates the process better.
Now proceed as before. Open METRIX and select Admin, then Import Data. Check the Manage Links dialogue and ensure that the Contacts and Contact Locations tables are linked by LinkFieldNumber_1. Select Import External Data, and find the spreadsheet with the data. This time, you will run the next part of the import process for each of your three worksheets. First, select the Contact worksheet. Map the fields and run the Pre-Import Error Check as before, then run the import. Take a look at Preview Contacts and make sure it's all there. Now select Import External Data again, and do the whole process again for Office sheet, and then for the Home sheet. Examine your import in Preview Contact Locations.
