1 Reply by writter

  • writter
  • Member
  • Offline
  • Registered: 12-01-2009
  • Posts: 30

Re: Butterfly: Transect Walker and Recorder

Hello,
at our Wildlife Trust we are using Transect Walker for our butterfly transects. Our central database is Recorder, where finally all data should go to. Now we've got quite a lot of data entered into Transect Walker and I don't want to enter them again into Recorder. So I wonder, if there is a tool to import data from Transect Walker into Recorder? There should be more people who need such a tool!
So my question is, if somebody knows such a tool, or is planning to develop one?


Thanks a lot,
Wolfgang

Wolfgang Ritter
Data and GIS Officer
Berks, Bucks & Oxon Wildlife Trust
( 01865 775476 Ext. 208
* The Lodge, 1 Armstrong Road, Littlemore, OX4 4XT
WolfgangRitter@BBOWT.org.uk

2 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Butterfly: Transect Walker and Recorder

Can you export data out of Transect walker (its been a few years since I last used it)?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

3 Reply by charliebarnes (edited by charliebarnes 07-10-2009 13:18:42)

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Butterfly: Transect Walker and Recorder

I've just read Transect Walker can

Export BNM-style butterfly record data (grid ref., species, date, number seen, etc.) as text file for distribution mapping

So it should be quite easy?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

4 Reply by writter

  • writter
  • Member
  • Offline
  • Registered: 12-01-2009
  • Posts: 30

Re: Butterfly: Transect Walker and Recorder

Hello Charlie,
thanks for your answer and sorry for my late reply.
Your are right, there is a method of exporting the data out of Transect Walker and there is an import wizard for Recorder.
But at the moment we've got 256 data sets in Transect Walker waiting for being transferred to Recorder. And we will have a lot of TW data sets every year. So I think, it would be useful to have a tool, which copies the data data directly from TW into Recorder without steps in beteween. Even if I would invest some time in such a tool, it could be worth it, because it would be paying back the time and would be much more safer.
I've got access to Recorder via ODBC, but at the moment I'm not sure, if I know the structure of Recorder well enough to do that.


Wolfgang

5 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Butterfly: Transect Walker and Recorder

What sort of format does TW store it's data in?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

6 Reply by writter

  • writter
  • Member
  • Offline
  • Registered: 12-01-2009
  • Posts: 30

Re: Butterfly: Transect Walker and Recorder

It's a set of ASCII files.

7 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Butterfly: Transect Walker and Recorder

What sort of format is the data in (can you post/upload a section)?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

8 Reply by writter

  • writter
  • Member
  • Offline
  • Registered: 12-01-2009
  • Posts: 30

Re: Butterfly: Transect Walker and Recorder

I've uploaded a zip file with the data for one year in one reserve. It's called TWExample.zip.

9 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Butterfly: Transect Walker and Recorder

Looking at the files the *.ful seems to contain the actual dates/species and counts - the rest just seem to contain metadata, and averages.

Depending on how much of the metadata you wanted (if any? a lot would only need to be put in once - site details, people etc.) it should be quite easy to run though the main data file (.ful) and create a file that could be imported into recorder (I'm not sure if a direct SQL query would be possible/easy enough, given the intricies of recorder, although a script could concatenate the results from each site .ful into one import file)

The only major hurdle would be how you deal with sections of the transect - as subsites?

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

10 Reply by writter

  • writter
  • Member
  • Offline
  • Registered: 12-01-2009
  • Posts: 30

Re: Butterfly: Transect Walker and Recorder

In an ideal word I would like to have a tool, which checks, if the sites and people exist in Recorder before and adds them into Recorder, if they are not there and takes the unique identifier from Reocorder, if they are there. It then enters the data into Recorder automatically and tracks the information somewhere.
What do you mean by the intricies of Recorder? Do you think there are problems in creating SQL queries to import the data? Are there many things, which I can't see, if I only see the tables? That't a very important question for me, because then I wouldn't try to do it.
To have a  tool, which concatenates the TW data to one file would be nice for the existing data, but not that much for the future data.
I think the sections should be subsites then.

11 Reply by charliebarnes

  • charliebarnes
  • Veteran
  • Offline
  • From: Greater Lincolnshire
  • Registered: 07-09-2008
  • Posts: 1,384

Re: Butterfly: Transect Walker and Recorder

Because Recorder uses a relational database, it stores (eg) Brimstone as (eg) NBN45676 in the database - if you were to implement a conversion programme which used direct SQL you would have to query to find out the NBN45676 (and the same for locations, people etc.)

IMHO the safest way would be to convert the data into a plain text CSV format that can be then be imported into Recorder (and let Recorder decide what SQL it want's to use) - Recorder would then also perform all the necessary data checks, site matching etc.

With regards to the concatenation, I was envisiging a end-of-year data dump into Recorder - convert all your sites into CSV format and then concatentate them to make it quicker to import.

Charlie Barnes
Information Officer
Greater Lincolnshire Nature Partnership

charliebarnes's Website

12 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Butterfly: Transect Walker and Recorder

I have to agree with Charlie, attempting to insert the data directly into recorder would probably be fraught and would bypass most of the built-in validation checks, with a real risk of data corruption. Probably not to be attempted unless you are very confident about your understanding of the data structure.

Recorder's import wizard has its quirks, but at least there is a development team working on it full time and any problems can usually be resolved, why reinvent the wheel?

I think you would be best spending your time working on a way of converting the data into a format that Recorder will accept. The individual sections could be subsites or samples, depending on how you want to be able to look at the data later on, but some manipulation will be required from the .ful file you uploaded since every section for every species for every walk is clearly its own record and should have its own row in the table to be imported.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

13 Reply by writter

  • writter
  • Member
  • Offline
  • Registered: 12-01-2009
  • Posts: 30

Re: Butterfly: Transect Walker and Recorder

Hello Charlie, hello Rob,
thanks for your answers.
I think, I know now, what I have to do. I won't try to create the one big tool, which moves data from TW to Recorder in one step. But I will create a tool, which concatenates the TW data once a year or more often into a format, which then can be imported into Recorder via the wizard.
Thanks a lot again,
Wolfgang