From PDF to Excel to Access

I've got a database in a PDF that I’ve converted into Excel to see if code can be written to extract the data for dumping into Access. The biggest challenge I see is that the data is not structured the same for each foundation (see below).

1
41 Washington St. Foundation
914 S. Harbor Drive
Grand Haven, MI 49417-1745
County: Ottawa
Contact: James P. Hovinga, Dir.
Established in 2003 in MI.
Donors: The James P. and Debra K. Hovinga
Charitable Foundation; James Hovinga; Debra
Hovinga.
Grantmaker type: Independent foundation.
Financial data (yr. ended 12/31/11): Assets,
$1,399,135 (M); gifts received,
$50,000;enditures, $43,496; total giving,
$32,000; qualifying distributions, $32,000; giving
activities include $32,000 for 10 grants (high:
$10,000; low:$1,000).
Fields of interest: Higher education, college;
Education; Homeless, human services; Christian
agencies & churches.
Limitations: Applications accepted. Giving primarily
in Grand Rapids and Holland, MI.
Application information: Application form not
required.
Initial approach: Contact foundation
Deadline(s): None
Officer: Sue Boschma, Pres. and Treas.
Directors: Debra Hovinga; James Hovinga.
EIN: 611438980

2
Frances H. Abbott Memorial Foundation
P.O. Box 3636
Grand Rapids, MI 49501-3636
County: Kent
Application address: c/o Riverside-Brookfield High
School, Attn.: Beth Augustine, 160 Ridgewood Rd.,
Riverside, IL 60546, tel.: (708) 442-7500
Established in IL.
Grantmaker type: Independent foundation.
Financial data (yr. ended 12/31/11): Assets, $0
(M); expenditures, $12,582; total giving, $7,000;
qualifying distributions, $7,733; giving activities
include $7,000 for 2 grants to individuals (high:
$3,500; low: $3,500).
Purpose and activities: Scholarship awards to
graduating seniors of Riverside-Brookfield High
School, Illinois.
Fields of interest: Higher education.
Type of support: Scholarships—to individuals.
Limitations: Giving limited to residents of Riverside,
IL.
Application information:
Initial approach: Letter
Deadline(s): Apr. 20
Trustee: Fifth Third Bank.
EIN: 366672535

i'm not sure i understand your question, but my solution would be to just setup the MS-Access database with data entry forms, then guess how long it'd take to fill in all the info so i know about how much it'd cost me, then go to the local highschool and get the computer lab/FBLA teacher to recomend a couple students to do the work, then randomly check entries and deduct from the final payout based on the random sampling's % error.

This issue is all about handling strings. Identify how your PDF information is structured. I noticed that they share similarities: e.g. chr(10) for new lines, or colons ( help identify fields and associated information

Because of this you could split the PDF extract into an array, then loop through each line to determine what it is. If you have multiple extracts per PDF page it will help to know where your start and end sections are. e.g. start = 1 and end = EIN.

Once you perform the split, you then loop through each element in the array and read the string (likely using Instr function).

E.g. if the string is a County, then read the string into a County field. If it's a Trustee, the line will be read into a Trustee field, etc.

You could then delete or nullify the read parts of the split array and see what's left. If you noticed, for example that the first 3 rows always contained address information, then you know how to resolve the issue.

You can also read a complex string like this one and split that into another array. (i.e a loop within a loop) This could be used to further break up the string into smaller parts.

Financial data (yr. ended 12/31/11): Assets, $0
(M); expenditures, $12,582; total giving, $7,000;
qualifying distributions, $7,733; giving activities
include $7,000 for 2 grants to individuals (high:
$3,500; low: $3,500).

Most of this is fairly elementary string manipulation, but the first time coder will have a bit of learning to do. You will need to know how to read PDF into text, how to use the Split function, understanding arrays. loops, writing to Access fields, and various other functions.

I recommend that you think about the logic of doing what you need and then divide the task into small pieces. Get each task to work as required and so on. Look around or ask the forum for the answer to small problems (rather than a large problem such as you described), and it is likely that you'll find the info you need.

Hope this helps.

Cheers