SQL Server – Importing Microsoft Excel Data

Background

Has a DBA we are either getting data into the database or getting data out.

In this post, I want to talk about getting data in.

Specifically MS Excel spreadsheet data.

 

Lineage

Our marketing guy buys leads and runs targetted campaign.

Each month we need to know how many of those leads actualize.

To get there, the leads vendor supplies a spreadsheet of the wins.

Prior to paying, we have to compare those numbers with actual recorded sales that is captured in our system.

 

Tooling

MSFT historically provides ample tools in the ETL Space.  Some are full blown applications in there own right.  Others are add-in Client Tools.

In this post we will cover the Import and Export wizard that is bundled with SSMS.

 

SSMS – Import and Export Wizard

Outline

Here is a brief outline of the steps:

  1. Launch SQL Server Management Studio ( SSMS )
  2. Connect to the SQL Server Instance
  3. Initiate Import
    • Navigate to the targeted database
    • Right Click on the database
    • From the drop down menu, select Tasks \ Import Data
  4. On the Welcome Screen
    • Please choose the Next button
  5. On the Choose a Data Source Screen
    • On the Data Source drop down
      • Please choose Microsoft Excel
    • On the Excel File Path textbox
      • Please choose the Excel file you will like to import
    • On the Excel Version drop-down
      • Please choose the version of the Excel file
    • First row has column names
      • Check if the first row has column names
  6. On the Choose a Data Source Destination
    • On the Destination drop down
      • Please choose Microsoft OLE DB Provider for SQL Server
    • In the server name text box
      • Please enter the SQL Server Instance
    • From the Authentication radio box
      • Choose Windows Authentication or SQL Sever Authentication depending on the path you have chosen
    • For Database Drop-down
      • Please choose the Database
  7. In the “Specify Table or Query” window
  8. The choices are
      • Copy data from one or more tables or views
      • Write a query to specify the data to transfer
    • We chose “Copy data from one or more tables or views” for the following reasons
      • We wanted all data
      • And, we will remove extranous data in SQL Server by
        • Deleting them
        • Creating a view and filtering them out via the “Where Clause
  9. In the “Select Source Tables and Views” window
    • The Tabs in the Excel file are listed in individual rows
    • On the Tabs you will like imported
      • Please place a checkbox on the tabs you will like imported
      • Once a Tab is checked, one is given the opportunity to specify the targeted table name
  10. Depending on the edition of SQL Server, a different version of window is displayed.  Those windows are
    • Choices
      • If the edition is Express, Web or Workgroup
        • The window allows us to Run the package
      • If the edition is Standard, Enterprise or Developer, or Evaluation
        • In the “Save and Run Package” window
  11. In the “Complete the Wizard“ Window
    • The Wizard lists the choices we have made
      • The choice location and provider chosen to access the Source
      • The destination location and provider chosen to access the Destination
      • The Source elements that we have chosen to import
      • And, their corresponding target elements
      • And, whether we have chosen to run the package immediately or chosen to both run and save the package
  12. The final window displayed is the “Execution Results” window
    • The package is ran
    • Any failures are listed
    • And, the final results of the execution is indicated

 

 

Object Explorer

 

TasksImportData (brushed up)

 

 

Welcome

Welcome

 

Choose a Data Source

ChooseADataSource

 

 

Choose a Destination

ChooseADestination (brushed up)

 

Specify Table Copy or Query

SpecifyTableCopyorQuery

 

Explanation:
  1. There are two options here
    • Copy data from one or more tables or views
    • Write a query to specify the data to transfer
  2. We chose the “Copy data from one or more tables or views

 

Select Source Tables and Views

SelectSourceTablesAndViews (brushed up)

 

 

Run Package

RunPackage

 

 

Save And Run Package

SaveAndRunPackage

 

Complete The Wizard

CompleteTheWizard (brushed up)

 

Execution Results

ExecutionResults

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s