Activity 1: Importing Spreadsheet Data Into a Database

ISP 121, Winter 2007, Activity 1

ISP 121, Winter, 2007

Section 201 (TTh, 10:10 – 11:40)

Section 202 (TTh, 11:50 – 1:20)

Activity 1: Importing Spreadsheet Data into a Database

We will first see how to create a database table from a flat set of records. In this exercise, the records will be stored in a text file (.txt extension) and in an Excel spreadsheet (.xls extension). Our database system is Microsoft Access, which makes it very easy to import data in these forms.

First create a Word document for storing your results. Call it Activity1.

Activity 1a

Creating the database

1. Go to the Excel Files section of the QRC website (qrc.depaul.edu).
2. Save the file StateTemperatures.xls to your flash drive.
3. Start up Access.
4. Create a new blank database and call it Activity 1a.
5. Pull down the File menu and select Get External Data and then Import.
6. Browse to where you stored the temperature file.
7. An import wizard will appear. Follow its suggestions. Import the Data worksheet only. The first row does contain column headings. Import into a new table (call it Temperatures). Don’t create indices or a primary key.
   Question: If you had wished to create a primary key, is there a field you could have used? Why or why not?
   
8. Once the spreadsheet has been imported into the database, be sure to check the file something$_ImportErrors (if one exists) and address any errors that occurred during importing.
   

Finding and filtering records

1. Pull down the Edit menu and select Find (or type Ctrl-F).
2. Determine whether there are any states with an average temperature of 90 degrees. If so, list in your Word document the state name and the month when 90 occurred. Look in the entire table and match the whole field.
3. Now do the same for 85 degrees.
4. Repeat for 0 degrees.
5. Locate a field with the value 1954 in it and highlight it.
6. Pull down the Records menu and select Filter and then Filter by Selection.
7. Select the first ten records and copy them into your Word document.

Activity 1b

Creating the database

Follow the steps under Creating the database in activity 1a but this time do it for the file Book Stores.xls. Name the database Activity 1b. Note that the spreadsheet has multiple worksheets. Import the data from the CA (California) worksheet only. As above, the first row contains column headings, you should import into a new table, and don’t create indices or a primary key.

Again: If you wanted to create a primary key, could you have used the field StoreID? Explain.

Fixing import errors

Once the spreadsheet has been imported into the Activity 1b database, be sure to check the tableCA$_ImportErrors (if it exists) and address any errors that occurred during importing.

How might you correct the import error(s)? You don’t have to correct the error, just tell me how you might do it.

Finding and filtering records

Use Find to find all the bookstores in San Jose. Then useFilter to find all the bookstores in San Jose. Write or copy the names of those stores into your Word document.

Printed January 2, 2007