Click here to Skip to main content
15,867,704 members
Articles / Programming Languages / C#
Article

Excel Converter To Microsoft SQL SERVER

Rate me:
Please Sign up or sign in to vote.
3.53/5 (11 votes)
24 May 2008CPOL2 min read 37.3K   1.1K   32   11
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.

C#
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:

C#
public static IList<string> GetActiveServers()

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

C#
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.

C#
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)


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

Comments and Discussions

 
Generalمرسی عالی بود Pin
arash_developer7-Apr-14 0:30
arash_developer7-Apr-14 0:30 
Generalone more comment Pin
lepetitchu23-Jun-09 1:35
lepetitchu23-Jun-09 1:35 
Your stuff is cool! Thanks!
I find that it's not necessary to declare the Begin Cell & End Cell in Select statement.
And I have an extra function: automatically get sheet names of the opened Excel file. Here the code:
<br />
        internal IList<string> GetExcelSheetNamesToIList(string ExcelFileName)<br />
        {<br />
            Collection<string> result = new Collection<string>();<br />
<br />
            String connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + ExcelFileName + ";Extended Properties=Excel 8.0;";<br />
<br />
            OleDbConnection OleDbCon = new OleDbConnection(connString);<br />
            OleDbCon.Open();<br />
<br />
            DataTable dTable = OleDbCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);<br />
<br />
            OleDbCon.Close();<br />
                        <br />
            foreach (DataRow dRow in dTable.Rows)<br />
            {                <br />
                result.Add(dRow["Table_Name"].ToString());                <br />
            }<br />
            return result;<br />
        }<br />


Using:
cmbSheet.DataSource = GetExcelSheetNamesToIList(openFileDialog1.FileName);
GeneralRe: one more comment Pin
HamidMoghadam23-Jun-09 21:15
HamidMoghadam23-Jun-09 21:15 
GeneralThank you so much Pin
shiv_dsnair2-Mar-09 7:24
shiv_dsnair2-Mar-09 7:24 
GeneralRe: Thank you so much Pin
HamidMoghadam2-Mar-09 20:13
HamidMoghadam2-Mar-09 20:13 
GeneralForm ConfigExcel Pin
RobertoRegazzoni8-Oct-08 0:26
RobertoRegazzoni8-Oct-08 0:26 
AnswerRe: Form ConfigExcel Pin
HamidMoghadam18-Oct-08 0:27
HamidMoghadam18-Oct-08 0:27 
GeneralGood Knowledge Pin
Morteza Naeiamabadi26-May-08 20:37
Morteza Naeiamabadi26-May-08 20:37 
GeneralInnovative idea Pin
Ashutosh Phoujdar26-May-08 1:31
Ashutosh Phoujdar26-May-08 1:31 
GeneralA suggestion Pin
Ed.Poore25-May-08 1:43
Ed.Poore25-May-08 1:43 
GeneralRe: A suggestion Pin
HamidMoghadam26-May-08 19:19
HamidMoghadam26-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.