By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

import different excel sheet to ONE sql table

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

Expand|Select|Wrap|Line Numbers
  1.  
  2.  
  3. using System;
  4. using System.Collections;
  5. using System.Configuration;
  6. using System.Data;
  7. using System.Data.OleDb;
  8. using System.Data.SqlClient;
  9. using System.Linq;
  10. using System.Web;
  11. using System.Web.Security;
  12. using System.Web.UI;
  13. using System.Web.UI.HtmlControls;
  14. using System.Web.UI.WebControls;
  15. using System.Web.UI.WebControls.WebParts;
  16. using System.Xml.Linq;
  17. public partial class _Default : System.Web.UI.Page 
  18. {
  19.     protected void Page_Load(object sender, EventArgs e)
  20.     {
  21.  
  22.     }
  23.  
  24.     protected void insertdata_Click(object sender, EventArgs e)
  25.     {
  26.         //-----------------connection to excel=--------------------------
  27.         OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("example.xls") + ";Extended Properties=Excel 8.0");
  28.         try
  29.         {
  30.             //-----------------Commad to get all columns---------------------
  31.             OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
  32.  
  33.             //-----------------open  the connection-----------------------
  34.             oconn.Open();
  35.  
  36.               //-----------------execute the command ----------------------
  37.             OleDbDataReader odr = ocmd.ExecuteReader();
  38.             string fname = "";
  39.             string lname = "";
  40.             string mobnum = "";
  41.  
  42.               //-----------------read from the datareader-------------------
  43.             while (odr.Read())
  44.             {
  45.  
  46.                    //-----------------insert into the db table ------------------ 
  47.                 insertdataintosql(fname, lname, mobnum);
  48.             }
  49.             oconn.Close();
  50.         }
  51.         catch (DataException ee)
  52.         {
  53.             lblmsg.Text = ee.Message;
  54.           }
  55.         finally
  56.         {
  57.             lblmsg.Text = "Data Inserted Sucessfully";
  58.         }
  59.     }
  60.  
  61.     public void insertdataintosql(string fname, string lname, string mobnum)
  62.     {
  63.         //-----------------connection to sql database----------------
  64.  
  65.         SqlConnection conn = new SqlConnection("Data Source=.\\sqlexpress;AttachDbFileName=|DataDirectory|exceltosql.mdf;Trusted_Connection=yes");
  66.  
  67.  
  68.         SqlCommand cmd = new SqlCommand();
  69.         cmd.Connection = conn;
  70.         cmd.CommandText = "insert into dbtable(fname,lname,mobnum) values(@fname,@lname,@mobnum)";
  71.         cmd.Parameters.Add("@fname", SqlDbType.NVarChar).Value = fname;
  72.         cmd.Parameters.Add("@lname", SqlDbType.NVarChar).Value = lname;
  73.         cmd.Parameters.Add("@mobnum", SqlDbType.NVarChar).Value = mobnum;
  74.  
  75.         cmd.CommandType = CommandType.Text;
  76.         conn.Open();
  77.         cmd.ExecuteNonQuery();
  78.         conn.Close();
  79.     }
  80.  
  81. }
  82.  
  83.  
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 :)
Oct 19 '10 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
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
Oct 19 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.