Saturday, November 19, 2011

Part 3: Updating the Plant Look-Up Spreadsheet Lists from the USDA Plants Website


Edit 4/25/2013:  I updated all spreadsheets to be able to convert Scientific Names to Accepted Plant Symbols, which was a requested feature.  In order to do this, I had to eliminate plant synonyms from the data list. But, I added a few other columns such as Family Common Name, Family Symbol, and Order.  The images below look slightly different than those in the updated spreadsheets.  - Colin

If you're using Excel 2003, you should download the ".xls" files.   If you're using Excel 2003, be aware that there is a 65,536 row data limit. If you use the current list without plant synonyms, it's not a problem. However, if you wish to include plant synonyms, you may have to limit your list to a particular state.  To download another state (or to update the LIST worksheet in any of the workbooks), follow these instructions:
  1. You'll need to download the list of plants from the USDA PLANTS database:
    • Click here to open the link to the plants database with the correct settings selected.
    • Click on "Review Selections or Sort Report"
    • Click on "Modify Selections"
    • Select the state you want plant info for or select other options to display.
    • Click on "display results" at bottom of the screen. 
    • On the new screen that comes up, click on the word "download" in small print on the upper right-hand portion of the screen, next to "Printer Friendly" 
    • A new screen will appear with comma-delimited text. 
    • Save the webpage as a text file.  There are many ways to do this, one way is to copy all the text, open notepad, paste the text, and save the file as a ".txt". 
  2. Import plant list into Excel 
    • Open Excel
    • Click on "File" and "Open"
    • Be sure the type of files being displayed is "txt" or "all files"
    • On the first, "Step 1", screen be sure the circle or box next to "Delimited" is marked. 
    • On the second step scree, be sure there's a check mark next to "comma" and the others are unchecked.
    • On the final screen, click on "finish".
  3. Copy all the plant codes and other data into this spreadsheet's "LIST" tab.
    • On the plant list you imported into Excel in the last step, select all cells (or press  "Ctrl + a"), then press the copy button (or "Ctrl + c").  This will highlight and copy the entire worksheet.
    • On the "LIST" tab of this workbook, press "Ctrl + a" and then either press the paste button or "Ctrl +v".
    • Re-save the "Plant Code Lookup" file with the new state name abbreviation at the end.
You're ready to go!
Note: using the Excel 2003 version, plant families are not downloaded so will not show up on the code lookup page.

Review other sections of this topic:
Also, see my blog and free download to convert AOU bird species codes into common name, scientific name, or family name.  The tool also helps sort bird lists in phylogenetic order.

No comments:

Post a Comment