Jump to content
Sign in to follow this  
wlegard

How can I import Bird Survey data into a join table?

Recommended Posts

Hi. I've set up a simple database for recording bird surveys. A screen capture of the "Relationships" display is attached below. Here's a description of the three tables:

1) BIRDS = a list of bird species known to be in this area;

2) SURVEY = a survey description;

3) RESULTS = a join table used to link the the BIRDS and SURVEY tables and record the birds seen on each survey.

I have a large number of previous bird surveys stored on Excel spreadsheets, each basically a survey description (which I can manually enter into the SURVEY table) and a column of the birds seen (names that match the bird_name field in the BIRDS table).

Is there an easy way to import a column of bird names to fill the foreign key ( _kf_birds_id) in my RESULTS table with the appropriate linked value (_kp_birds_id) from the BIRDS table? Or should I just import the bird column into an empty IMPORT table, calculate the foreign key value there, then import from the IMPORT table into RESULTS?

Thanks in advance for any suggestions.

post-107150-0-98140800-1343687402_thumb.

Share this post


Link to post
Share on other sites

I think your second idea will work (or something along those lines). Since you need to create primary key values that don't exist in or source data - this needs to be a multi-step import process that will probably require some scripting.

What format is the "column of the birds seen" in? comma separated list? If you can convert that to a return separated list in a temporary field/table, I think it will be easier to work with

Share this post


Link to post
Share on other sites

Thanks for the suggestion dansmith65. The "birds seen" list is a column in Excel, with 1 row per bird.

Following your suggestion, I created an IMPORT table linked to BIRDS with a bird_name field and included a reference to the BIRD table's key field (_kf_birds_id) that fills in automatically with the import,

Then I import the appropriate BIRD table key field values from the IMPORT table into my RESULTS table. Not very elegant, but it works.

Thanks again!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.