import different excel sheet to ONE sql table

[PeacefulSoul]

Hello,

I have a lot of excel sheets which columns are slightly different, i want to import all of these sheets (one at a time) into ONE SQL TABLE. I'll give an example :

Say ive written the required program and called it Excel2sql converter. So Excel2sql takes an excel sheet and create a database table with the data of that excel sheet, For example say i have the following excel sheet:

Excel_Sheet_1
-----------------------------
FirstName MiddleName LastName
John------- A. -------- Smith


when i run Excel2sql (Excel_Sheet_1), a database table should be CREATED for me with the following data in it:


FirstName MiddleName LastName
John------- A. -------- Smith



Now, when i run my program again with the following excel sheet:

Excel_Sheet_2
-----------------------------
LastName FirstName MiddleName
Kerry------- M. --------- wolf


i should get the following UPDATED db table:

FirstName MiddleName LastName
John-------- A. --------- Smith
Kerry------- M. --------- wolf


Notice that it added the data of excel sheet 2, into the already existing database table.
It did some kind of mapping between the columns of db and the columns of the excel sheet 2 to append the data appropriately.


Now, if i run my program again with the following excel sheet:

Sheet 3
--------
PhoneNumber LastName MiddleName FirstName
232-232 ----- Lame -------- K. ------ Phoebe



i should get the following db table:

FirstName MiddleName LastName PhoneNumber
John-------- A. --------- Smith--------null
Kerry------- M. --------- wolf --------null
Phoebe------ K. --------- Lame --------232-232

I want the code to do this dynamically, i mean, anybody can use my code, give it an excel sheet as an input, my code will then CREATE for him an sql data table and each time a user gives him an excel sheet, it should UPDATED the already created sql table.

Please i really need all the help i can get. Im very new at this. I wrote a primitive code that simply uploads one excel sheet into an ALREADY existing datatable. (which is not what i want , but i had to start somewhere)

 
 
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {
 
    }
 
    protected void insertdata_Click(object sender, EventArgs e)
    {
        //-----------------connection to excel=--------------------------
        OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + ";Extended Properties=Excel 8.0");
        try
        {
            //-----------------Commad to get all columns---------------------
            OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
 
            //-----------------open  the connection-----------------------
            oconn.Open();
 
              //-----------------execute the command ----------------------
            OleDbDataReader odr = ocmd.ExecuteReader();
            string fname = "";
            string lname = "";
            string mobnum = "";
 
              //-----------------read from the datareader-------------------
            while (odr.Read())
            {
 
                   //-----------------insert into the db table ------------------ 
                insertdataintosql(fname, lname, mobnum);
            }
            oconn.Close();
        }
        catch (DataException ee)
        {
            lblmsg.Text = ee.Message;
          }
        finally
        {
            lblmsg.Text = "Data Inserted Sucessfully";
        }
    }
 
    public void insertdataintosql(string fname, string lname, string mobnum)
    {
        //-----------------connection to sql database----------------
 
        SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
 
 
        SqlCommand cmd = new SqlCommand();
        cmd.Connection = conn;
        cmd.CommandText = "insert into dbtable(fname,lname,mobnum) values(@fname,@lname,@mobnum)";
        cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
        cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
        cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
 
        cmd.CommandType = CommandType.Text;
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }
 
}
 
 

Again the code ive written is NOT what i want, can you please help me modify it to get the requirements i want!

Thank you in advance :)

[ck9663]

I'm not familiar with the code you posted. Looks like a C/Java-based language. Search your language if there's a way you can retrieve the number of sheets in a workbook. Then loop through that...

Good Luck!!!

~~ CK