how to export the excel sheet data into sqlserver

Question

i have excel sheet in that records are there. i want to export the excel sheet into sqlserver. how to do.please send the code in asp.net with csharp

Munesh Sharma · Answer

http://dotnet-munesh.blogspot.in/2014/05/how-to-import-excel-file-to.html

Marina Eberhardt · Answer

Hi, here is a pretty simple way that you use (you will need this C# assembly for excel file formats ). DataTable table = ExcelFile .Load( "excel file's paths" ) .Worksheets .ActiveWorksheet .CreateDataTable( new CreateDataTableOptions ( "A1" , 200, 4)); So you just export your excel's sheet data to a DataTable object and after that just use an SqlBulkCopy .

Satyapriya Nayak · Answer

Untitled Page Insert Data         using System; using System.Collections; using System.Configuration; using System.Data; 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; using System.IO; using System.Data.OleDb; using System.Data.SqlClient; using System.Text; namespace Excel_data_gridview_db1 { public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; protected void insertdata_Click(object sender, EventArgs e) { OleDbConnection conn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("test.xls") + ";Extended Properties=Excel 8.0"); OleDbCommand com = new OleDbCommand("select * from [Sheet1$]", conn); conn.Open(); OleDbDataReader reader = com.ExecuteReader(); string Name = ""; string RollNumber = ""; while (reader.Read()) { Name = valid(reader, 0); RollNumber = valid(reader, 1); insertdataintosql(Name, RollNumber); } conn.Close(); l1.Text = "Records Inserted successfully"; } protected string valid(OleDbDataReader reader, int str1) { object val = reader[str1]; if (val != DBNull.Value) return val.ToString(); else return Convert.ToString(0); } public void insertdataintosql(string Name, string RollNumber) { SqlConnection conn = new SqlConnection(connStr); SqlCommand com = new SqlCommand(); com.Connection = conn; com.CommandText = "insert into student(Name,RollNumber)values(@Name,@RollNumber)"; com.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name; com.Parameters.Add("@RollNumber", SqlDbType.VarChar).Value = RollNumber; com.CommandType = CommandType.Text; conn.Open(); com.ExecuteNonQuery(); conn.Close(); } } }

White Evaln · Answer

Below code requires a datatable already existing in your database, and the datatable has the same structure with the original Excel file. You can add this .net excel component first. and then export excel to sql by below code: private void Form1_Load( object sender, System. EventArgs e) { Workbook workbook = new Workbook (); workbook.LoadFromFile( @"..\..\DataTableSample.xls" ); Worksheet sheet = workbook.Worksheets[0]; DataTable data= sheet.ExportDataTable(); //set the string connection to be the local databasestring string connectionStr= "" ; SqlConnection conn= new SqlConnection (connectionStr); for ( int i=0;i { DataRow row=data.Rows[i]; int columnCount=data.Columns.Count; string [] columns= new string [columnCount]; for ( int j=0;j { columns[j]=row[j].ToString(); } // suppose there are only three columns in your datatable SqlCommand command= new SqlCommand ( "insert into DataTableSample(column1,column2,column3) values('" +columns[0]+ "','" +columns[1]+ " ','" +columns[2]+ "')" ); command.ExecuteNonQuery(); } conn.Close(); } Normal 0 false false false EN-US ZH-CN X-NONE /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-parent:"";mso-padding-alt:0cm 5.4pt 0cm 5.4pt;mso-para-margin-top:0cm;mso-para-margin-right:0cm;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0cm;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;} Normal 0 false false false EN-US ZH-CN X-NONE /* Style Definitions */ table.MsoNormalTable{mso-style-name:"Table Normal";mso-tstyle-rowband-size:0;mso-tstyle-colband-size:0;mso-style-noshow:yes;mso-style-priority:99;mso-style-parent:"";mso-padding-alt:0cm 5.4pt 0cm 5.4pt;mso-para-margin-top:0cm;mso-para-margin-right:0cm;mso-para-margin-bottom:10.0pt;mso-para-margin-left:0cm;line-height:115%;mso-pagination:widow-orphan;font-size:11.0pt;font-family:"Calibri","sans-serif";mso-ascii-font-family:Calibri;mso-ascii-theme-font:minor-latin;mso-hansi-font-family:Calibri;mso-hansi-theme-font:minor-latin;mso-bidi-font-family:"Times New Roman";mso-bidi-theme-font:minor-bidi;}

venkata kumar · Answer

hi check fallowing code datatable dt=new datatable(); string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filepath + ";Extended Properties=Excel 12.0;"; //Create Connection to Excel work book OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); excelConnection.Open(); OleDbDataAdapter da = new OleDbDataAdapter("Select [ISBN/Code],[Title],[Publisher],[Author],[Qty],[Price],[Rate In INR],[Disc%],[Value In INR] from [Sheet1$]", excelConnection); da.Fill(dt); excelconnectionstring.close();

how to export the excel sheet data into sqlserver

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)