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

UPDATING THE DATABASE LOOK-UP LIST IN THE PLANT SPREADSHEETS

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:

• Introduction: Replace Plant Symbol Codes with Common Names, Latin (Scientific) Names, or Family Names (with and without Excel macros).
• Part 1:  Copy and paste plant symbol codes into Colin's Plant Code Lookup Excel spreadsheet tool to convert them into scientific/latin names, common names, and families.
• Part 2:  Use Colin's Plant Code Lookup Excel macro to convert plant symbol codes to scientific/latin names, common names, and family names.
• Part 3:  Customizing Colin's Plant Code Lookup Excel spreadsheet with individual states or custom lists.

===================================================================
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.