[Odb-help] Preparing an ODB file for importing: a Tip Sheet

[odb-help@lists.democracygroups.org]

People often have difficulty importing files from other databases
into ODB.  This email offers a few tricks you can use to get
started more quickly.

First, here are the common Obstacles:

1) My data file is in Access or Filemaker, and ODB cannot read
these formats

2) My addresses don't fit in 5 lines, or some of my organization
   names and addresses are too long.

3) My data doesn't import cleanly, because some of the
addresses contain extra "linefeeds" and thus when I
import data partial information will end up in two different
ODB records.

4) My data is now in 3 or 4 different places, and I would like to merge
it all together.




Obstacle #1:

My data file is in Access or Filemaker, and ODB cannot read
these formats

Solution:  Access offers you the ability to export tables, and
so does Filemaker.  On the ODB web site there is a resource
that explains how to get the data out of Access, into a Text
file.  This resource might be extended to explain the steps to
get data out of Filemaker, which is really quite easy, unless
your Filemaker database is "locked" in such a way as to
hide the Export function.  If this is the case you may need to
obtain the Master Password for your filemaker database before
you export.  We recommend you choose text format for exporting
your data, and that you choose "Tabs" to separate your data
rather than commas.


Obstacle #2

My addresses don't fit in 4 lines, or some of my organization
names and addresses are too long.

Solution:
This might be confusing to people who use other databases, but ODB
is very exacting about field lengths, and there is a reason for that.
The reason is that we want you to be able to fit the information on a
mailing label, and we want to encourage you to provide street
addresses so that your list can be geographically sorted.  We will
likely relax these restrictions in the future (you can turn a country
field on, adding a 5th line, and version 1.0 already implements 5-line
addresses) but for now, using 0.9.4, you are limited to a 20-character
first name, 20-character last name, and 40-character address lines.

If you data does not conform to this there is work to do.  for example:

Phil Buchanan                                          ->name
The Center for Effective Philanthropy           ->org
1678 Massachusetts Avenue                     ->addr1
Suite 1904                                                ->addr2
Cambridge, MA  02139                              ->city, state, zip

You will need to condense this to 4 lines.  Assuming that your data
is in tab-separated format (see obstacle #1), you would then load
it in using Excel.  In Excel, you could try the following:

a) Sort the spreadsheet by addr2, then by addr1
b) Add a new spreadsheet column after addr2  (column E)
c) Put a formula into column E to make it the concatenation
    of columns C and D.  For example, in the E2 cell, it should say:

=trim(C2 & " " & D2)

d) Now copy the entire E Column.
e) Now choose Paste Special... from the Edit menu and replace
    all cells in the E column with their Values.
f)  Now, some of the remaining lines will be too long.  To determine
    which ones need to be fixed... Add a new column, F, to the
    spreadsheet.  Make F be the length of E.  I.e. in the F2 cell, it
    should say "= len(E2)".
g) Paste that formula into all cells of column E, just in the
    part of the spreadsheet where there is data in Column D.
h) Sort the whole spreadsheet (exclude the header row) by the new
    F column, in descending order.
i) Now you can see which cells need shortening so that they will
   fit within 40 characters.  To start the process, look for common
   shortening tricks.  For example, you can select a column and
   replace instances of the word "Street" with the abbreviation "St."
j) Once you finish making obvious edits (Changing Apartment 7C
   to "#7C") then you might have to make some other manual
   edits.  For example, a line that says "Director of Development"
   is not really necessary for the address. In fact, the postal
   service prefers that you not include it in the address.  So you
   can append it to the comments field.  And  if you have an
   address that says "Harvard University" and then "Department
   of History" you can shorten this to "Harvard Dept. of History"
   without losing any accuracy in the delivery of the mail.
   
You should notice by now that some of the design decisions for
ODB were driven by the needs of grassroots organizations, who
do much of their communication with hundreds of donors at once.



Obstacle #3

My data doesn't import cleanly, because some of the
addresses contain extra "linefeeds" and thus when I
import data partial information will end up in two different
ODB records.

Solution:  The answer is to "clean" your data.  Until
someone makes us aware of a tool to strip out these
extra linefeeds automatically (a tool that would not be
that difficult to write, hint hint), we suggest this manual
method:

a) open your data file in wordpad
b) From the View menu, choose Options...
c) Under the Text tab, click on "No Wrap", then click OK

Then you can pretty easily scroll through your data and
manually remove the extra "line feeds"



Obstacle #4:

My data is now in 3 or 4 different places, and I would like to merge
it all together.

Solution:  All you have to do is load your data into multiple
spreadsheets.  You can then copy the data into a single
worksheet: which might look like this:

A B C D E F G
A B C D E F G
A B C D E F G
A B C D E F G
B C F E G H
B C F E G H
B C F E G H
B C F E G H
B F A H G I J K
B F A H G I J K
B F A H G I J K
B F A H G I J K

>From here you will want to move your data around so that
the similar information lines up, i.e. so that all of the zip codes
are in the same column:

A B C D E F G
A B C D E F G
A B C D E F G
A B C D E F G
   B C     E F G H
   B C     E F G H
   B C     E F G H
   B C     E F G H
A B           F G I J K
A B           F G I J K
A B           F G I J K
A B           F G I J K

This explanation is a bit cryptic, but I for those of you
who have never done this, I am just trying to provide a basic
tool which you can show to a friend for a more detailed
explanation if you need that.