1 Reply by iwoodward

  • iwoodward
  • Member
  • Offline
  • Registered: 07-09-2007
  • Posts: 8

Re: Reformatting tabular data for import into Recorder

Hi All,

Has anyone produced any tools for quickly converting data collected in a tabular format into a format to load it into Recorder via the Import Wizard (e.g. transect data with columns for different sections of a walk, or columns showing monthly data from the same location)?

I've been dealing with a number of different spreadsheets in this type of format - I've designed something in Access that helps but still requires some manual adjustments to the original table format. I am sure it will be possible (and probably fairly simple) to completely automate the conversion but before I try to do this I though it would be worth checking whether anybody had already done it.

Also If anyone has any other ways of getting tabular data into Recorder then it would be good to hear about them. It would be nice if we could have a Data entry form in Recorder allowing tabular data to be manually input en bloc the same format and converted within the system!

Thanks,

Ian

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Reformatting tabular data for import into Recorder

Could you possibly post an example?

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

3 Reply by iwoodward

  • iwoodward
  • Member
  • Offline
  • Registered: 07-09-2007
  • Posts: 8

Re: Reformatting tabular data for import into Recorder

Charles,

I have uploaded a file containing examples of tabular data - the first showing columns of transect data, the second with the columns relating to dates and the third with columns relating to sites (the first and third are effectively the same thing although the transect data contains additional info relating to the whole dataset).

I have a large number of data sets that I need to convert into a format ready for importing into Recorder - these are similar to the examples but with different numbers of columns and column headings

http://forums.nbn.org.uk/uploads.php?page=0

4 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Reformatting tabular data for import into Recorder

Coincidentally I have just been working on the problem this week. I have uploades the resulting spreadsheet to http://forums.nbn.org.uk/uploads.php?file=Tab%20data%20to%20Recorder%20import%20example%20-%20Tyntesfield%20moths%2003%20to%2005.xls

The conversion is on Sheet 2. It would be useful to reconfigure your data so that the species list starts in cell A2 with nothing in the rows below, and the abundances in C2, otherwise you may need to play with the formulae a bit.

Please contact me if you need assistance

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

5 Reply by Gordon Barker (edited by Gordon Barker 31-01-2008 12:40:15)

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Reformatting tabular data for import into Recorder

I should add that in the upload Sheet 2 is autofiltered to exclude zero values, so you would need to remove the filter to get the correct formula in cells A2, B2 & C2. Sheet 3 is the 'Paste Values' of the filtered Sheet 2 result.

The formula is based on the OFFSET function, calling from the top row and left column. The repetition is based on the number of rows in the species column i.e. if there are 25 names in the species column, the 26th row in the results (Sheet 2) moves across to the top row of the next column (& 51st, 76th etc)

I haven't yet run an import with this data, but it should work.

Any suggestions for enhancements gratefully received.

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust

6 Reply by iwoodward

  • iwoodward
  • Member
  • Offline
  • Registered: 07-09-2007
  • Posts: 8

Re: Reformatting tabular data for import into Recorder

Thanks Gordon,

It looks to be just what I was after. Will let you know if I come up with any enhancements.

Ian

7 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Reformatting tabular data for import into Recorder

I have uploaded an updated version of this tool to http://forums.nbn.org.uk/uploads.php?file=Tab%20data%20to%20Recorder%20import%202%20-%20example%20data%20-%20Pin%20Mill%20inverts.xls

Hopefully, description and instructions are clearer than in the original, it should also be easier to understand the formulae and amend them if needed to suit the data. The original version wasn't even particularly clear to me, after not having used it for a few months. I can confirm that data does import as expected into Recorder.

Please contact me if you have any queries.

Gordon

Gordon Barker
Biological Survey Data Manager
National Trust