Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

load a db table from excel

I am new to .net and i wanted to know how to implemnet thisi project that i have  been given and wanted an experts advice on the best way to implement the project.Plz advice.

Requiremnets:

Read from multiple sheets of an excel file  and load the single sql db table with this data from the excel file and also run a stored procedure in the background
to manupilate other db tables using this table.
This is an windows application.
Thank you.
0
pgupta81
Asked:
pgupta81
  • 2
  • 2
  • 2
  • +1
1 Solution
 
Gautham JanardhanCommented:
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
0
 
rameedevCommented:
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
    }
}


0
 
pgupta81Author Commented:
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 ?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
redpipeCommented:
The following function takes a complete filepath to an Excel file and returns all sheetnames in the file as an string array.

public string[] GetSheetNames(string FilePath)
    {
      string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
                          "Data Source=" + FilePath + ";" +
                          "Extended Properties=\"Excel 8.0;HDR=YES\"";
      OleDbConnection conn = null;
      string[] xlsSheets = null;
      DataTable dt = null;
      try {
        using (conn = new OleDbConnection(connString)) {
          if (conn.State != ConnectionState.Open) { conn.Open(); }
          dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
          if ((dt != null) || (dt.Rows.Count > 0)) {
            xlsSheets = new String[dt.Rows.Count];
            int i = 0;
            string sheetName;
            foreach (DataRow row in dt.Rows) {
              sheetName = row["TABLE_NAME"].ToString();
              if (sheetName.StartsWith("'")) { sheetName = sheetName.Remove(0, 1); }
              if (sheetName.EndsWith("'")) { sheetName = sheetName.Remove(sheetName.Length - 1, 1); }
              if (sheetName.EndsWith("$")) { sheetName = sheetName.Remove(sheetName.Length - 1, 1); }
              xlsSheets[i] = sheetName;
              i++;
            }
          }
        }
      } finally {
        if (conn != null) {
          try {
            conn.Close();
            conn.Dispose();
            conn = null;
          } catch (OleDbException exOleDb) {
            Logger.LogException(exOleDb);
          }
        }

        if (dt != null) {
          dt.Dispose();
        }
      }
      return xlsSheets;
    }
0
 
Gautham JanardhanCommented:
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()
0
 
redpipeCommented:
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;
    }
0
 
pgupta81Author Commented:
code Helped!! Thanks
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now