Electronic Data Center : Infomation Services : Services

This page is deigned to help you transfer data on the web that is in a text format into more readable formats, such as Microsoft Excel. The example below is taken from the Federal Statistics site. You may click on the screen capture thumbnail to obtain a larger graphic. The example below is performed through a word application. However, Excel has several options as well for the conversion of text files (the text to columns option).

Suppose that you are interested in downloading information on 30-Year Constant Maturity (Interest) Rates. You have located this data on the FedStats page, for which there is a link under the Economics course materials on the EDC website. The data should appear in the following format:

Choose "copy" from the dropdown menu and select the data columns. You may also select the header that describes the data. Some of the examples below include the header.

Open up a Word application and select "paste" (or Control-V) from the edit drop down menu. The data should still be dispalyed in a two column format. [Note: you could also paste this data straight into an excel file--see note below for what to do afterwards].

You will now need to create a "csv" file that can be read into Excel. In the original text file, the data columns should be separated by an equal number of spaces (i.e., it is fixed width). Select (i.e., highlight) the space between the data columns and choose the "replace" option under the edit menu.

The replace function will open up a box with several "find and replace" options. In the "find what" field, click paste (this will automatically paste in the number of spaces between the data columns). In the "replace" field, type in a comma. Then click on "replace all". There should now be commas that separate the data in each column.

A prompt will appear indicating the number of replacements that have been performed and whether you want Word to begin searching from the beginning of the document. Go ahead and click "yes."

Open up Microsoft Excel and open up the text document. Automatically, Excel will open a box entitled "Text Import Wizard." This will be the first of three steps you will need to take in order to process the file in Excel. Step 1: Be sure that you choose "delimited" in the choices under "original data type." Delimited indicates that commas or tabs separate the columns in your data. You inserted such separations back in the word document. Click on "next." [Note: If you simply pasted the data into Excel, you should choose the "text top columns option" under the data menu. You will follow the same steps as the Text Import Wizard, except that you will choose "fixed width" instead of delimited in the first table]

Step 2: Under delimiters, be sure to check "comma." You will see a preview of how the data will appear in Excel. Click on "next."

Step 3: You really do not need to do anything here. You will agains see a preview of your data as it will appear in the Excel workbook. Click on finish.

You will now see your data in Excel worksheet format. You have actuallynow converted the text file into a comma-separated values file (.csv), which can be easily read by Excel.

Save the data file as an excel file (extension .xls). If you don't save the file as such, you will have to go through the text import wizard process each time you open the file!