Jump to content
Sign in to follow this  

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.


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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Create New...

Important Information

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