Jan's Working with Databases

Step-by-Step: Import Spreadsheet Data

---

What you will learn:

to import spreadsheet data: new table with wizard to modify table design: field sizes, data types, Attachment field to import spreadsheet data: Named range to append data with copy and paste

---

Start with:  , resource files

You will create a new database for World Travel Inc. Previously you created a database of just the work projects for this company. This time you will create a database that includes information on staff, clients, and trips. (This database will not be nearly as complex as a commercial database for such a purpose!) You will use this database in the Forms and Reports project, too.

Create New Database

1. Create a new blank database and name it worldtravel-Lastname-Firstname.accdb (using your own first and last names) and save it to your Class disk in the databases project3 folder.

---

Import Spreadsheet Data: New Table

There is a handy wizard for importing spreadsheet data. It walks you through a number of choices. Happily your resource files have a spreadsheet that is ready to be imported. In the real world you might need to do some rearranging in the spreadsheet first.

1. From the External Data ribbon tab in the Import and Link tab group, click the Excel button.
   A dialog opens, Get External Data - Excel Spreadsheet.

   
   

2. Click the Browse button and navigate to where you stored the resource files.
   Only Excel files will show in the File Open dialog.
   Select the file Staff.xls or download it. (Pay attention to where you save it!)

   
   

   Problem: You do not see the file Staff.xls
   Either you did not download the resource files or you are not looking in the correct folder. If necessary, download the resource files again or the single file Staff.xls.

3. Verify that the radio button is selected for "Import the source data into a new table in the current database."
   This is the default choice.
   

4. Click on the button OK.
   The Import Spreadsheet Wizard appears.

   

   Notice that you can choose to import any sheet that is in the workbook or any named range. Flexibility!

   This spreadsheet only has one sheet and no named ranges.
   

5. Click the button Next >.
   

6. In the second step of the wizard, check the box "First Row Contains Column Headings".
   The dialog display changes to put the column headings as headings instead of as record 1.

   

   Arrange BEFORE importing: For some spreadsheets the column headings may not be in the row directly above the data that you are importing. Or you may not want to import the whole sheet. Before importing, you should create a copy or a linked sheet that has only the data that you want and has the column headings in the first row with no blank rows.

7. Click the button Next >.
   

   In the third step of the wizard you can select a field and change some of the field properties: field name, data type, index, or to not import the field at all.

   

   You could also wait and change the properties inside Access after the import is done.
   The first field is already selected for you.

8. Change the form to show the following:
   StaffID - Indexed = Yes (No Duplicates)
   

   This field will be the primary key for the new table so duplicates cannot be allowed.

9. Click the button Next >.
   

10. In the fourth step of the wizard, select to "Choose my own primary key, StaffID".

    
    

11. Click the button Next.
    

12. In the sixth step of the wizard, name the table Staff, which is the default name.

     

13. Click the button Finish.
    A final step appears that tells you that the wizard has finished importing.

    

    If you plan to import this data regularly, you can save these step so that it will be faster the next time. You won't be doing this one again.
    

14. Click on Close.
    The new table was created but it does not open automatically. Your database lists two tables - Staff and Table1.
    

---

Modify Table Design: Field Sizes, Data Types, Attachment Field

The Import Spreadsheet Wizard did a good job of bringing over the data, but the table it creates uses far more disk space than necessary! There are also some other properties that you should modify.

1. Open the table Staff in Table Datasheet View and scroll to view the fields.

   

   There are a lot of fields, but the table looks good!
   

2. Switch to Table Design View.
    
   Whoops. The StaffID field is the primary key but the data type is Number instead of AutoNumber. That is not always bad but this time an AutoNumber is exactly what you need.
   

3. Change the Data Type for the field StaffID to AutoNumber.
   Whoops. Did you remember that we cannot do this? A message box appears.

   

   Access won't let you change an existing field to AutoNumber. Even if there were no values in this field, Access would balk.

   Of course a primary key does not HAVE to be an AutoNumber data type. It just has to have unique values. But in this case, it really should be.
   
   Skip AutoNumber fields when importing: You are going to have to recreate the field anyway.
   

4. Click on OK to close the message.
   
5. Select the StaffID field and delete it.
   A message asks if you really want to delete this.
   
6. Click Yes.
   Another message points out that you are trying to delete the primary key.
7. Click Yes.
   
8. Save the table since you deleted a field.
   Access can get confused if you delete a field and then create a new one with the same name, which you are about to do. Access thinks you have tried to name two fields with the same name. Ouch!
9. Add a new field named StaffID to the top of the list with the Add Row or Insert Row button.
10. Select AutoNumber as the data type and make it the primary key.
11. Save the table.

12. Inspect the field properties for the remaining fields.

    All of the text fields are set to a field size of 255! Far too large!!  We can make better guesses than that for how large the values are likely to be. For this database we will need to allow enough space for international phone numbers and postal codes and also for long last names with hyphens, like Rastonkowsky-Michaelson
    (People are just not thinking about the comfort and ease of database programmers when they choose such names!)
    

13. Change the following field sizes and/or data types:
    Save your table along the way! Don't wait until you have made all of the changes.

    You will get a warning message about reducing the field sizes. It's OK. The sizes are larger than the current data. (Rats! I could have included another error here for your education. Ah well. Perhaps later! )
    
    Prefix and Suffix:  5
    FirstName, MiddleName, Nickname:  20
    LastName: 30
    Title: 20
    DivisionID and Location ID: Number data type with field size Integer.
    Address:  50
    City: 30
    State: 20
    Region: 20
    PostalCode: 15
    Country: 30
    HomePhone, WorkPhone, MobilePhone, FaxNumber, AlternativePhone: 20
    EmailName: 20
    EmailExtension: 50
    Birthday - no changes!
    Nationality: 50
    EmrgcyContactName: 50
    EmrgcyContactPhone: 20
    DateUpdated - no change
    DateHired - no change
    Hobbies - no change
    HealthIssues: Memo or Long Text data type
    Notes: Memo or Long Text data type

14. Save.
15. Delete the Photograph field and recreate it on the row above the Notes field as an Attachment data type.
    Access will not let you change a field to an Attachment data type.  No photographs were imported anyway.
16. Save and close the table.
    

Problem: Message says you cannot use the same name for two fields
You did not save after deleting a field. Access gets confused when you delete a field and create a new one with the same name unless you saved after deleting.
Solution: Close the table and reopen. Make the changes that were not saved. Be sure to save the table after deleting a field.

---

Import Spreadsheet Data: Named Range

Our spreadsheet has been updated with some new data for the Australia office. You need to append those new records to the existing Staff table.

You will import a named range as a new temporary table and then, in the next section, you will use Copy and Paste to append the records to the Staff table.

Why not use the Append choice in the Import Wizard? The named range does not include the field names, so the import will fail.

1. In Access, from the ribbon tab External Data in the Import & Link tab group, click on the button Excel .
   The Get External Data dialog appears.  
2. Select Staff-updated.xls in the resource files you downloaded and click the OK button.
   The Import Spreadsheet Wizard opens.
   
3. Choose to import as a new table.
   

4. In step 1 of the wizard, select Show Named Ranges and select australia.
   The grid shows 4 records and no column headings.

    

5. Click the Next > button.
   

6. In step 2 of the wizard, do NOT check the box for First Rows Contains Headings, since that is not true this time.

   
   

7. Click the Next > button.
   

8. In step 3, select the first column and click the box 'Do not import field (Skip)'.

   

   These values will not be used when you append these records to the Staff table, which already has an AutoNumber field as the primary key.

   This is actually going to be a bad choice, but we need to see what happens when the fields do not fit together right.

9. Click the Next > button.
   

10. In step 4 of the wizard, choose "No primary key."
    The temporary table does not need a primary key.

    

    Field1 is still showing even though you told Access not to import it. Confusing!
    

11. Click the Next > button.
    

12. In step 5 of the wizard, accept the name "australia" for the temporary table.

    
    

13. Click on Finish.
    
14. Click on OK to close the import wizard without saving the steps.
    
15. Inspect the Navigation Pane.
    You have a new table.
    So far, so good!
     

---

Append Data: Copy and Paste

The easiest way to append data is to copy and paste. The fields must be in the same order and the data types must match.

A common problem is pasting with the fields not in the same order, resulting in a mismatch of data types. Worse would be a paste that is apparently successful but actually put data into the wrong fields! You must check the order of fields carefully!

First you will try pasting data without a matching AutoNumber field so you can see what paste error messages look like. There are a lot of them!

1. Open the table australia in Datasheet View.

   
   

2. Select the 4 records by dragging down the record selector boxes at the left.
   
3. Copy.
   (CTRL + C or Copy button on the Home ribbon tab)
   

4. Open the table Staff in Datasheet View.

   
   

5. Select the bottom row, by clicking the * at the left.

   If you don't select the whole row, the paste will always fail.

6. Paste.
   (CTRL + V or button on the Home ribbon tab)
   Whoops. An message appears that says the value is not valid for this field.

   

   The problem is the first field, which is an AutoNumber field. Your pasting is trying to put the text values from the first field (Prefix) there.

   The easiest solution is to modify the table australia to have its own AutoNumber field.

   (You would not have had a problem if you have imported the StaffID field from the spreadsheet even though there was data in that field. Go figure!)

7. Click on OK.
   Another message appears that asks if you want to suppress any more error messages. There will be at least one for each record in this case.

   Error messages: It is a good idea to see what the error messages say about the first record that failed to paste. But it gets old very quickly when there are many messages for many records!
   

8. Click on Yes.
   Another message appears, telling you that records that could not be pasted will be put into a new table called Paste Errors.

   

   Paste Errors table: Each time you have paste errors, the Paste Errors table is deleted and re-created.
   

9. Click on OK.

   Number of Errors: There are often multiple errors. One field that is out of order can cause a whole cascade of errors. You may have to go through several repair steps.

   Checking Errors: Read the messages carefully! They won't tell you exactly what went wrong, but they do offer some clues. Open the Paste Errors table and inspect those records for common problems. Mismatched fields, data types, and data sizes are most often the problem.

   When importing data from other sources, you may find that some records have been mistyped so that data was entered in the wrong fields. So confusing! Perhaps the State was skipped and the postal code was entered in that field instead. If you are appending data, the destination table may have validation rules that block the pasted data in such a case. Sometimes you can repair the data before importing. For example you could figure out what the missing State was from the postal code.
   

10. Open the table australia in Table Design View.
    If you get a message about a lot of data on the clipboard, you can let Access erase it.
    
11. Add a new row at the top of the list of fields.
    
12. Name this new field ID and make it an AutoNumber field.
    
13. Save the table.
14. Switch to Table Datasheet View.
    
15. Select all four records and Copy.
    
16. Switch to the Staff table in Datasheet View and select the whole bottom row by clicking the * at the left.
    
17. Paste.
    A message tells you how many records you are about to paste.
    
     

18. Click on Yes.
    Success!

    But wait! The StaffID numbers skipped some numbers.

    Gaps in ID numbers: If you try to paste records and the paste fails, the numbers that should have been used for an AutoNumber field are treated as 'used'. When you add a new record later, there will be a gap in the numbers.

19. Close all of the tables.
    A message box appears asking if you want to keep the Clipboard contents. You can choose No. You pasted that already.

20. In the Navigation Pane, delete the tables australia, Paste Errors, and Table1.
    You are left with the Staff table in the database worldtravel-Lastname-Firstname.accdb. The table now has 13 records, autonumbers 1-9 and 14-17.

Append Query: When you are appending records that have a different number of fields than the destination table, you should use an append query instead of copy-and-paste. With an append query you choose which fields to append and you match them to the correct destination field. You can omit any incoming fields that do not have a matching destination field.