Import Excel Data Into MS Access

This is the first in a series of video lessons where I demonstrate how you can share data between Excel and Access. In this lesson I show you how to Import, Append and Link Excel data to Access tables.

You initiate this process from inside Access. There are "Wizards" that guide you through each step in the process.

I invite you to visit my online shopping website - http://shop.thecompanyrocks.com - to view all of my videos and to explore the resources that I offer.

Danny Rocks
The Company Rocks

---

Closed Caption:

hello and welcome back to the tips of
time savers aren't any rocks and today's
lesson is the first in a series where I
will demonstrate how to integrate excel
data with an access database
we're going to be taking banking
transactions from two different sources
are original set of transactions as well
as a set that came from another source
which contains newer transactions notice
that while we're still here in Excel
that we come to the data tab and we have
a series of commands to get external
data from access and some other sources
but we don't have a set of commands for
sending information directly from excel
to access so our process will begin over
here in axis where we have set of
commands to both import as well as
export the data now before we begin this
process let's take a look at what we're
working with so over here in the banking
transactions notice that our top row has
the field headers that has the column
headers and they are clearly identified
as field headers because we set them off
with bold formatting
we've done the same over here with the
new transactions
now it's generally a good practice to
close down the file that you are going
to send to another program it's not
necessary but I generally recommend it
in today's lesson I'm going to keep this
file open and now i'm going to switch
over here to access so we have a new
database over here in axis but we don't
have any information in it yet
our table information is going to come
from excel so on the ribbon go to the
external data tab and in the import
group don't confuse it with export in
the import group we want to import
information from excel
now we'll walk through the wizard notice
over here that i have two choices i can
import the source data or i can link to
the original data source now we're going
to link later on in the lesson so I'm
going to choose to import and now let's
identify
that information is so it's in this
directory but we want to make sure that
we select the file that contains the
information so now that we have that
selected
let's click OK now remember in the Excel
workbook we have four worksheet so let's
identify the worksheet that we want to
import
so we have date source branch type and
amount
now notice over here in the date field
notice the serial numbers that we have
here if i switch back here to the excel
worksheet you see that these are dates
but remember that excels stores a date
as a serial number will format it to
show it as a date but it's stored as a
number
now the other feel that i want to draw
attention to is the amount of the amount
over here we want to make sure that it
contains values and not formulas
if you try to import an Excel worksheet
that contains formulas
don't do it that's a formula for
disaster so convert them to values
all right now our first row contains the
column headers yes let's take the next
step and now let's go through and define
the data type so over here for our date
it is going to be stored as a date or
time filled the text fields for source
and branch and type and then the amount
will come in as currency and again
remember it's a value and not a formula
take the next step
now here's a major difference between
excel and access access uses primary
keys access uses keys over here so that
it can relate one table to the next
let's take the default and let access
add a primary key to our table
click Next and now what do we want to
name it well by default it's going to
take the name of the worksheet tab that
we had so if it were sheet1 would bring
it in a sheet 1 so we have banking
transactions
let's click finish and now notice over
here that if we are importing
information from this excel worksheet on
a regular basis we could save this as
the definition so with savings time and
pretty much automate the process we're
going to say this is a one
off transaction so we won't do it now
let's come over here and take a look at
the table
so here is the primary key that's been
added notice that they're sequential
numbers is what's called and at auto
number and then we have our date and
remember they were stored as serial
numbers but overhear the displayed as a
date and we can change the format we
have our texts and over here we have our
currency with two decimal places
no coming down here we can see that
there are 678 records to come down to
the last record we click here there's
678 records and if we want to add a new
record
we can do that let's go back up here to
the top and now let's come in to the
design view for our access table so
moving over here into design view we see
that our new primary key is set up as a
to increment it set up as an auto number
and it is set up to be indexed so that
there are no duplicates allowed now
access ads this number automatically we
do not in this case
add that number now let's come back here
into table view right mouse click and
say let's go over here in the datasheet
view all right now let's close this and
what we want to do next is we want to
append our second excel worksheet so we
already imported the banking
transactions what we want to do over
here is take the new transactions and
append them to the table that we
established in access
so once again let's come over here in to
access we'll again go to the external
data tab of the ribbon we want to import
information from excel
now this case we already have a table in
our access database so notice that we
have a second choice over here
we want to in this case append
information from our data set to this
table if I had additional tables they be
in the drop down once again let's go
through and identify the exact file now
I have these as separate worksheets but
I collected them together into this one
book so over here I want to make sure
that i'm working with the correct file
click ok and once again we see the four
worksheets that we have here so new
transactions and again for headers over
here
click Next and now again the first row
contains the column headers click Next
where do we want to import where we are
pending it too
so now let's click finish and again we
have the the dialog box over here that
if we're doing this on a regular basis
we could save these import steps as a
definition so we don't want to do that
now remember we had 678 records before
we appended now we have 753 records
so there you see how easy it was to
append that information
all right now let's go back and let's
i'm going to demonstrate how we can link
the information to the original source
the excel worksheet so over here what
I'm going to do is I'm going to open up
another worksheet
i'm going to go to the office button and
i'm going to come over here and as the
separate worksheet i'm going to come
down here to the new transactions
this is the table that I'm going to link
to access all right so now let's again
return to access and this time again
external data excel and what I want to
do is I want a link to the original data
source which is the excel worksheet
let's identify the source now this time
remember that i'm linking back here to
that separate worksheet to demonstrate
how linking work so we're linking the
data to the original source click ok and
again let's just take all the defaults
the information is identified correctly
and I want to bring it in as a linked
table called new transactions click
finish
now it's easy to spot when we have a
link to you notice the difference
between the banking transactions table
and the new transactions so the new
transactions as an icon demonstrating
that its link to the original source so
if i come over here and i have 75
records but notice that when we've
linked to the original excel source if i
try to delete a record notice that it's
great out if i try to add a new record
I don't have an option because the data
is controlled
over here by the source which in this
case is an Excel worksheet so coming
back over here when I could do let's use
ctrl + to come down here to the bottom
let's add in another transaction for
june fourth and this time let's make it
a teller and let's put it in Main Street
and let's make it a CD
certificate of deposit and let's make it
for ten thousand dollars
all right now what we want to do is of
course save this
so now i'm going to save this and now
what I want to do is I want to see if
that information has been updated in
axis so come over here in to access what
we want to do is we want to make sure
that that record is now in there so in
order to do that we're going to have to
refresh the data
so if i close this down and now open it
up again
now when we come back here we can see
that we have 76 records so we had 75
before we refreshed and i refreshed in a
simple way I closed it and then opened
it up again
so there's that deposit for ten thousand
dollars so when we link to a source when
we link to a source which is excel we
cannot make a change in the access
database we make the change in the
source which is excel and then we
refresh the data when we are importing
the information we have two choices we
can import it with a link or we can
import it and then we can if we have the
data set up with the same headers we can
append that data
so there you've learned the first step
in integrating excel data
with an access database and I looked for
you in the next lesson