How to import Excel into database

Posted by Oswaldlily under ASP.NET on 2/3/2012 | Points: 10 | Views : 1535 | Status : [Member] | Replies : 4
I have nearly 20 columns in Excel.

Now i Need to import these datas into corresponding table in database

eg) First 10 columns into Table1
last 10 columns into Table2




Responses

Posted by: Santhimurthyd on: 2/3/2012 [Member] Starter | Points: 25

Up
0
Down
Take a look into the Wizard for import
http://www.sql-server-performance.com/2008/how-to-import-data-from-excel-2007/

Through SSIS package, follow the link
http://www.techrepublic.com/blog/datacenter/how-to-import-an-excel-file-into-sql-server-2005-using-integration-services/205


Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Naraayanan on: 2/3/2012 [Member] Starter | Points: 25

Up
0
Down
Hi,
Check this link
http://forums.asp.net/t/1162069.aspx/1

Regards,
Lakshmi Naraayanan.S
http://dotnettechrocks.blogspot.in/
http://abaprocker.blogspot.com/

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Vforvijay on: 2/3/2012 [Member] Starter | Points: 25

Up
0
Down
hi..
Try this below link....i hope it will be useful

http://www.dotnetcode.in/2011/01/import-excel-table-into-sql-table.html

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 3/20/2013 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Try this Code.


OleDbConnection con;
OleDbCommand com;
SqlCommand Cmmd;
SqlConnection Sqlcon;
string str;
protected void Page_Load(object sender, EventArgs e)
{

}


private DataTable dtBind()
{
DataTable dt = new DataTable();
com = new OleDbCommand();
con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source='d:\\test.xls';Extended Properties=Excel 8.0;");
con.Open();
com.Connection = con;
str = "Select * from [sheet1$]";
OleDbDataAdapter oleda = new OleDbDataAdapter(str, con);
oleda.Fill(dt);
return dt;
}





protected void Bt_Submit_Click(object sender, EventArgs e)
{
try
{
//DataTable dt1 = new DataTable();
//dt1 = dtBind();
//if (Convert.ToInt32(dt1.DefaultView[0][0]) == 1)
//{
DataTable dt=new DataTable ();
dt=dtBind();
if(dt.Rows.Count>0)
{
Cmmd = new SqlCommand();
Sqlcon = new SqlConnection("Your ConnectionString");

for (int i = 0; i <= dt.Rows.Count-1; i++)
{
Sqlcon.Open();
Cmmd.Connection = Sqlcon;
str = "Insert into [Table1] (A1,B2,C3,D4,E5,F6,G7,H8,I9,J10) values('" + dt.DefaultView[0][0] + "','" + dt.DefaultView[0][1] + "','" + dt.DefaultView[0][2] + "','" + dt.DefaultView[0][3] + "','" + dt.DefaultView[0][4] + "','" + dt.DefaultView[0][5] + "','" + dt.DefaultView[0][6] + "','" + dt.DefaultView[0][7] + "','" + dt.DefaultView[0][8] + "','" + dt.DefaultView[0][9] + "')";
Cmmd.CommandText = str;
Cmmd.ExecuteNonQuery();
Sqlcon.Close();
}

for (int j = 0; j <= dt.Rows.Count - 1; j++)
{
Sqlcon.Open();
Cmmd.Connection = Sqlcon;
str = "Insert into [Table2] (K11,L12,M13,N14,O15,P16,Q17,R18,S19,T20) values('" + dt.DefaultView[0][10] + "','" + dt.DefaultView[0][11] + "','" + dt.DefaultView[0][12] + "','" + dt.DefaultView[0][13] + "','" + dt.DefaultView[0][14] + "','" + dt.DefaultView[0][15] + "','" + dt.DefaultView[0][16] + "','" + dt.DefaultView[0][17] + "','" + dt.DefaultView[0][18] + "','" + dt.DefaultView[0][19] + "')";
Cmmd.CommandText = str;
Cmmd.ExecuteNonQuery();
Sqlcon.Close();
}
}
//}
}
catch (Exception ex)
{

}
}
}

Mark as Answer if its helpful to you

Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response