Click here to Skip to main content
12,999,845 members (48,413 online)
Click here to Skip to main content
Add your own
alternative version

Stats

25K views
970 downloads
31 bookmarked
Posted 24 May 2008

Excel Converter To Microsoft SQL SERVER

, 24 May 2008
Rate this:
Please Sign up or sign in to vote.
This application dynamically gets the Excel database and converts it to SQL Server

Introduction

This article is aimed to enrich access to two databases. Through this application, you are able to feasibly convert the Excel database table to the associated table in Microsoft SQL Server. The interface is designed like a wizard that would direct you to the destination very easily. So, let's take off!

Background

The application doesn't need much preliminaries, so just drive your car!

Using the Code

In here, we have two main classes:

  1. ExcelManager.cs
  2. ServerExplorer.cs

First, we will look at ExcelManager and later jump to ServerExplorer. So fasten your belts, guys!!

Now, this is what we use for managing the Excel side. Let's dip inside and dissect the code. In the code below, there is a method which gets the database filename in Excel and the sheetName, beginCell, endCell in the associated table in our Excel database. The code takes advantage of OLeDb data Provider for accessing the Excel DBMS. That's all what the ExcelManager class does.

public static DataTable GetExcelTable
    (string fileName,string sheetName,string beginCell,string endCell)
        {
            try
            {
                OleDbConnection dbConnection = new OleDbConnection();
                dbConnection.ConnectionString= "provider=Microsoft.Jet.OLEDB.4.0;
                            data source=" + fileName + ";Extended Properties=Excel 8.0;";

                StringBuilder stbQuery = new StringBuilder();
                stbQuery.Append("SELECT * FROM [" + sheetName + "$" + beginCell + ":" 
                                 + endCell + "]");
                OleDbDataAdapter adp = new OleDbDataAdapter
                                       (stbQuery.ToString(), dbConnection);
                DataTable dsXLS = new DataTable();
                adp.Fill(dsXLS);
                return dsXLS;
            }
            catch
            {
                return null;
            }
        }

Now let's take a look at the ServerExplorer class. Actually, this class performs the main operation in our app. Take a look below:

public static IList<string> GetActiveServers()

The method GetActiveServers() takes advantage of SqlDataSourceEnumerator for accessing the Microsoft SQL Server names in a typical LAN Network.

public static IList<string> GetDatabases(string serverName, string userId, 
    string password,bool windowsAuthentication)

The GetDatabases in fact tries to connect to the associated ServerName chosen in the previous method. You are able to connect either Windows Authentication or appropriate UserName and Password.

public static IList<string> GetTables 

public static IList<string> GetColumns(string serverName, string databaseName, 
    string userId,string password, bool windowsAuthentication, string tableName)

The GetTables and GetColumns which are defined above as their names imply do nothing but get the tables in the SQL Server associated database and columns of the tables of the previous ones.

Summary Of This App

In here, I have developed three WinForms:

  1. frmConfigExcel
  2. frmConfigSql
  3. frmConfigTransfer

In frmConfigExcel, by using ExcelManager class I have retrieved the database which we need in Excel and the associated SheetName and Beginning Cell and the End Cell too.

In frmConfigSql, I actually make a connection to the SQL Server database. Eventually, in frmConfigTransfer, I have used a gridview intended for viewing the data retrieved from the associated ExcelDatabase. Now the role is that of selecting the table from the SQL Server database and in the last scene, I chose a column of my SQL Server side and its associated column on the Excel side for converting by clicking the Add button for each. And that's the end of the film. You hit the button "Finish" and can have a coffee now!

About the Author

I am Hamid Moghaddam, .NET Programmer, MCP and own a company, Kahroba Inc, Iran, Yazd.

History

  • 25th May, 2008: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

HamidMoghadam
Web Developer
Iran (Islamic Republic of) Iran (Islamic Republic of)
I'm 22 years old.I'm from Iran.I'm study computer programing.

You may also be interested in...

Comments and Discussions

 
Generalمرسی عالی بود Pin
arash_developer7-Apr-14 0:30
memberarash_developer7-Apr-14 0:30 
Generalone more comment Pin
lepetitchu23-Jun-09 1:35
memberlepetitchu23-Jun-09 1:35 
GeneralRe: one more comment Pin
HamidMoghadam23-Jun-09 21:15
memberHamidMoghadam23-Jun-09 21:15 
GeneralThank you so much Pin
shiv_dsnair2-Mar-09 7:24
membershiv_dsnair2-Mar-09 7:24 
GeneralRe: Thank you so much Pin
HamidMoghadam2-Mar-09 20:13
memberHamidMoghadam2-Mar-09 20:13 
GeneralForm ConfigExcel Pin
RobertoRegazzoni8-Oct-08 0:26
memberRobertoRegazzoni8-Oct-08 0:26 
AnswerRe: Form ConfigExcel Pin
HamidMoghadam18-Oct-08 0:27
memberHamidMoghadam18-Oct-08 0:27 
GeneralGood Knowledge Pin
Morteza Naeiamabadi26-May-08 20:37
memberMorteza Naeiamabadi26-May-08 20:37 
GeneralInnovative idea Pin
dnpro26-May-08 1:31
memberdnpro26-May-08 1:31 
GeneralA suggestion Pin
Ed.Poore25-May-08 1:43
memberEd.Poore25-May-08 1:43 
GeneralRe: A suggestion Pin
HamidMoghadam26-May-08 19:19
memberHamidMoghadam26-May-08 19:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170624.1 | Last Updated 25 May 2008
Article Copyright 2008 by HamidMoghadam
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid