Importing data from Excel to Sql server

Posted by Nishadullas under Sql Server on 12/9/2011 | Points: 10 | Views : 4227 | Status : [Member] | Replies : 4
Hi..
I want to import data from sheet in Excel-2010 to a table in Sql server 2008 R2.
Can anyone help me with any query which will help me to sort out this.

Thank You

regards
Nishad




Responses

Posted by: maninaanee-8287 on: 12/9/2011 [Member] Starter | Points: 25

Up
0
Down
using System.Data.OleDb;
using System.Data.SqlClient;

public partial class newtask : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void btnSend_Click(object sender, EventArgs e)
{
string strConnection = "Data Source=172.18.25.55;Initial Catalog=master;Integrated Security=True";
string path = FileUploadExcel.PostedFile.FileName;
string excelConnectionString = @"Provider=Microsoft.jet.OLEDB.4.0;Data Source=C:\\trail.xls; Extended Properties=Excel 8.0;Persist Security Info=False";
OleDbConnection excelconnection=new OleDbConnection(excelConnectionString);
OleDbCommand cmd = new OleDbCommand("Select [ID],[Name],[Designation] from [Sheet1$]", excelconnection);
excelconnection.Open();
OleDbDataReader dReader;
dReader=cmd.ExecuteReader();
SqlBulkCopy sqlBulk=new SqlBulkCopy (strConnection);
sqlBulk.DestinationTableName="Excel";
sqlBulk.WriteToServer(dReader);
excelconnection.Close();

////string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;Persist Security Info=False";

}
}

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

Posted by: Sriramnandha on: 5/25/2012 [Member] Starter | Points: 25

Up
0
Down
U CAN USE SSIS PACKAGES.TO IMPORT EXCEL TO SQL SERVER 2008 R2.ONCE U CREATE A PACKAGE MEANS ITS REUSABILITY FOR ANY TIMES..


REGARDS


sriram

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

Posted by: Lacy on: 5/30/2012 [Member] Starter | Points: 25

Up
0
Down
I only know how to export the data from spreadsheet to datatable and how to import the data from datatable to spreadsheet in C# and VB.NET. I do not know whether it can help you.

http://www.e-iceblue.com/Knowledgebase/Spire.XLS/Demos/DataTable.html#twoj_fragment2-1




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

Posted by: Vforvijay on: 5/30/2012 [Member] Starter | Points: 25

Up
0
Down
HI..

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

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

Login to post response