Updated Aug 12, 2007
The plant database is maintained from a master spreadsheet. The website database is completely refreshed by loading the latest master spreadsheet. To aid in validating changes, the website holds more than one version of the spreadsheet. At the time of upload, each spreadsheet is given a version number. On the upload page, a table shows all versions present. One of the versions is selected to be the publically viewable database. However the upload page permits viewing the contents of any version. Typically when changes have been made, an upload is done, the new version is examined on the website to see that new items are properly present, then it is made "active" (for public viewing).
The spreadsheet can be kept in your preferred format. Perhaps that is Excel, as a .xls file. However whenever you wish to upload new information, you must do a SaveAs and select the "common separated values" .csv format. Excel may prompt you "may contain features which are not compatible with CSV", select Yes save anyway.
A sample spreadsheet, illustrating the required format, is at PHP/test-db-good-short.csv, here.
To load the spreadsheet, use this webpage of the site: Resources. over on the right hand side, near the top, is a small link called "admin". Click on admin. This brings you to a page which is used for both
a) loading a new .csv file, and
b) viewing and selecting which .csv file (usually the latest one loaded) to make viewable on the website.
c) to use this page you have to enter a password.
Try this: Open a second browser window and go to http://www.mayaforestgardeners.org Select Resources, then Browse by Plant. Notice on this page you can now sort in various ways. We'll call this the DB-viewing window.
Open Excel/OpenOffice and load the .csv. To see it you have to set FilesOfType to AllFiles. Now modify it slightly, perhaps adding a fake plant row. Now SaveAs and pick SaveAsType CSV Comma Delimited, then Save.
In the first browser window .csv File Loader,
click Browse and select your new .csv file.
Click Now upload this .csv file.
Any errors will appear at the head of this page after the upload attempt. If successful, now you see a new entry in the table, and it is not yet selected. In the DB-viewing window, if you click Refresh, you still see the same old page, no change.
Here is a sample file, to see an example spreadsheet in good formatting. Right click and do "save target as": short .csv file. Now in Excel/OpenOffice, Open and select this file.
In .csv File Loader window, click on "view in test mode" in the row with your new spreadsheet. You should see the change you made there!
Navigate via admin link back to the .csv File Loader (don't use the browser Back button).
Now in the table click the Currently Active selector to choose your new file.
Then click Make Selected Spreadsheet Active. (don't forget to enter the Password first).
Now in the DB-viewing window, click Refresh, and the new page is now there! If you get any error messages, please cut/paste them into email to me, along with attaching the .csv file you were working with when the error occurred.
There are a number of requirements on the formatting of the spreadsheet. These methods enable the DB upload program to recognize the row and column format.
The spreadsheet must consist of exactly two header rows followed by plant rows, followed by people info rows. The header rows contain names which we scan for, to enter those columns into the database. Hence the exact text of the header names is important. The header row contains both a) names of plant info columns, and b) people names. We require all the people names to be in columns to the right of all plant info columns. Further the first person column must be exactly preceeded by a column with this text string in the cell by itself: STARTNAMES.
Here is the list of plant info column text we must find in the cells of either the first or second row of the spreadsheet:
Family, Scientific name, Habit*, Uses, English, Spanish, Mayan, Creola, Other Info, .jpg
* means other characters can follow but name must begin with that text. Each of these column names must appear exactly once.
The exact text "STARTNAMES" is important and is taken to mean all following columns are names of people/places. A column beginning LOCATION will be ignored and not interpreted and not loaded into the people list.
In all following plant rows (3rd row and onward in the spreadsheet) we look for x or X in the person columns to associate a plant with that person.
If both the Scientific name cell and English name cell are blank, then we ignore this row (so put something in there). If a Scientific name is the same in more than one plant row, it is advised to make them unique by changing the scientific name to e.g. Annona 1, Annona 2.
In general the cells of a plant row contain descriptive text, which we show in the Plant or People table on the website. Two cells have contents which are interpreted specially. The .jpg column is either a blank cell, or is the file name of a plant image expected to be in the images-plants/ directory. Further this cell can contain multiple filenames with the syntax: xxx.jpg PHOTO: yyy.jpg
Futher the PHOTO: zzz.jpg can be repeated several times for up to 4 images (no limit is enforced, but the webpage is less viewable). The exact text PHOTO: is required (uppercase and with the colon). A space can preceed and follow, or not.
The Other Info cell is expected to contain a hypertext link in the format: display text http://aaa.com
That is displayable text followed by a legal web address. The displayable text is made into a hyperlink going to the address.
Further the keyword LINK: can be used to supply more than one link, in this format: display http://aaa.com LINK: more text http://bbb.com
Up to 4 links can be provided (no limit is enforced). LINK: must be exactly that syntax (uppercase and a colon). A space can preceed and follow, or not. If no http: is found then the display text is shown as text and not a hyperlink, this is fine.
You are free to modify your master copy of the spreadsheet by any of,
a) add/remove as many plant rows as you like. Then upload and those updates are immediately there!
b) add/remove as many people columns as you like, BUT all people names must be to the right side of the spreadsheet, following the special column labeled STARTNAMES. (that column name clues the loader that people names follow, not plant information).
Then upload, and those people are immediately there!
In the event you wish to add new columns with new plant information, please go right ahead. Just be sure,
c) they are to the left of STARTNAMES column!
d) each new column gets a nice unique name in one of the two header rows.
e) then let me know, as I have to manually add plant info to the DB. This part is not automatic.
Following the plant rows are some special people information rows. These rows add information about people in the columns under peoples' names. The people information rows are indicated by a row with cell A containing: PEOPLEINFO. Following this are three rows with cell A containing: .jpg, Location, Quotation. The .jpg row contains a filename under a person, or the cell can be blank for no photo. The photo is expected to be in directory image-people/. The Location cell contains a short text descriptive of the persons geographical location, and is shown next to their photo. Similary the Quotation cell contains text, which can be 1000 characters long, with descriptive information about the person, or perhaps a statement from them.
Users of Excel: please do not use the character " (double quote) in any cell. Excel can't save this in CSV format. Secondly do not embed "returns" in the text in a cell. If you use either, then upon loading we will get the columns mis-aligned.