Forms & Reports:
Import Spreadsheet Data

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016


When you have some data already entered somewhere, it is useful to import it directly into Access. It saves a lot of work and avoids the errors that may occur when re-entering data.

Access has a wizard to help you import from a spreadsheet, like Microsoft Excel. Each row must be one complete record.

Diagram: Excel into Access

Access imports a spreadsheet as a table IF
each row is a separate record.


Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries

Project 4: Forms & Reports Arrow: subtopic open
    Import/Export/Link Arrow: subtopic open
    Icon: StepImport Spreadsheet
    Icon: StepImport Text File
    Icon: StepImport Access Objects
    Icon: StepLink
    Icon: StepExport from Access
    Designing FormsTo subtopics
    Designing ReportsTo subtopics
    Special Forms & ReportsTo subtopics
    Summary
    Quiz
    ExercisesTo subtopics


Search  
Glossary
  
Appendix



Methods of Importing from a Spreadsheet

You import from inside the database where you want the new data/object. 

  • Copy and Paste from spreadsheet:
    Copy cells from a spreadsheet and paste into a Access datasheet. If you are appending (adding new records) to an existing table, the data must match the existing field order and the data types. If you are creating a new table, you can paste directly into a blank datasheet and then name and configure the fields.
     
  • File types for importing spreadsheet dataMenu:
      File | Get External Data | Import  - The Import dialog appears, which looks like an Open dialog.
    Choose one  of the file types for spreadsheets (Microsoft Excel, Lotus 1-2-3).
     
    Select a file and click the Import button. The Import Spreadsheet Wizard starts. You can choose to import a whole sheet from the workbook or a named range. You can choose to create a new table or append the records to an existing table.
     

Import Errors = Paste Errors: If some or all of the records fail to import properly, they will be saved in a new table called Paste Errors. A message box will tell you how many records failed. Most errors occur when appending records to an existing table. You may be able to tell what the problem was just by inspecting the records in the Paste Errors table.

Common Causes of Paste Errors:

  • Different table structure: Missing or extra fields
    Example: Source might have a person's name in 1 field while the destination table use 3 fields
  • Mis-matched data types:
    Example: Source may have zip codes in a Number field while the destination table uses a Text field.
  • Different field names:
    Example: Source may use FirstName, without a space, while destination uses First Name, with a space, or some other name entirely.
  • Field order: If the first row of imported data does not contain the field names, then the fields must be in the same order as in the Access table.
  • Duplicates in primary key: Imported data may have duplicate values in the field that is the destination table's primary key or does not allow duplications.

Appending Data to Existing Table

The Import Spreadsheet Wizard appears to let you append records to an existing table, but it is not as easy as it looks. The data that you are appending must include the headings as the first row. Normally your new data is at the bottom of a set of records, not up at the top underneath the column headings. Problem! Excel will let you create a named range with these nonadjacent cells, but Access won't import such a range! Frustrating!

Methods for Appending Spreadsheet Data:

  • Import a named range or a sheet as a new temporary table, create an append query or copy and paste to append the records to another table, then delete the temporary table.
      OR
  • Create a separate sheet in the Excel workbook that holds only the column headings and the data to import. Let the Import Spreadsheet Wizard append the data to an existing table.

Named Range: australiaFor Your Information:

How to name a range in Excel:
Select the range of cells and in the Name box at the upper left of all the cells, type the name you want. The name can use only letters, numbers, periods and underscore characters. No spaces!
 

How to delete a name from the list: (It's not obvious!)
From the Excel menu, select Insert | Name | Define. Select the name and click the Delete button.
 

Icon: Step-by-Step 

Step-by-Step: Import Spreadsheet Data

 Icon: Step-by-Step

What you will learn:

to use Import Spreadsheet Wizard:
    to import data to a new table
    to append data to an existing table
to copy and paste cells from spreadsheet

Start with:  Class disk, resource files

You will create a new database for World Travel Inc. In the last project you did 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!)

Create New Database

  1. Class diskCreate a new blank database and name it worldtravel.mdb and save it to your Class disk in the databases project4 folder. (Create the folder if necessary)

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. Menu: File | Get External Data | Import...From the menu select File | Get External Data | Import 
    The Import dialog appears.
     
  2. Dialog: Import - Staff.xlsChange the File of type box to Microsoft Excel (xls).
     
  3. Navigate to where you stored the resource files and select the file Staff.xls.
     
    Icon: TroubleProblem: You do not see the file Staff.xls
        
    Either you did not change the file type to xls or you are not looking in the folder where you put the resource files. If necessary, download the resource files again.
     
  4. Dialog: Import Spreadsheet Wizard - step 1: Which worksheet or range?Click on the button Import.
    The Import Spreadsheet Wizard appears.

    Notice that you can choose to import any sheet that is in the workbook or any named range. Flexibility!
     
  5. Click the button Next.
     
  6. Dialog: Import Spreadsheet Wizard - step 2: First Rows Contains Column HeadingsIn 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.
     
    TipFor some spreadsheets the column headings may not be in the row directly above the data that you are importing. You could create another sheet that links to the original cells and arranges the data better for importing.
     
  7. Click the button Next.
     
  8. Dialog: Import Spreadsheet Wizard - step 3: Store data in new tableIn the third step of the wizard, select to store the data "In a New Table". This database does not have any tables yet.
     
    Note that you can choose to append the imported data to an existing table here.
     
  9. Click the button Next.
     
  10. Dialog: Import Spreadsheet Wizard - step 4: select fields, data type, indexIn the fourth step of the wizard you can select which fields to import and change some of their properties. You could wait to change the properties inside Access.
    The first field is already selected for you.
     
    Change the following:
    StaffID - Indexed = Yes (No Duplicates)
     
  11. Click the button Next.
     
  12. Dialog: Import Spreadsheet Wizard - step 5: primary keyIn the fifth step of the wizard, select to "Choose my own primary key, StaffID".
     
  13. Click the button Next.
     
  14. Dialog: Import Spreadsheet Wizard - step 6: name the tableIn the sixth step of the wizard, name the table Staff, which is the default name.
     
  15. Click the button Finish.
    Message: Finished importingA message box appears that tells you that the wizard has finished importing.
     
  16. Click on OK.
    The new table was created but it does not open automatically.

Modify Table Design

The Import Spreadsheet Wizard did a good job of bringing over the data, but the table it creates is a real hog for disk space! There are also some other properties that you should modify.

  1. Open the table Staff in Table Datasheet View and view the fields. Looks good!
    Table Datasheet View: Staff - after import
     
  2. Icon: Design Switch to Table Design View.
     
    Table Design View: Staff table - StaffID as Number typeWhoops. The StaffID field is the primary key but the data type is Number. That should be AutoNumber. In the wizard you chose to use an existing field as the primary key but Access can not change a field that has data in it to an AutoNumber field.
     
  3. Change the Data Type for the field StaffID to AutoNumber.
    Whoops. A message box appears. Access won't let you change an existing field to AutoNumber because there are already values in the table. Even if there were no values in this field, Access would balk.
    Message: Once you enter data in a table, you can't change the data type of any field to AutoNumber
    TipYou might not want to import a field that you want to be an AutoNumber field.  You are going to have to recreate it anyway.
     
  4. Click on OK to close the message.
     
  5. Select the StaffID field and delete it.
     
  6. Add a new field with the same name at the top of the list, but select AutoNumber as the data type and make it the primary key.
     
  7. Table Design View: Staff - after import and AutoNumber createdInspect 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!)
     
  8. Change the following field sizes and/or data types:
    Prefix, Suffix:  5
    FirstName, MiddleName, Nickname:  20
    LastName: 30
    Title: 20

    DivisionID: 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
    data type
    PhotographOLE Object data type
    Notes: Memo data type
     
  9. Icon: Save Save the table. You will get a warning 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! )

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.

  1. In Access, from the menu select File | Get External Data | Import 
    The Import dialog appears.
     
  2. If necessary, in the Import dialog, set the Files of Type to "Microsoft Excel" and navigate to where you saved the resource files.
     
  3. Select Staff-updated.xls and click the Import button.
    The Import Spreadsheet Wizard opens.
     
  4. Dialog: Import Spreadsheet Wizard - step 1: Show Named RangesIn 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. Dialog: Import Spreadsheet Wizard - step 3: only choice is 'In a New Table'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.
    In step 3 of the wizard, you only have the choice of a new table. As discussed above, without the headings, Access does not know how to match the fields to those in an existing table.
     
  8. Click the Next button.
     
  9. Import Spreadsheet Wiard: Range - Do not import fieldsIn step 4, select the first column and click the box 'Do not import field (Skip)'
     
    These values will not be used in 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.
     
  10. Import Spreadsheet Wizard: Step 5 - primary keyIn step 5 of the wizard, choose "No primary key."
    The temporary table does not need a primary key.
     
  11. Click the Next button.
     
  12. Import Spreadsheet Wizard: Range - name of tableIn step 6 of the wizard, accept the name "australia" for the temporary table.
     
  13. Click on Finish.
     
  14. Database Window: australia table createdSwitch to the Database Window and look at the tables. 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 is a mis-match 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.
    Table Datasheet View: australia with all records selected
  3. Copy. ( CTRL + C or Copy button or from the menu Edit | Copy)
     
  4. Open the table Staff in Datasheet View.
     
  5. Select the bottom row, with the * at the left.
     Table Datasheet View: Staff - with bottom row selected
  6. Paste. (CTRL + V or Paste button or from the menu Edit | Paste)
    Whoops. An message appears that says the value is not valid for this field.
    Message: The value you entered isn't 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.
     Message: Do you want to suppress further error messages telling you why records can't be pasted?
    TipError 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.
    Message: Records that Microsoft Office Access was unable to paste have been inserted into a new table called 'Paste Errors'.
    TipPaste Errors table: Each time you have paste errors, the Paste Errors table is deleted and re-created.
     
  9. Click on OK.

    TipNumber 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.

    TipChecking 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. Mis-matched 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 mis-typed 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. For example you could figure out what the missing State was from the postal code.
     

  10. Open the table australia in Table Design View.
     
  11. Add a new row at the top of the list.
     
  12. Name this new field ID and make it an AutoNumber field.
     
  13. Icon: Save Save the table and switch to Table Datasheet View.
     
  14. Select all four records and Copy.
     
  15. Switch to the Staff table and select the bottom row.
     
  16. Paste.
    A message tells you how many records you are about to paste.
    Message: You are about to paste 4 record(s).
     
  17. Click on Yes.
    Success!
    Table Datasheet View: Staff - after pasting new records
  18. Close the table and switch to the Database Window.
     
  19. Delete the tables australia and Paste Errors.
    You are left with the Staff table which now has 13 records.

TipAppend Query: When you are appending data that has a different number of fields than the destination table, an append query works better than copy-and-paste. With an append query you choose which fields to append and you match them to the correct destination field. Just omit any incoming fields that do not have a matching destination field.