Transcript Import "Cities" - Data w/large strings and integers

Importing Data from a Spreadsheet
If you came to this presentation via a web browser,
right-click and choose “Full Screen” before proceeding.
Click mouse or press space bar to continue.
• This presentation was prepared by Professor Steve Ross, with the advice of other MIS Faculty, for use in MIS Classes at Western Washington University.
Please contact Dr. Ross for permission to use in other settings..
Introductory Slides
• See “ImportingData - Overview and Preparation
of the Database” for an overview, for creating a
database and for basic preparation steps.
• These slides introduce the Import Wizard
provided with SQL Server Management Studio.
• These slides show you how to simultaneously
create the table and import the data when the
data contains data in large strings.
Steps described in this set of slides are marked here in red.
Process Overview
1. Create the database.
2. Prepare the data for
importing
– Model the column headings
in the Excel spreadsheet to
be consistent with the
physical data design.
Step 1: Create database
3. Use the Management
Studio import wizard to
simultaneously …
– Create the new table
– Import the data into the table
4. Modify the table design
–
Using the GUI, modify the
attributes’ type and size to
match the physical design.
Step 3
Step 2
Original Data 
Modified Data  “Import” Table 
Step 4
“Final” Table
Import the Data
3. Import the data
a. Right-click Database name; choose Tasks; choose
Import Data…; click Next > on the splash screen
b. Choose the Data Source
Microsoft Excel) and
browse to locate
the file 
c. Note these
settings
DESTINATION
 Click Next
until you reach
the window with
“Sister Cities$”
(illustrated on the
next slide)
Note: in this example
• “tblCity” is the entire Excel workbook.
• “Sister Cities$” refers to a tab in the workbook.

Check “Sister Cities$” as shown above.

Rename Destination to [dbo].[tblSisterCity]  Note the table name

Click Edit Mappings
Note that you can
1. name the new
table below, or …
2. rename it after
importation is
complete.
 Under “Edit Mappings”, choose…
• int as the data type for Population
• nvarchar with size max for Description

Click OK to close Edit Mappings.

Click to the last window, then Finish
Continue through the import wizard until you
reach the last window.
• Note: along the way you may encounter the yellow
warning symbol that you see below. Ignore it and
keep going to the last window)
Click the Finish button.
• The importation should complete
without problem.
Close the wizard.
• The new table in your database
will be named ‘Sister Cities$’.
• Remove any spaces or special
character symbols, e.g. $ or
quotation marks..
Verify the Import Results
4. Inspect the table (right-click table name, choose Select Top 1000 Rows)
a.
b.
c.
d.
Refresh “Tables”
Data in expected columns
Numeric data format, ensure no loss
Character data, check for truncation (the Description
of Punta Arenas is 193 words long).