Importing Data

Modules >>
Excel II >>
Importing Data

In the Excel I module, you learned how to enter data into a spreadsheet. There are plenty of data files out there, however, that you may wish to import into your Excel program. In this module, you will learn how to import data.

Spreadsheets can be used for much more than creating an inventory list or recording marks; it can be used to analyze data. There are lots of different places from which you might encounter data files - from the data library and Statistics Canada, for example. These data can be used with your students in the classroom, where they can learn how to analyze data. For your assignment, you are asked to import data from the Statistics Canada website. Details on where to find data will be provided lower down on this page.

Statistics Canada
http://www.statcan.ca/

Just for your information, there is a support page for teachers who may be interested in using Statistics Canada data in their classroom. You may access this website at the following link:

Statistics Canada - Learning Resources for Teachers
http://www.statcan.ca/english/edu/teachers.htm

Now, to access data on the Statistics Canada website for your assignment, you must go to the following link. To open this page in a new window, right-click on the link and select "Open in New Window" (Netscape) or "Open Link in New Window" (IE):

Free Publications (some include data) - Statistics Canada
http://www.statcan.ca/cgi-bin/downpub/freepub.cgi

To import data, you must first search for a data file (as opposed to a .pdf or .html report).

To save time, I'll direct you to a data file that you can use for this assignment. Go to the title "Health" by clicking on the folder icon next to its title. The "folder" for that title will then expand to show the titles contained within it.
Click on "Health Indicators"
Click on "Volume 2001, No. 3 (December 2001)"
Click on "Data Tables"
Click on "Potential Years of Life Lost"
Click on the "CANSIM" button for potential years of life. This button is shown circled below:

A new window will open that requires you to indicate what fields you wish to include in the file you are about to download.
From the Geography menu, select just the provinces and territories (i.e., do not select "Canada" or any region within a province). To select more that one title, you will have to hold down the "Ctrl" button on your keyboard while you click your selections. When you get to Ontario, choose "Ontario by District Health Council" and ignore "Ontario by Health Unit"
From the Sex menu, select "Males" and "Females" from the list, but do not select "Both Sexes"
From the "Selected Causes of Death" menu, click the "Select All" button.
From the "Characteristics" menu, select "Potential years of lives lost"
Leave the drop-down menus for the years as defaulted.
Click "Continue"
The next window you see will ask you to confirm your choice. Choose "Option 1" by clicking on the "Table" button.
The following window will show a long table containing the various data you selected. Scroll to the bottom of this window and click the "Continue" button.
The data is now ready for downloading. You must specify what format in which the data is to be provided. Since we will be importing this data into a spreadsheet, go to the "Screen Output" drop-down menu and select "PRN (Tab-separated values) file for spreadsheet use"
Leave the remaining menus with their default values and click the "GO" button to begin the download.
Netscape users will first need to choose the "Save File" option from the first pop-up window. Another window will appear in which you will have to choose the folder in which you want to save the file. You must also give it a recognizable name. For example, you could name it XXXdata (with XXX being your initials). Be sure to remember where this file is located on your hard drive. Click "Save" when ready.
Next, open your Excel program.
Go to the "File" menu and choose "Open"
Locate your data file. You will notice that the extension ".TAB" has been added to it. This is because the data stored in this file is separated by tabs (yes, like the tab on your keyboard). The importing program will be able to separate rows and columns by the tabs used.
Click on the data file to select it, then click Open.
A "Text Import Wizard" will appear, which looks similar to the following

Leave the default values as is with "Delimited" selected and File origin as from a Windows computer.
Click the "Next" button
The following window will ask you to specify the type of character that is used to separate the data in your file. In this case, "Tab" must be selected as we are importing a tab-delimited file..
Click "Next".
The final window will ask you to specify the format for the data in each column. The data is defaulted to "General," which is fine for now, so click the "Finish" button.
Your data file should now appear in Excel! Some of the data will not be fully displayed as the columns may need to be widened, but it's there! To widen columns, simply drag the border next to the column label shown circled below:

Double-clicking this border will widen the column so the longest text within that column is fully displayed.

Now, save this file as an Excel file. Go to "File" and select "Save As." You may noticed that the "Save As Type" drop-down menu currently has "Text (tab delimited)" selected. Change this to "Excel Workbook" and name it "XXXdata" (without the quotes). You will need this spreadsheet for the following lessons.

Please continue to the next section on Viewing Spreadsheets.