How to move data from Excel to SQL Server 2008/2012- Act 1 Scene 1

So, I have undertaken a massive BIG project in Educational Data Mining which the first step is data collection. After collecting the data and most of it is in excel format. So to make any sense out of it was imperative that I convert it to a relational database format. Now this gets interesting, for I have a couple of choices to accomplish this feat;

a. Manually copy and paste the data which is like “Yucks”… and really I have no intention to go insane at such a young stage of my life 🙂

b. Programmatically, but how….? Million dollar question.. Read on,

  1. Ensure that you have first installed Visual Studio 2008 and then download and Install MS SQL Server 2008 Enterprise Edition R2 and install Service Pack 1 for the same. The benefit of installing SQL Server 2008 is that it comes bundled up with Business Intelligence Studio. You must install Visual Studio should first followed by installing SQL Server 2008. I have wasted several productive hours on trying to integrate higher versions of VS and SQL Server has led me to believe that this is the correct method to avoid throwing your computer out of sheer frustration.
  2. After the installation is completed you can restart your computer.

In this example project, I will describe on how to import data from excel table into SQL Server 2008 by using the SQL Server Integration Services (SSIS) tool.

Let the fun begin

Assume, you have an excel spreadsheet of student records that you want to import into SQL Server. Given below is a sample screenshot. Student records Sample

 Step 1: Create a SSIS Project

Go to SQL Server Data Tools or Visual Studio and then choose to create a New Project. Ensure that you choose the right project template as shown.New Project

SSIS will now create a new project, and also (by default) a new package too, Package.dtsx that is the default naming convention. There are two ways you can see that this is what’s happened. One is that you can see the package in Solution Explorer:packageName

(if you can’t see Solution Explorer, choose‘View’ from the top menu bar and then choose ‘SolutionExplorer’ from the menu to show it. Alternatively you can also press the keys ‘Ctrl’, ‘Alt’ and ‘L’ together on the keyboard and release them together to show the solution explorer window.)

The other one which you see shown to you on the screen packageName1Please note that by default you are put in Control Flow view, which is like a flow diagram showing the sequence in which tasks that you create will execute.default view Step 2: Create a connection to the SQLServer Database

Method 1: If you look at the solution explorer, You will see ‘Data Sources’. Right Click on it and choose ‘New Data Source’. Add new data Source Wizard

b. When you click the button ‘Next’, you will see the following: New Data sourceBy default the radio button titled ‘Create a data source based on existing or new connection’ will be selected. Click the button ‘New’ to create a new data source. When you click the New button you will see this connectionMgr10

c. As shown in the above screenshot if you have chosen the server name and the database name correctly and when you click on the button ‘Test Connection’ you should see the following ‘Test Connection succeeded’ message box. connectionMgrSucess

d. Finally, if you look at the Solution Explorer, under Data Source you should see the database connection as shown: connectionMgrSucess1

Hope you were able to accomplish this much. If you have encountered any problem at any step, please leave your comment and I will be happy to answer it for you. In the next post I will show you how to create tables in SQL by using the Business Intelligence Development Studio (BIDS) 2008, populate data from excel files into sql server database. Till then stay safe and be well.

Thank you for reading.

Advertisements