Seed, Bulb, Plant Inventory Software

Leo A. Martin
Fri, 02 Oct 2009 17:25:09 PDT
On Mon, 28 Sep 2009 Dell Sherk wrote

> Did you or someone else say that one could import an Excel spreadsheet
> into a database with OpenOffice?

I don't know about importing a spreadsheet directly into a database, but
Open Office does read MS formats. You could just try it - make a practice
copy of the spreadsheet to import.

Or, what might be simpler... export the spreadsheet to a plain text file,
then import the plain text file to the database. Depending on your
software you might have to manually enter database field names.

Or, perhaps even easier, because it works between any spreadsheet,
database, and plain text editor if the spreadsheet is small enough to fit
into the clipboard: Highlight the data-containing portion of the sheet and
copy to clipboard (Ctrl-C) then paste into a plain text file (the old
NOTEPAD.EXE file works well for this but names are limited to the old DOS
8.3 length.) The spreadsheet rows will be preserved and data in columns
will be separated by tabs. If the spreadsheet is too big to fit into the
clipboard, do it piece-by-piece by highlighting a number of rows to copy
and paste, then go on to the next series of rows. That way you preserve
the columns of data.

I work between a text file, a scratch spreadsheet and my database when I
have a lot of plants to enter into my database. I use an input text file
which I cleverly named INPUT.TXT. I made the original write-protected so I
can't overwrite it. The original is in my PLANTS folder. I put a shadow of
the file on my desktop so I can get to it faster.

The file has instructions to me at the top on how to use it, and then a
single line containing all the fields in my database:

AccessionNumber   Genus   species   Family   etc. etc. etc...

When it's time to enter plants to the database, I open INPUT.TXT and save
it under a new name (usually corresponding to Input20090928.txt) and
populate the AccessionNumber column. I use numbers in sequence, so it
looks like this:

AccessionNumber   Genus   species   Family   etc. etc. etc...

I didn't design my accession numbers properly. If I had just used
incremental numbers I could have used the autofill function of a
spreadsheet to populate the numbers. I have to do it mostly by hand in the
text file. Next time.

I then copy the field names and the accession numbers from the text file
to the clipboard and paste the whole thing into the spreadsheet. I wind up
with this spreadsheet:

AccessionNumber   Genus   species   Family   etc. etc. etc...

Now I can use cut and paste to populate the various fields. For example,
if I'm entering irids, I copy Iridaceae to the clipboard, highlight the
required boxes in the spreadsheet, and paste. For vendors, addresses, etc.
I find the same vendor in my database, open a view with the data in rows
rather than the entry form, and copy the required fields. Then I paste
this into my scratch spreadsheet. I wind up with something like this:

AccessionNumber   Genus   species   Family   etc. etc. etc...
1                 Freesia  alba  Iridaceae  etc  etc  etc
2                 Freesia  corymbosa  Iridaceae  etc  etc  etc
3                 Freesia  refracta  Iridaceae  etc  etc  etc
4                 Freesia  viridis  Iridaceae  etc  etc  etc

Once they're all done I copy the whole thing, column labels and data, from
the scratch spreadsheet to the clipboard, and then paste the whole thing
into the text file. I delete everything from the text file except what I
just pasted. Then I save and close the file.

I go to my database and use the import function to import from a plain
text file. I specify fields are separated by tabs and the text file column
headings represent database field names. Then I import.

This saves a lot of time over manual entry.

Leo Martin
Phoenix Arizona USA

More information about the pbs mailing list