Thursday, August 30, 2012

Tech: Import Excel Data into a SQL 2012 Table

Importing Excel Data into a SQL Table

Using the built-in Import and Export Data  Utility


Importing data into a Microsoft SQL Server Database Table can be a little tricky.  Things have changed from back in the days where the DTS packages were easy to configure (although SSIS is a more powerful alternative),  if you want a very simple, very fast way to import data into your SQL Server Database from a Microsoft Excel file this is an extremely quick and easy way to do it.

The first thing to note, and this is very important, is that the 64-bit import tool does not natively open Microsoft Excel files.  

Choose the 32-bit tool.

icon
32-bit Import/Export Tool

When you open the Import/Export Wizard you will see the following dialogue box and will need to choose the data source.  If you do not see a Microsoft Excel option then you may have accidentally opened the 64 Bit version of the wizard.
choose_data_source
Once you have chosen Microsoft Excel as your data source the dialogue options will change and allow you to locate an excel file.  Browse to or type in the location of your excel file.

Choose_data_dource_b

Choose the database server and the database you wish to connect to.

Choose_destination
If you need to add additional parameters or logic into the import, or want to import into an existing table based on existing data you can write a query to specify a data transfer.  I'll cover that in another article but for now we are just going to import the data into a new table.
Specify_table_query

You will then be given an opportunity to either select a table in which you can import the data, or create a new table.  I've created a new table called New_Contact_Table.
Select_tables

The import can then be imported immediately or it can be saved as a SSIS package which could be executed on a schedule or again at a later point in time.
Save_and_run

The confirmation reiterates the configuration changes that you've selected.
Complete_confirmation

When you click the finish button the status screen will show the progress, and if all goes well it will indicate that the data was exported properly into the database.
Successful_execution

Finally as you can see the 9200 rows that were imported are verified.

query_test_result


From this point you can simply manipulate the data into an existing table or if you just needed the data imported into a SQL server then your job is done.

This is a fairly simple process and I hope to create a short YouTube Video detailing the process in the next few days.

I have created a new video dealing with this process here.



~Yati


4 comments:


  1. Much obliged to you for requiring significant investment to give us a portion of the valuable and restrictive data with us.
    Regards,
    Excel Advanced Training | MS excel Training in Chennai | Excel Macro Training in chennai

    ReplyDelete
  2. Impressive blog with useful content, The way of explanation about SQL was really superb. Thanks a lot for share such a nice articleLinux Training in Chennai | Unix Training in Chennai | Python Training in Chennai

    ReplyDelete
  3. Nice blog too informative. looking and reading your points its so impressive. doing more blog like this. i really appreciated doing like this.
    No.1 Software Testing Training Institute in Chennai | Best Selenium Training Institute in Chennai | ISTQB Certification Center in Velachery

    ReplyDelete