|
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.
- Menu:
|| - 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.
For 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.
|
Step-by-Step: Import Spreadsheet Data |
 |
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:
,
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
-
Create
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.
-
From
the menu select||The Import dialog appears.
-
Change
the File of type box to Microsoft Excel (xls).
- Navigate to where you stored the resource files and select
the file Staff.xls.
Problem:
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.
-
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!
- Click the button Next.
-
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.
For 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.
- Click the button Next.
-
In
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.
- Click the button Next.
-
In
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)
- Click the button Next.
-
In
the fifth step of the wizard, select to "Choose my own primary
key, StaffID".
- Click the button Next.
-
In
the sixth step of the wizard, name the table
Staff, which is the default name.
- Click the button Finish.
A
message box appears that tells you that the wizard has finished
importing.
- 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.
- Open the table Staff in Table
Datasheet View and view the fields. Looks good!

-
Switch to
Table Design View.
Whoops. 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.
- 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.
You might not want to import
a field that you want to be an AutoNumber field. You are going to
have to recreate it anyway.
- Click on OK to close the message.
- Select the StaffID field and
delete it.
- 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.
-
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!)
-
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
Photograph: OLE Object data type
Notes: Memo data
type
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.
- In Access, from
the menu select||The Import dialog appears.
- If necessary, in the Import dialog, set the Files of Type to
"Microsoft Excel" and navigate to where you saved the resource files.
- Select Staff-updated.xls and
click the Import button.
The Import Spreadsheet Wizard opens.
-
In
step 1 of the wizard, select Show Named Ranges and select
australia.
The grid shows 4 records and no column headings.
- Click the Next button.
-
In step 2 of the wizard, do NOT check the box for First Rows
Contains Headings, since that is not true this time.
- 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.
- Click the Next button.
-
In
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.
-
In
step 5 of the wizard, choose "No primary key."
The temporary table does not need a primary key.
- Click the Next button.
-
In
step 6 of the wizard, accept the name "australia" for the temporary
table.
- Click on Finish.
-
Switch
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!
- Open the table australia in
Datasheet View.
- Select the 4 records by dragging down the record selector
boxes at the left.

- Copy. ( CTRL + C or Copy button or from the menu Edit | Copy)
- Open the table Staff in
Datasheet View.
- Select the bottom row, with the * at the left.

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

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!)
- 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!
- 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.
- 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. 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.
- Open the table australia in
Table
Design View.
- Add a new row at the top of the list.
- Name this new field ID and make it an
AutoNumber field.
Save the table and
switch to Table Datasheet View.
- Select all four records and Copy.
- Switch to the Staff table and select the bottom row.
- Paste.
A message tells you how many records you are about to paste.

- Click on Yes.
Success!

- Close the table and switch to the Database Window.
- Delete the tables australia and
Paste Errors.
You are left with the Staff table which
now has 13 records.
Append 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.
|