[odb-help] How to import tracking codes from old ODB database

[Organizers' Collaborative]

At 06:46 AM 1/12/03 -0600, you wrote:
>Hi Rich, I have customized the ODB for our uses, but we haven't really used
>it to see how effective it is....


>  One problem I have had that I should have written to
>the discussion group about was how to code ahead of time all the contacts
>from a particular organization /group that we want to import into the larger
>ODB data base.

Answer:
There is a way to import codes into ODB along with the data;
because of the simple (non-relational) structure of the codes
this is actually even easier than it might otherwise be.  To
do this:

1) figure out what the final codes will look like and
    customize your odb database (using "enable code changes")
    so that it has those codes.
   For example, today I converted an ODB database for a group in new
   jersey that had yes-no codes for "key" and "key list" and "active
   within affiliate".  The group also had a code for affiliate,
   which was basically a 3-character code.

   I created 3 tracking codes in the "activities" area: A1 for
   active within affiliate and K1 and K2 for the two key codes.

2) Then, go into their old database (Access), and export
   all the data directly into an XLS (Excel 97) format, for
   the main flat "people" table.

3) The true-false fields in access will appear in Excel with
    the words "True" and False.  you need to get rid of false
    values as they are no use.  So for example, for the Key
    contact code, I just selected the column in Excel, and
    did a "Replace..." operation, replacing "FALSE" with
    nothing.  Then I did a second replace, replacing "TRUE"
    with "K1".

    Do the same thing for the other fields you want to move
    into ODB, putting in the 2-letter codes in place of "true".

4) Now you need to build the ODB style activities field. The
    way to do this is to use the Excel string concatenation
    function, which is &.

    What you want to do is to concatenate the codes in
    alphabetical order.

    So if A1 codes are in column B, and K1 codes are in
    column H, and K2 codes are in column G, you need to
    insert a new column, and the put the formula in row
    #2 (the first data row):

    =B1 & H1 & G1

    Then you need to copy that cell to all the other cells
    in the new column.

5) Again, if you use Excel a lot, you probably already know
    this, but the next thing you want to do is give the column
    a proper header, such as "Activit" or "Activities", and
    if you are sure that all the activity codes are properly
    stored in it, "fix" the values of the cells.  No, I am
    not talking about spraying artist's fixer compound on
    your screen.  I mean what you do is copy the entire column,
    and choose paste special from the edit menu, and finally
    select "Values" and click OK.  This replaces the formulas
    with the results of the formulas.  Then you can delete
    any of the component columns that you used to create
    the rows, row B, row H, row G.

6) Again, needless to say if you are a frequent excel
    user, but after each step it is a good idea to save
    a copy of your work.  So you might start out with
    members.xls, and by the end of the process you ought
    to have intermediate versions members1, members2,
    members3.  That way if you make a mistake and wipe
    out part of the spreadsheet, you can go back to
    a version before you made the mistake.

7) you need to undertake a similar process to prepare
    any other fields for ODB import.  For example, the
    affiliation information can be imported into the ODB
    "region" field, and that field requires that the names
    of the region codes have no more than 3 characters.

8) Before importing, save the spreadsheet one last time
    and then save it in either a tab separated (.txt) or
    a comma separated (.csv) format, using the option that
    is available in the "Save As..." command window.

9) Now go back to ODB, and if you are still in "Enable
    code changes" mode, good, because that's where you need
    to be.  Now, assuming your import document has ALL
    of the changes necessary, click the import button and
    then click on the "P2" option near the top of the import
    screen.

    this is the secret to importing additional information
    besides basic contact information and comments.  The
    P2 option is a people import with "Additional fields"
    the additional fields include VID, Activit, Issues, Type,
    etc.  If you find a field is missing that you need
    to be included, just email us and we will see if we
    can get it in.

    As soon as the data is imported, you should then be able
    to go into Activities, click on A1 or K1, and see all
    of the imported records that had those values.

-rich

p.s. the import screen of 0.89 has several limitations
which were removed in 0.9.  0.9 is much more powerful and
it includes much better duplicate checking algorithms, which
were added in the last 48 hours!  It
is available upon request; just email rich at organizenow.net