Opening Downloads in Excel (or other spreadsheet application)

So you’ve completed your query using the VertNet data portal and you’ve downloaded a file to your local computer. You’ve probably noticed that if you double click on the file to open it, your computer’s default text editor opens the file. This may be a fine option, depending upon what you want to accomplish with the dataset. For many, however, it is necessary to open the file in a spreadsheet application, such as Excel. The following will detail how to open files Downloaded from VertNet using Excel or other applications.

The Download File

The file the the VertNet data portal provides is a TXT-formatted document, which is a type of text file in which the data are organized using tabs. TXT is a standard download format, used by a wide-range of data-sharing services. A TXT file can be opened using the processes described in this document. When you have completed a download from the VertNet data portal, the filename will look similar to these examples: spp_mykiss-2398756290.txv or DMNS-fossils-9898734867.txt, if you named the download file, or MyResults--23978409872349.txt or -23978409872349.txt, if you didn’t.

To open the file, just double click it. This will open the file using your computer’s default text editor. For PCs that’s probably Notepad, for Mac it’s TextEdit. If you’ve changed the default, it will open using your selected application. Any text downloadable editor, such as TextWrangler, Smultron, Notepad++, or Sublime (depending upon your OS) can also open the TXT file.

Spreadsheet applications, such as Microsoft Excel, Numbers, or Calc, are all capable of opening TXT files, and offer greater flexibility to review and organize datasets.

Opening the File in Excel

The following process describes how you can open your VertNet download in Excel. Other spreadsheet applications may have similar processes, but we recommend you consult your application’s Help files to learn how to open a TXT correctly.

Before we get to the steps, there is something important you need to know about opening text files in spreadsheet applications — you don’t want to change the contents of your data unwittingly.

What is a Character Set, Why UTF-8?

A character set is simply the set of letters, numbers, and other characters that are used to express the contents of a given dataset. Your computer interprets the binary information (remember those bits and bytes, the 1s and 0s, from Computing 101 class?) that makes up the contents of the dataset into its corresponding characters. The binary code 0100100, for example, is interpreted as the letter U. If it’s in a digital format, it’s using a character set, and datasets from VertNet are no exception.

Datasets downloaded from VertNet use a character set known as UTF-8 — the most common character set used on the World Wide Web. UTF-8 encodes all of the characters in it’s character set using eight bit clusters. For example, the letter U, using UTF-8, is encoded as 00100100.

So why should you care?

Well, it’s simple. When you open a TXT or CSV file in an application, such as Excel, you may have to tell the software what encoding to use. If you select the wrong encoding, say you choose UTF-16 or UTF-32 instead, there is a good chance that many characters in the dataset, especially special characters such as umlaut (ü) or tilde (ñ) could be lost or transformed into the incorrect character. If that happens, your dataset is no longer the same as the content provided by Vertnet and you will have no way of knowing how much things have changed making your dataset unreliable for research.

So, as you work through the steps for Excel below (or for another spreadsheet application), please keep these things in mind. Now, back to the opening a file in Excel.

The Steps

If you have Microsoft Products, your VertNet download can be opened in Excel; if you are using Numbers for Mac or Calc in Open Office the following process may be similar, but we recommend you consult your product help documentation.

  1. To begin, open Excel first, before you open the file (the order of operations is important here for step #4 to be successful).
  2. Next click on the File Menu and then select Open.
  3. You will be prompted to select the file you want to open. Locate your file, select it, and then click Open.
    • Steps 1-3 should cause Excel to start it’s File Import Wizard, so you should see a new dialog box pop-up after you click to open your file. You can then step through the wizard selecting the appropriate options. The steps are listed below. You can watch a video of this process (QuickTime, 122.4MB), if you want before you start clicking buttons. The video demonstrates the process using Excel 2011 on a Mac. There is no sound with the movie.
  4. On the first screen of the File Import Wizard choose Delimited. Change the File Origin to UTF-8.
  5. Click Next.
  6. Now Excel will try to recognize the delimiter. If it does not, choose Tab, and change the Text Qualifier to None.
  7. Click Next.
    • There is an Advanced option (button) on the next screen that will allow you to leave values as numbers. We do not recommend this. Instead we recommend you set each field as a text field. Most will be text already, but Excel will try to interpret numbers and dates and Excel does not always do this correctly.
    • To change them all to text on the Mac, select all of the columns presented — you can do this by selecting the first column, then pressing and holding down your Shift key. Keeping the Shift key down, scroll to the right and click the last column. The result should be that all columns are selected. You can let go of the Shift key after you see everything has been selected.
    • Click the Text option from the options at the top.
    • If you are using Excel on a PC, you just have to move from column to column selecting Text from the options above.
  8. Click Next/Finish and your file will open.
  9. Adjust columns as necessary to see all the values.
  10. Save your file.
    • At this point, it would be best to save as an Excel file or you will have to repeat the Wizard options the next time you try to open the file.

If you have any questions about this document, please contact VertNet's support team.

Visit our Help page for more resources created for the VertNet project.

Orig Release, 26Sept2014 (David Bloom and Laura Russell)