ODE Interactive Web Center

Procedure for importing delimited data into a Microsoft Excel spreadsheet

ODE will not provide technical support - this guide is simply a courtesy for Web visitors who wish to import delimited data into a spreadsheet. ODE does not endorse the Microsoft product over others, but it happens to be included in the standard build for computers in the Ohio Department of Education.  This guide assumes the reader has a basic level of computing experience and is using Microsoft Excel 97 or higher.

Step 1: After extracting the data from the Ohio Educational Directory or other Ohio Department of Education delimited data, save as a text file on a convenient drive.

Step 2: Open Microsoft Excel.

Step 3: Go to "File" and select and open. In the open dialog box, be sure that it is pointing to the same directory where you saved the file in Step 1 (see the top text box to the right of the words Look In). Be sure that the Files of Type text box on the bottom left says All Files. If both of those are correct, you should see the name of the file on the list in the middle of the dialog box.

Step 4: Text import wizard Page 1 of 3 appears. Choose "delimited", one of the two top left radio buttons. In the middle of this page, there is a box that says "Start Import at Row" and defaults to 1. Click on the up arrow on the right and change it to 2; the first row is a title and will create problems in lining up the fields if you do not do this. Then click "next".

Step 5: On the Text Import Wizard, page 2 of 3, the top left box has check boxes under the section "Delimiters." Choose the one you used when you created the file on the Web site. In the Data Preview area, you should see columns of data separated by vertical lines. These lines should line up immediately to the left of the first character in each field. If not, click on the back button and make sure that you chose the appropriate delimiter and that you started at row 2. Then click "next"

Step 6: You are now viewing screen 3 of 3 in the text import wizard. The word "GENERAL" will appear over each data column and the left-most column will be highlighted. There are radio buttons in the box on the upper right under Data Column Format that allow you to make decisions about each column. If you want to keep the IRN on your data file, make sure that column is highlighted in the Data Review section and click the radio button in front of "Text"; if you do not do so, Excel will think that this is a number and drop leading zeroes. This applies to phone numbers, zip codes and other data that look like numbers but are identifying rather than counting or calculating fields. Columns that contain all alphabetic data or mixed numeric and alphabetic can be left as "General" or changed to "Text". If you do not want a column, you can highlight it and click on the radio button in front of "Do Not Import:" (the e-mail address would be one that is not worth importing). If you chose schools, you want to make sure that you leave the estimated enrollment and estimated teacher numbers as general. If you have more than one adjoining column that you want to apply a radio button choice to, you can hold down the shift key and highlight them all. Then click on the appropriate radio button. When you are finished making your selections, click the "Finish" button.

Step 7: Now you should see the file in spreadsheet format. It will probably look as though some of the data are cut off but this is only a visual effect. You can rectify it by selecting all the columns, clicking on the menu option Format, then Column, then Auto Fit. This will expand the column width so that all data can be viewed. You can sort the data any way you wish, delete rows or columns or perform any other activities that are allowed in spreadsheet manipulation. Save the resulting spreadsheet as an Excel spreadsheet or Macintosh text.

NOTE: If you want to use a generic addressee, e.g. Curriculum Coordinator or Transportation Director, you can add a column in EXCEL in which every row has that title. However, it would be easier to create that addressee in WORD.

If you want some of the counties, you could sort by county and delete the ones you don’t want in EXCEL. However, it would be easier to filter records in WORD MailMerge.

To generate data for address labels, please click here.

Updated Tuesday, August 22, 2006 10:48 AM