Connect to Excel using ODBC in C#

by Administrator 20. February 2013 13:08

Working with ODBC connections: 

1)ODBC(open database connectivity) is a very old technology 

2)ODBC concept was introduced in 1980s  

3)OLEDB concept was introduced in 1995s 

4)TDS concept was introduced in 2000s 

5)ODBC connections are recommended especially with very old databases like foxpro, dbase, lotus 123, ms excel 

6)ODBC supports to work with all types of databases 

7)ODBC works with jelp of ODBC drivers , ODBC drivers are developed DLL files which are com components 

8)for these DLL we need to create aliasname which is called as DSN(data source name) 

9)in excel every sheet works like a table

 

Example on ODBC connection with EXCEL 

Note:all the ODBC drivers(DLLs) are located at  

   Start->control panel->performance and maintainance->administrative tools->data sources(ODBC) 

                               (or) 

    Start ->run->ODBCAD32

 

Step1:creat an excel sheet 

Open excel software type as follows in sheet1, then save it with name c:\mydata.xlsx (2007)  (or) c:\mydata.xls(2003) 

 

A                B                  c 

Eno            ename       sal 

101             raj              5000 

102             varun        10000 

103             teja           15000 

 

Step2:creat a DSN 

Open datasources(start->run->ODBCAD32) 

Click on add button 

Select ‘driver domicrosoft excel’->finish 

Provide data source name=abc(need to be remembered)

 

Then click on “select workbook” button then choose c:\mydata.xls (or) c:\mydata.xlsx->ok->ok

 

Step3: 

Write a program to get the data from excel 

Open windows forms app project

 

Start->programs->Microsoft visual studio 2010->Microsoft Visual studio 2010->file menu->new-> 

project->select visual c# from installed templates->select windows forms application project 

place a button and datagridview controls 

using System.Data.Odbc;

 

using System; 

using System.Collections.Generic; 

using System.ComponentModel; 

using System.Data; 

using System.Drawing; 

using System.Linq; 

using System.Text; 

using System.Windows.Forms; 

using System.Data.Odbc;

 

namespace WindowsFormsApplication27 

{ 

    public partial class Form1 : Form 

    { 

        public Form1() 

        { 

            InitializeComponent(); 

        }

 

        private void button1_Click(object sender, EventArgs e) 

        { 

            OdbcConnection cn = new OdbcConnection("dsn=abc"); 

            OdbcDataAdapter da = new OdbcDataAdapter("select * from [Sheet1$]", cn); 

            //[] and $ is required only with excel

            DataSet ds = new DataSet(); 

            da.Fill(ds, "d"); 

            dataGridView1.DataSource = ds.Tables["d"];

        } 

    } 

}

 

Add comment