An introduction to databases. Elections Canada.

1) Go to the Elections Canada website.

2) Click on the "Election Financing" link to the left-hand side of the Elections Canada logo.

3) Click on the "Contributions and Expenses (database)" link located just below the "General Information on Election Financing" banner.

4) Under the column head "Choose a period" select the link, "2007 and beyond" link. Choose the "40th general election," and then the "By contributor" option on the "Choose a Search Option" page.

5) On the top far right-hand side, you'll see two check boxes beside two choices: "Data as submitted" and "Data as reviewed by Elections Canada." The former is checked by befault.* Choose the "Data as reviewed by Elections Canada" by double clicking on that link. Once you double click, the check mark will appear in the box on the left.

6) Select the "By contributor" option at the bottom.

7) The next page is entitled "Search by Contributor" page. Select the box beside the "Select all classes" option in "Class of contributors." To select a province or territory, you would chose one from the menu. But for this exercise, we want all the provinces and territories, which we will obtain by leaving the box at it's default position, "Select a province/territory." Before obtaining the table by clicking the "Find contributors" tab, your screen should look like this. If it does, then you're on the right track and ready to click the "Find contributors" tab at the bottom.

08) The next page is entitled "Search by Contributor." Below that title, is a tab called "Download (tab-delimited file)." Click the tab.

09) You'll obtain a "File Download" box asking if "you want to open or save this file?"

10) Select "save."

11) You'll see a "Save As" dialogue box. Be sure to select the folder where this file will be saved, otherwise you will be unable to find the file when it comes time to import it into Excel.

13) The file's default name is "ReportDownload.txt." Rename it with something that makes the contents of the file clear. Be sure to keep the "txt" extension.

14) Open an Excel workbook. Go to the "Data" section of the broad ribbon across the top of the worksheet.

15) In the "Get External Data" tab on the left-hand side, you'll see a number of options. Go to the Get External Data on the left-hand side of the ribbon and select the "From Text" option.Your spreadsheet should look like this.

16) You'll see an "Import Text File" dialogue box. Go the the folder on your hard drive that contains the elections text file.

17) Clicking on the text file will produce a "Text Import Wizard" indicating the number of steps (3) you'll need to import the file.

18) Under the heading "Original data type" you'll see two options: "Delimited" and "Fixed width." Delimited is the Excel's default position. In this case the one you want to stick with the "Delimited" option.

19). Click on the "next" tab. The "delimiter" -- what separates the columns of information -- is a tab, which unless otherwise specified, is the import wizard's default position.

20) Select the tabs "next" and then "Finish." You'll see an "Import Data" box. Select the "OK" tab. Congratulations! Now you have imported your first file into Excel.

21) Save the Excel file in the same folder as the text file. Rename the first sheet ("Sheet 1" is Excel's default name) with a title. You can call it something like "Original version." Copy the entire worksheet by either using the keys "Ctrl" and "A" (for PC users), or by putting your mouse on the square to the right of the top of column A and above the first row. Using either method, will result in the entire table turning the colour blue.

22) Copy the table by using the keys "Ctrl" and "A" or right-clicking on your mouse ( again, for PC users ) and selecting the "copy" option.

23) Select "Sheet 2" and paste the table you just copied into that worksheet either by using the keys "Ctrl" and "V" or right clicking and employing "Paste" option.

24) Rename the worksheet to "working copy."

25) Now we will proceed to clean up the table.

26) Select the first two rows, right click on your mouse while holding down the "Ctrl" key (for PC users) to obtain the short-cut menu, and choose the delete option.

27) Before going any further, use the horizontal and vertical scroll bars to browse the material in the columns. You'll notice that there are many columns of information: Family Name; Initial; Given Name, etc.

28) When working with data in a spreadsheet, you only want to have one piece of information per column. In column D, there are three bits of information: Name of contestant; Contest date/Political party/Electoral district. Scrolling down the column, it's evident that there is no "Contest date." So we'll have to eliminate that label by placing your cursor on cell D1 and double-clicking. Doing so will place the cursor inside the cell, allowing you to edit highlighting "Contest date" In this case, we will delete "Contest date," giving us three titles -- Name of contestant, Political party and Electoral district -- to match the names in the column.

29) Now we want to split the information in column D into three separate columns: one for "Name of contestant." One for "Political party." And one for "Electoral district."

30) Before splitting up the information in column D, we'll have to insert to extra columns for the "Political party" and "Electoral district." Excel inserts columns to the left. This means in order to insert two columns, you must hightlight column E, right click to get the short cut menu, and select the insert option twice. You'll have to shorten the width of the two new columns by placing your cursor on the border between column D and E, the new blank column you've just inserted. Once placed on the border between D and E, your cursor will turn into a black cross. You can readjust the width by double-clicking with your index finger, or by holding down the mouse with your index finger ( for PC users ) and dragging the icon to your left, and then releasing the mouse when you've obtained the desired width. Once you've readjusted the columns, you should be able to see D, E and F on the same screen, with E and F being blank.

31) The result looks like this: I've adjusted the size of the new columns in order to fit everyting into the diagram you've just seen. You'll notice that to the right of column D, there are two new columns: E and F. They will eventually contain the columns for "Political party" and "Electoral district."

32) Now we're ready to split the columns. Highlight column D, and go to the "Text to Columns" option in the "Data Tools" tab.

33) Clicking on the "Text to Columns" option produces a "Convert Text to Columns Wizard".

34) As we saw when importing the text file, the dialogue box defaults to the "Delimited" selection. Hit the "Next" tab.

35) In this case, the delimitor is not a Tab, but foward slash "/". To obtain this delimiter, choose the "Other" category in the "Delimiter" section of the dialogue box, and type a forward slash into the box to the right of Other. You'll notice that choosing that delimitor has split the cells into separate columns -- just like magic! As was the case in step 31, I have adjusted the size of the new cells, something you'll have to do in order to fit all most of the cells onto one screen without having too much scrolling horizontally.

36) Take a look at column H. It, too, contains more than one column of information -- "Class of contributor" and "Part # of the return". Repeat the steps 30 to 35 to split the columns. In this case, you only need to add one extra column for "Part # of the return."

37) We're almost ready to begin filtering and sorting the data. But not before we do one more bit of clean up. Column G -- Date received -- contains the date the candidate received the contribution. The dates are aligned to the left of the column, which means that Excel reads the information as "text" rather than a dates. Dates and numbers are justified to the right of the cell. This is important because Excel will only perform math such as counting and grouping on date and number fields. So we will want to convert the information in the "Date received" column to a real date. The information is in a format that Excel doesn't recognize. The culprit is the period -- "." -- after the abbreviated date (Sept.21,2008). Deleting the period, would allow Excel to recognize Sept. 21, 2008 as a real date. To see what happens when you delete period, double click on cell G2 to put your cursor inside. Now delete the period and see what happens. Excel recognizes it as a date because the information is now justified to the right of the cell. But doing this for each cell in column G would take too long. Not to worry. We'll use the "Find and replace" option to locate all the periods in that column and delete them. To do this, highlight the column, and select the keys "Ctrl" and "F" (again, for PC users) simultaneously to obtain a dialogue box. Type a period -- "." -- to the right of "Find what." Select the "Replace" tab and leave the choice blank. In other words, don't type in anything. So what we're instructing Excel to do is this: find all the periods in the column we've hightlighted -- "G" -- and replace them with nothing, which is another way of deleting them. Hit the "Find Next" and then the "Replace" tab once or twice just to make sure we're doing it properly. Now, choose the "Find all" tab and then the "Replace all" tab. In a few easy steps, you've allowed Excel to recognize the information as dates.

38) Now we've cleaned up the data and are ready for sorting and filtering.

*This a preferrable option because it represents all the election finance returns that Elections Canada verifies, an exercise that is performed each Friday