Import Data From Excel Spreadsheet

One method to import data from an Excel spreadsheet into a new table in a SQL Server database.

---

Closed Caption:

hi this is Dave Clark today I'd like to
discuss importing
data into a sequel server database a
common request i get from many clients
is to have a way where they can take
data from some external source such as
another sequel server database or
another database that's not a sequel
server database and also possibly files
such as Microsoft Excel spreadsheets or
flat text files and eliminate format or
xml formats things along those lines and
take that data and load it into another
single server database
so what we're going to do today
pro probably the most common example we
get is for someone has a spreadsheet
from some source and what we're going to
do today is to take the contents of an
Excel spreadsheet and load that into a
brand new table into single server
environment other there are many ways to
to do this today we're going to go over
an option which is probably the easiest
for brand new single server user and is
to use the import wizard
there are other ways to do this through
ssis packages which even through the
import wizard you can create that might
be a little beyond the skill set of a
beginner
there's also some methods through t
sequel using open rowset functions
things along those lines all over those
in a future video so today we're going
to review using the import with you
ok so I'm going to load data into this
database called demo test and see if we
expand the tables not even just refresh
here show you there's currently no
tables in this database
now let us have to be that way just in
my particular example I don't happen to
have any in here all right now
so what I
let's see what we're going to do is take
data from this spreadsheet named
customers . xlsx so if i open the
spreadsheet and those we have three
columns in here
customer ID customer name and customer
ranking and we actually have to have
three different rows of data as well for
acne supply Johnson technology company
ABC
we're gonna load this data into our
database and you can have as many
columns as you want as many rows but
just wanted to make a simplistic example
and after it for this video
so what we'll do is to actually promotes
on the database excuse me know that
database right mouse button on the
database and choose tasks and then
import data and we are presented with a
wizard
let's just give us a brief description
of what it's going to do and so I had
next to get past the startup window and
then we have our data source select the
source from which to copy the dates this
is where data resides
it defaults to the sequel server Native
Client 10 0 10 0 being a sequel server
2008 data source but we know we're
getting from a spreadsheet so we're
gonna look for an Excel source in here
and here we have Microsoft Excel you can
see a lot of the different types of
sources you can use which is Microsoft
Excel then select the file path i'm
going to select my path my customers are
xlsx file and then the XL version and i
believe that i have the 2007 loaded and
it was an older
file version you could select that as
well in the first row is column means
now we haven't already
look at the spread seeds you would
either need to open the spreadsheet or
get that information from the person
that you know tells you where
spreadsheet is whether the first rows
columns or not it's important to note
visit if if the first one doesn't have
call means that means that first Rose an
actual record of data you want to get
loaded in
so yeah that's where the common we're
going to come from that first row and
then where do we want to put that data
I'm gonna put that in my demo instance
just going to use my windows
authentication for the credentials to
make the connection
you could specify the sequel server
authentication as well if you don't use
the mixed mode and then which database
we're going to look at this into so I'm
gonna load this into the demo test TV
database and then we can copy data from
the table view or we can write a query
to do it
so the copy just says here i'm going to
get these this the the Rose the rain
here Collins I found we can load it in
if you want to write a query to select
specific columns or you know but if if
you're brand new to this
you're probably not gonna be regulated
at t is equal to load that so usually
I'll go over with my clients just to
select the accommodate copy data option
and select next and you can see we have
our sources from that spreadsheet are
listed and they are the individual
worksheets
so one thing it's nice if we're is
giving you the source data if they can
have those worksheets name something
that kind of makes sense
so in this case our first worksheet was
renamed from sheet1 to customers and the
others were left on there now if they're
not even to be used
it's also nice if they're deleted and
not there but we were loading they were
loading customers and then products and
sales data
you know we might have names on all
three of these sheets if we didn't we'd
have c1 c2 c3 it's kind of confusing to
know what we're learning where move till
we get to the commentator
so in this case we know we're just going
to load our customer data and these
other two are blank sheets now one thing
also that like to do is actually change
the destination
tablename the dollar is a reference
referencing for Microsoft Excel you know
how it references the worksheets the
dollars on but I don't want a dollar
sign my table name
let's go ahead and remove that and say
the destination is going to go to the
dvo schema but it's going to the
customers table and we can actually
preview and see what we're going to show
you notice it's select star from
customers dollar signs that's the source
worksheet and there's the three records
that we thought we would get if we need
to modify anything in the mappings we
could we could do that in here you can
go into edit the sequel but if you do
that then you have to maintain
everything through there instead of the
GUI again for your brand new and run
ones type things it's easiest just to do
it with a gooey
um so create destination table is
selected and then drop and recreate
destination table but that's useful for
is if you have some some data that
you're going to load in that you just
eat
you always need to see that particular
set of data then you drop the table
every time and load the new data and no
like insert it doesn't exist
update if it does exist etc we can
enable identity and start where each row
we've got a unique unique column
so here's the source columns and the
destination columns
now notice that people server is
figuring out the data types based upon
the data in the column and how their
format in excel spreadsheet we have to
that will be float or decimal type
numeric values 1 the name is going to be
in bart car - 55
if we want to change this to know size
of 50 we could we could do that in here
if we knew that the customer name was
never longer than 50 characters so one
thing that comes into play
you'll notice the column names are going
to be named whatever they are named on
your source spreadsheet in this case
customer ideas customer underscore ID
you see that a lot in sequel server
databases where the column name every
portion of the name is separated by
underscores
you can also see this format like Pascal
or camel case where the first letter of
each portion is capitalized there's no
underscores everything's just strung
together so
customer name and then there's the
customer ranking with sexually customer
space ranking
so that's going to go into our
destination table column we need
customers page ranking in order to give
these various examples to see that
that's important as well somebody's just
throwing some stuff in a spreadsheet
giving you this is how it's going to
look at how you have to access it from
other outside sources like reports and T
sequel statements etc so you want to
make the story is consistent so best
that it can be that to find that way in
a source if not you can see and what you
wanted to look like here of course
you've got 50 columns
it's not not a fantastic go through
modify each one
alright so everything looks good there
was going to go ahead and select next
and then we can choose to run
immediately or save as an ssis package
he saves as this is package is great if
you want something that you're going to
run over and over over it but then you
have to know a little bit about the ssis
package it may be where you want to
store that how to set it up etc
it kind of will do that stuff for you
here
like whether the load its sequel server
just the windows file system
I you know if you want to save the
information as far as the protection
level that you know whether so if you
use a sequel credentials to connect my
ever pass for things like that need to
be saved
so that's a little bit beyond the scope
of what we want to discuss today but
just note that you can create an
exercise packages right from from this
wizard
we're going to choose to run this
immediately
go ahead and select next and then we get
the wizard telling us the what is going
to happen basically taking from this
source location using this type of
provider a lady be tool
12 the location this instance on my
machine is the destination location and
a destination providers the sequel
native
client 10 it's going to copy rose from
the customers dollar sign to customers
all right let's go ahead and hit finish
and we'll see the all the information we
come up about this
you know what we have success there's an
information message then copying finish
success and then three rows transferred
and we can click on this but we just see
the you know the same thing if it was
more detailed information or perhaps
there are some error messages and
someone you know we'd be able to click
on that for the more and more
information
so go ahead just click close to the
export wizard and now i will refresh the
tables and you'll notice now we have our
customers table in the database check
out the columns and there are three
columns just just like we wanted
so we could make a connection to that
database now and we'll just select all
columns from that table run that you see
there we have our data and our sequel
server database now just one other quick
. i wanted to mention which already
touched upon was the column names
I just did select start here so it just
select every column if I wanted to
select the particular column just
customer ID and you know maybe I want
the customer name and I can do that but
if i wanted to get the customer ranking
notice that the sequel server will
qualify that with the left and right
square bracket
I run that and it works great I get the
information if I if I was just typing
the query and said well the column name
is customer space ranking when i run
that we get an error message that
there's invalid column name customer but
i have custom ranking well what it does
is it treats each of these as
independent entities of customers the
column is looking for
and since there's no , and i think we
are a lacing our column name customer
column a ranking will be the similar to
doing this
select customer as ranking meaning
rename it or alias . com to be called
ranking but it doesn't work so because
our customer column custom column name
customer does not exist
so we simply qualify it like so and then
we can can get our data out
so if we had a customer ID
whoops excuse me customer ID and
customer name and customer ranking we
could select them as well
I believe you may also be able to use
single quotes
no single quotes for a leasing something
that is actually just going to put the
literal and their reach
so that so we do need to qualify with
these i was talking about was if you
want to name it
I say you wanted to name it must rank
notice this wouldn't work either because
it's going to name it cussed and then
it's going to you know say well what's
this rank doing out here
so here we are talking we could use
either the left and right square
brackets
now it's called custom rank in our
output or the single quotes
so what I prefer to use the left and
right square brackets everywhere it is
because then it's always the same you
know where he had accessing a column has
basis or if your name in a lace that has
spaces will just use that
all right so that last piece was just a
little bit extra
because when you're loading data in from
a spreadsheet a lot of times the person
creating it is not in a database
environment world and they just type
things with spaces because it looks
nicer to read but when you come into a
database may have to handle thousands
thousands special situations
so that's the video today on loading
data in through the import wizard
I hope you learned something today and
thanks for watching and have a great day