load a db table from excel

If its a one time requirement u can directly import data from excel into sql table using DTS.
In EM , rt click DATABSE, all tasks - Import and there u give source and destination, Ur source will be excel file

IF it is not a One time requirement then

On some Button Click Event (Or whatever is the UI event)...You could probably do something like this


Function LoadDataFrom ExcelAndUpdateDB()
{
    // Write your Code for filling some dataset from the Excel file
    // This Could return a DataSet
   
    foreach(DataRow dr in DataSet.Tables[0].Rows)
    {
        //Write your Code to take all the rows from the datatable and call your SP
    }
}

I have to develop a windows application on this thats why i cannot use EM DTS to do this..

How to iterate through multiple sheets of excel to load sql table?
Do i need to follow certain naming convention ?

Dim DS As System.Data.DataSet
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim MyConnection As System.Data.OleDb.OleDbConnection

MyConnection = New System.Data.OleDb.OleDbConnection( _
      "provider=Microsoft.Jet.OLEDB.4.0; " & _
      "data source=C:\myData.XLS; " & _
      "Extended Properties=Excel 8.0;")
' Select the data from Sheet1 of the workbook.
MyCommand = New System.Data.OleDb.OleDbDataAdapter( _
      "select * from [Sheet1$]", MyConnection)

DS = New System.Data.DataSet()
MyCommand.Fill(DS)
MyConnection.Close()

Forgot to submit the function for retrieving data from a given sheet.

public DataTable GetData(string FilePath, string SheetName, bool FirstRowIsHeader)
{
      string connString = null;
      if (FirstRowIsHeader) {
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                     "Data Source=" + FilePath + ";" +
                     "Extended Properties=\"Excel 8.0;HDR=YES\"";
      } else {
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + FilePath + ";" +
                    "Extended Properties=\"Excel 8.0;HDR=NO\"";
      }
      OleDbConnection conn = null;
      OleDbCommand cmd = new OleDbCommand();
      DataTable dt = null;
      IDataReader dr = null;
      try {
        using (conn = new OleDbConnection(connString)) {
          if (conn.State != ConnectionState.Open) { conn.Open(); }
          cmd.Connection = conn;
          cmd.CommandText = "SELECT * FROM [" + SheetName + "$]";
          OleDbDataAdapter adapter = new OleDbDataAdapter();
          adapter.SelectCommand = cmd;
          dt = new DataTable(SheetName);
          adapter.FillSchema(dt, SchemaType.Source);
          adapter.Fill(dt);
        }
      } finally {
        if (cmd != null) {
        cmd.Dispose();
        cmd = null;
      }

      if (dr != null) {
        dr.Close();
        dr.Dispose();
        dr = null;
      }

      if (conn != null) {
        try {
          conn.Close();
          conn.Dispose();
          conn = null;
        } catch (OleDbException exOleDb) {
          Logger.LogException(exOleDb);
        }
      }
      }
      return dt;
    }

code Helped!! Thanks