Import Excel to MySQL Database

Take a Microsoft Excel Spreadsheet and import it into a MySQL Database using CSV files and PHPMyAdmin.

---

Closed Caption:

hello everyone and welcome to our
tutorial on how to import a Microsoft
Excel spreadsheet into a my sequel
database first just like to introduce
myself my name is matter of wine and I
work for the office of instructional
design here in Northern Kentucky
University
I work for Mike lively who is often the
host of our tutorials here so we have
microsoft excel up and running right now
and we also have access to a cpanel for
an online server that we have access to
go to that reduced to just to show you
what that might look like
that's actually PHP my admin but are the
way we get to our PHP my admin which
you'll need as well is through our
cpanel on our website here so if you
scroll down here you can see that our
PHP my admin is right here and also how
we create databases through here so i
just want to let you know up front what
you need to get started so first let's
go ahead and go to upsell and just bring
up what we're going to be importing into
the my sequel database
here you see just the very very generic
table we have students for different are
three different tests and then a final
grade just averaging those the few
things i know that's very basic but this
just to show you how to do it
so with all that with all this data here
normally you wouldn't really want to
have have to go ahead and type
everything into your databases
granted this is pretty small so you
could do it but say it wasn't on a much
larger scale
so first of all we're going to do is
going to save this file as a specific
type of file that is easily imported
into my sequel databases and that type
of file is a CSV SOT csv file we can do
that just by going to file save as and
then just pull down the save as type
here and look for csv should be too far
down here
surely there we go csb a comment to
limit the limited so i can't even say
the word document so go ahead and click
on that and hit save
you'll see the type their change if
you'd save it's going to say well i
already have one existing there was
going to go ahead and replace it comes
up i just tested this early to make sure
I could do it correctly select type file
does not support workbooks all this is
saying is you can you cannot have
multiple sheets when you're importing
this cut type kind of file into a mess
with that
so just said okay and it's going to keep
the active sheet only the one on top
so then hit OK and it's also going to
mention that there are some features
that are saved in the csv file or that
will be lost from your original file so
really just go ahead and hit yes here to
leave out all the incompatible features
and it should be just fine
yes and I went ahead and save it so now
actually you can see at the top for this
is csv so these can still be viewed in
Excel and it can be edited it in excel
but we like to look at them in a
different way just to make sure they
look ok
all right i'm gonna go ahead and
minimize each of the things i had up
here just so you can see the file is
saved on my desktop for quick to
minimize so minimize browser here
and surely here's the file that i need a
great book . csv we're gonna go ahead
and right click on this and open with
just to open it with notepad just to
make just so we can see what it looks
like a notepad
ok here's what it looks like in notepad
and you can see pretty much the exact
same table that you saw in Excel
except it's all separated by commas
that's what a CSV is it separates each
field each value with , see student
test1 test2 test3 final grade just like
before and everything is in order it was
before
so that's really what we want to see but
the way we're going to be doing this is
we're going to be importing all the data
down here into our database
we're going to be creating the the names
of each field in a different way just
when we create the table so what I'm
going to ask you to do is just go ahead
and delete this top set here because
we're going to create that another way
goodnight file and save that that should
be fine
ok when you see this window that just
means that it's giving you a little bit
of trouble because the file we have open
is also open in Excel still so you could
go back and close it in Excel or you
could just go ahead and
and rename is something a little bit
different here so I'm just going to
rename it slightly different put an
underscore import next to it and save
there we go less trouble that way so now
that we have this up and running go
ahead and get rid of notepad here and
we're going to go ahead and go to our
browser and log in to your cPanel now
you may I have another way of accessing
your server cpanel is 1 plus cos another
I know and I either way you can mess
with your databases this way
just go ahead and browse to the area
where you can create a database however
you might do that
I like to just use the my single
database button here to load this up and
easiest way to create a new out of it
database here is just a new database
we're going to name it let's see here we
will just name it
test DB for test database create the
database and it says it's been created
I'm not gonna go much more into
specifically how to create a user for
database and all that good stuff because
it could be covered in another tutorial
prefer right now we're just gonna browse
down here to the letter T to see and it
looks like it has a bunch of them here
so let's go ahead and look where we
think the test . DB will be it looks
like it'll be around here somewhere
alright test DB there it is so one is
created
so now that we have a database there we
can also access it with our PHP my admin
which already have opened the other tab
you can see but also show you how you
can get to that from the cpanel
all right to scroll down here and go to
PHP my admin and click on that and it
will open up something similar like this
from this list of databases go ahead and
choose the one we just created it was
test
db2 page over and we have a lot of
databases on there
click on test t be so now we have an
empty database of no tables or anything
we're going to create a table real quick
just by clicking this area
we're going to name the table a great
book our file the number of fields if
you remember right was a one field for
the student and then for for the three
tests and the average of the last grade
that's a total of five fields the one
going to go and it will lead to the next
part of creating a table
so go and type in those different types
of fields again so we have student
we have test one can see have actually
already or entered all these while
testing it
tell us three and final grade
I'm gonna go and leave uses of our car
here that just means you can put any
kind of character in there and we're
going to set the length at ten just
because I know that none of my grades or
anything
get beyond 10 but if you have student
names in there you're more than likely
won't want going to want to have more
than 10 characters because usually
students names with first and last will
be more than 10 monsters that might
attend
it just means 10 characters in the name
you know first and last
so minor is mainly numbers because i
just gave the students generic names of
numbers 1 to 10 so this looks looks
pretty good we don't want to set
anything to primary right here even
though we could but that's more to do
with databases that can be explained in
another tutorial
so go ahead and hit save and it should
create this table for us
there we go
just reviews the sequel query and we now
have a table with no data in it
but to get data in this and also you can
see that a great book table showed up
over here on the left
that's good so if we click on that it's
going to be the same thing without the
sequel query more than likely right
so from all these options up here you
can browse through the data if we had
any but as you can see there's a little
X up there showing us that we don't have
any data so i'm going to go to import
and here's the part that took me a while
to figure out but thankfully figured it
out eventually and browse to that file
that we made the great book . csv loops
off the page there
here it is right there go ahead and head
on it open on that one
now i remember sleeve all the stuff the
same but we exported as a CSV but i
found out that using the CSV using load
data is actually a way to get this to
work
I had no success using just the CSV
option
so if we wanted to just replace all of
the data we have in our table with the
new data are getting here we would go
ahead and click on replace table data
with file but since we don't have any
data in there we're just going to go
ahead and leave that blank all of our
fields are terminated by , so let's go
and change this to a , and the rest of
them we don't have to worry about any of
this because we didn't have any strange
characters in there so just go and leave
those the same for now and leave that on
auto and let's go ahead and give it a go
go go
looks like it says import has
successfully finished one query is
executed it looks like we have 11 rows
affected here and let's go ahead and
look at the data
let's go over to browse now that it's
not an access to hand now
mmm looks like whenever I save my CSV
document i forgot to get rid of the the
top row like I just said but you can go
ahead and delete it is easy by clicking
here and clicking the X stands for
delete it going to ask you if you really
want to do that and I do so here you can
see all of the data that we had an Excel
spreadsheet looking pretty much exactly
the same which can be useful for
attaching other programs using PHP or
whatever you guys happen to use
so this is just a short explanation of
how to get your data from a excel
spreadsheet to my sequel database