How to generate a Tree List and Open It in Excel Edward Frank
Novemebr 30, 2009

How to Generate a Tree List and Open it in Excel

1) Go to the form http://plants.usda.gov/adv_search.html

2) Fill in the checkboxes and pull down lists with the options you want.  In the example I attched in the previous post, I chose New York under State or Province, but did not chgeck the display box.  Under scientific names I included "Accepted names only" and checked the dispay box.  Under National Common Name - I entered a wildcard *  and checked the display box.  Under Ecology- Growth Habit - I chose tree and checked the dislay option.   Under Ecology - Native Status I left the choice at any and checked the display box.  I did not select or change anything else.

3) At the bottom of the page I hit the display results button to see if the sort selections worked, or if they were the ones I wanted.  
  a) If they are not what you want or you get no results, hit the previous page button in you browser (In Windows Explorer it is a back arrow at the upper left corner of the page) go back through the selections and look for what is missing or needs changed.

  b) If they are acceptable then hit the back buttom to return to the selecetion page.

4)  Don't change anything  On the bottom right of the page check the box "Download text file without formatted display" box and then hit the Diplay results button.

5) You will see a text listing of the data i your browser.  Select everything and save it as a text file.  In Windoews Explorer at the top pull down the menu under edit and choose "select all.'  Then under the file menu choose "save as.." and then choose text file on the next screen.  Give the file a name and take note of where you save it.

You can now import the file into Excel.  I am using 2007, but the process is the same in other versions and spreadsheets, just the buttons may not be in the same place.

6) Open Excel

7) Go to the Data Tab and click it.

8) In Excel 2007, the third icon from the right reads "from Text" click it.  In other versions you are looking for the option to import data from a text file.    This opens a window where you can select the text file you saved in step 5.

9)  This opens the Text Import Wizard.  Choose the option Delimited, then hit the next button.  

10)  Under the delimiters button check other, and then type in the " symbol.  A rough sorting will then be shown in the small window in the wizard.  There will be some extra columns, but so long as the data is organized right, that doesn't matter.

11)  Click Next. Under column data format the general optio is already selected.  That is fine.Click finish.  Tell it you want to import it into existing spreadsheet (it should be balnk at this point).  Click OK

12)  Voila!    The Tree list appears in the spreadsheet.  sorted into columns.  You can go through and delete the columns that have only commas or blank columns, and the list is ready for editing.

Ed Frank

 

Continued at: