Import Excel data into SQL Server 2014 Database by Kalman Toth

Import Excel worksheet into SQL Server 2014 table using the SSIS import/export wizard.

---

Closed Caption:

import from exile two sequences
doesn't 14 database this is comment off
here are a few of my secret server books
sequel server 2014 database design
sequel server to doesn't 14 design and
programming
big in a database design and sequel
programming using Microsoft sequel
server 2014 beginner secret programming
using Microsoft sequel server 2014
the books are available on amazon.com
this is we excel worksheet or
spreadsheet which we are going to import
actually this is the sales order header
table from the adventureworks database
the adventureworks family of sample
databases used since sequel server 2005
to demonstrate with new features of
sequel server
this table always excel sheet has over
30 12 oz and rolls and we have the first
role as the header so let's import this
to a sequel server 2014 database
first we have to create one we are in
sequel server 2014 management studio so
let's say create database test 2014 go
execute so we have our database
the actual excel file is located in his
folder on the c drive and data export
folder
let's alone
the import/export resort so here are the
databases we have to refresh it so we
see our new database and this is our new
database we right-click tasks and we
select import data
this gives us the import/export desert
next
so we are going to select the data
source the excel file is going to be the
datasource microsoft excel and we have
to find the excel file this is we excel
fine
the first throw his column names this is
significant we have to make sure we read
this is check marked or not check mark
the destination is going to be a sequel
server Native Client 11 and the test
database which is our destination next
copy data from one or more tables or
views so this is going to be our source
and the destination is named the same
way that's fine we was
we can add it mappings
as we see if the type is not recognized
the visa just assigned and watch art 255
this is fine for the time being but
after remove the data to a destination
table in the database we may have to
work on this to refine it a little bit
more
we can also add it with sequel at this
point this is were table which is going
to create so for example we can make
data type changes at this point if we
know how to change it
we are not going to do any changes at
the moment we say okay we can also
preview the data now this step is very
important because if we don't see what
data correctly here using just visual
inspection then it's not going to be
good data in the database is going to be
transferred as bad data so it's
important that we see the data correctly
it looks it looks ok
next step we can run it immediately we
can also save it and run it or just save
it as an ssis package we are just going
to run it next
this is a summary page finish
the transfer is going on
it's very hopeful and it is successful
now unfortunately frequently happens
when it's not successful and mainly due
to data problems but this data set with
axel is clean data so we have our table
and we can inspector from the object
Explorer where is our table and here is
our new table select top 12 hours and
rose and here it is here is the data
which we transferred from the excel
worksheet based on visual inspection it
looks ok
mrs. were
well Larry generally when we transfer
data from external sources such as flat
files or axial rubble databases we
transferred into a special table called
staging table
when we are successful transferring to a
staging table when we look at the date
of a check it we analyze it and at what
point the right queries to move the data
to the final destination in between we
may have to do changes to the data or we
may have to do data type changes this is
we end of the demo
I