Multiple different Excel sheets into one SQL table

Question

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 
wolf     Kerry         M.

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 :)

Answer 1

First off, you'll make life slightly easier on yourself if you create a minimal table containing, say, just a RowID column. This way, you avoid the edge case of having to create the table on the first pass. Or, you could do this on the first pass, and introduce the first group of columns using ALTER TABLE ..., the same as any other pass.

The next step is to query the metadata in sysobjects and syscolumns to figure which columns aren't already there, and add them with ALTER TABLE ....

The third step is to read the heading row and use it to build the SQL statement. Use @_1, @_2 as parameter names to avoid having to remember the names in the final step.

Finally, iterate over the remaining rows. For each row, binding the cells to the corresponding paremeter and then execute the statement.

Answer 2

First of all, thank you so much for replying !

let me see if i got what you said:

if sheet1 that has the columns A B C D , when i run the code, i will have an sql table that has the columns A B C D

then if i have another sheet that has the columns A B C D E, i will do the following :

step 0 : get all columns names of my existing table ( in this case A B C D)

step 1 : read the heading rows from excel sheet ( A B C D E )

step 2- a) compare the result of step 0 and step 1 step 2- b) get the columns names that are missing from my sql table and add them to it (
in this case column E) so after adding it to the table, my sql table will have the columns A B C D E

but i didnt understand what you meant when u said the following:

# The third step is to read the heading row and use it to build the SQL statement. Use @_1, @_2 as parameter names to avoid having to remember the names in the final step.

Finally, iterate over the remaining rows. For each row, binding the cells to the corresponding paremeter and then execute the statement. # again i really appreciate ur help :):)