Cataloging multiple spreadsheets:

contents previous next

2.6 Cataloging multiple spreadsheets

Spreadsheets are fine for managing individual worksheets of data, but they are not so suitable when it comes to managing large numbers of worksheets, or complex connections between worksheets.

You can use a catalog as a place to store and structure tabular data from spreadsheets.

For example, you may have a number of spreadsheets like:-

data-jan-5.xls:-

name      strain     result     quality    notes
r1        a100       56         ok
r2        a100       72         med
r3        a200       76         bad        some noise
r4        a200       30         ok
r5        a200       22         ok

and strain-info.xls:-

strain     sample-room   freezer     desc    
a100       100           G           knockout of XYZ
a200       76            H           wild type

and store all your spreadsheets in a directory like:

  strains/
     strain-info.xls
  jan/
     data-jan-5.xls
     data-jan-7.xls
     data-jan-8.xls
     data-jan-9.xls
     data-jan-10.xls

The problem then becomes - how to sift out data which is scattered across multiple spreadsheets? (e.g. to choose all the good quality data values for a particular strain). With the spreadsheet you have to resort to time consuming manual copy / paste of rows to extract the data you want.

If you copy the data into Catalyzer, however, you can do instant sorting and searching across all the data.

You could structure the catalog similarly to the file system: and then copy each spreadsheet into a different folder of the catalog.

Catalyzer viewing a single worksheet from a spreadsheet

Once you have the data as a catalog, you are free to:

- Search and browse across all the spreadsheets at once, so you could extract all the good results sorted by strain
- Link together the different types of data - e.g. making a direct connection from the strain name in the results field to the data from the strain-infor spreadsheet
- Publish the catalog as a website to make it easy for anyone in the group to browse.

Combining data from multiple spreadsheets, and pulling out all the 'bad' quality rows

A simpler database

Because catalogs are based around familiar notions of folders and subfolders, they are often easier to use than database applications, which typically need a programmer to set up.

You can set up a catalog to store the types of information you may have put in a database before Catalyzer was available.

There are a number of ways of automatically importing the data you have in a database into a catalog (see section importing data from a database).