How to Import Excel Spreadsheets to Access Databases

Remember to check out my other channels & subscribe!!!

---

Closed Caption:

everybody in this video I'm going to be
showing you guys how to import and
exhale spreadsheet into an access
database
this is really easy and simple when you
get the hang of it it's it's actually
only a few clicks or steps and a little
bit of typing so if you you know
don't know how to do this at all then
don't worry i'll be explaining it from
beginning to end
and with that being said let's get
started first thing we're going to do is
open up your XL database or spreadsheet
sorry and we're just going to go ahead
and look at everything that's going on
here so basically you want to make sure
that your you know your books down here
at the bottom or tabs i guess are just
set to 1 so delete everything you don't
want and then just leave the one and you
can save it as a new file that would
make it a lot less confusing later on
when you're setting up the import wizard
and then
yeah so we really want to pay attention
though to the top of these the field
names which are the names of the columns
and then we want to pay attention to how
much like what kind of data is in here
what the name of the data is you know
everything like that and so let's open
up our access and make sure everything
matches up
go ahead enable this content and then
open it back up again and the customer
name or number sorry i just want to make
sure all the names of the actual columns
matchup so like we want customer number
and customer number named named street
street city city state state postal code
balance amount paid you know all these
are good to go
and that's all we basically need but
when we go in here we right click this
and go to design view we want to make
sure all this data basically lines up as
well so if i wanted the customer number
to have a maximum field size of four
because that's all I need as you can see
i am 23 down to TW 56 w is 34 we only
need for four characters so that would
be good and it is text because we can't
do arithmetic sequences on it
anything you can't do arithmetic on
counts as text so like if you have a
phone number
you can't do any arithmetic you can add
or subtract them so they're text but
that's how you remember what's text and
what's number so you know money of
course this currency its separate but
still so format you don't mess with any
of that we don't want any duplicates
because customer number should not be
duplicated
you don't want to duplicate records
because usually they're there are minor
differences between them and that causes
problems in the future
so we do want that indexed customer name
maximum field size of 25
so basically if they had a name that's
longer than 25 characters we wouldn't be
able to type it in and believe it or not
this will fit so good
so that's good and then let's skip on
down to I guess postal code postal codes
a good example
postal code there is no postal code
that's more than five digits so we can
keep it at five and just leave it at
that because like 3708 four that's five
and they all match up so so we do want
that and then duplicates are ok but we
do want these to be indexed so basically
when you index something that makes it
easier to search faster to search and
all that good stuff
well when you're doing a quarry later on
so that will be fine
and just so everyone knows I hate the
word query it's not fucking query it's
corey so anyways moving on a amount paid
and currency you do want currency and
you can do decimal places to auto but we
really just would rather have it too
just to be safe
basically what this means is when you
input this it will keep the . 00 at the
end because if we had you know 38 cents
on the end of that 195
then we want to make sure we can see
that and we don't want to go into three
because you know it doesn't matter if
you have three decimal places you can
you can't get like half a penny
so so that's why we do that and then the
next currency thing would be
like let's see balance balance is
currency decimal places to and I don't
know whether I yeah
ok so both of these are good to go and
of course I'd explain what the
description was another video so don't
have to worry about that but basically
we can close that XL now and I don't
want to save any changes because i
didn't really do anything and we want to
go back to our home tab so we know what
we're looking at and make this full-size
and let's double tap this to go back
into regular view or um I think it's
like table view or data sheet view
I forget what it's called but either way
it works so so what like what we have
here is our basic view and what we want
to do is import r XL spreadsheet so the
first thing we're going to do is we're
going to close this
believe it or not and click on external
data tab go to exhale
we're going to find our actual file here
like XL where we go and we're going on
and here we go lab 11 customer data and
we're going to click open and then we're
going to append it to the table and then
we're going to choose which table
now we only have one table it's named
customer so we click that one but if you
wanted to append it to a different one
than that would work as well
and the reason that we want to append it
as opposed to just importing it to a new
table
I may seem easier to import it to a new
table but at least this way you don't
have to worry about what what you're
doing with like column names or sorry I
fields and you don't have to worry about
data types data sizes all that other
crap you've already have already done it
and as long as everything matches up
this will import correctly so so you're
good to go
and now what we're going to do is click
OK and look make sure everything's fine
don't worry if like one of these turns
out black it's a little weird i've only
had it do it one time and it wasn't mine
it was on Jeremy's he's kind of an idiot
so I'm regardless it works and basically
this is good to go so let's go ahead and
click Next and
next and then finish and it will import
everything and then we can close
and next time we double-click this
surprise all our data is here
it's all formatted correctly maximum
field size on that is to maximum field
side on this one is for so we can't type
anything else in all works and then if
we exit out
you notice something just disappeared
right here basically what that is is if
we were to open this up you can see
everything saved because there's this
log access database here it's basically
a la CCB as basically like a logger or
something
we're basically any changes you make it
automatically logged in there and then
if you you know if your program shuts
down if your computer freezes if you
exit on accident
it just wraps it all up and exits really
quick so that it doesn't lose any data
so anyways that is how you import data
and what not to a access database from
an Excel spreadsheet
I'll be going to do that and i'll see
you next time