import excel data into the sql table

Question

I have to import the data from excelsome predefined format to sql table in a day to day basis. need help for the same and or share any project related to such. thank you!!!

Satyapriya Nayak · Answer

Hi Ds Try this... 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(NameRollNumber)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(); } } } Thanks If this post helps you mark it as answer

ds ps · Answer

hello SatayPriya it did worked for string fields in excel. now what i need to upload is date  money and rate fields as well. does it is helpful for such fields? also i need to choose the file name can you help for that. Thanks anyway...

Kunal Vaishya · Answer

You can get your Excel Data Into DataSet after Get DataSet you Can Insert that data into Sql Table Example -------------------- Setp 1 : First Start visual Studion and Create new Project in C# Lnguages Step 2 : Go Design Form and put a gridview and a button on Form ; Step 3 : Use Namespace using System.Data; using System.Data.OleDb; Setp 4 : Create Event og Button Write code in private void Button1_Click( object sender EventArgs e) { OpenFileDialog op = new OpenFileDialog (); op.Filter = Excel 97 - 2003|*.xls|Excel 2007|*.xlsx ; if (op.ShowDialog() == System.Windows.Forms. DialogResult .OK) { if ( File .Exists(op.FileName)) { string [] Arr = null ; Arr = op.FileName.Split( '.' ); if (Arr.Length > 0) { if (Arr[Arr.Length - 1] == xls ) sConnectionString = Provider=Microsoft.Jet.OLEDB.4.0;Data Source= + op.FileName + ;Extended Properties='Excel 8.0;HDR=Yes;IMEX=1' ; } else if (Arr[Arr.Length - 1] == xlsx ) { sConnectionString = Provider=Microsoft.ACE.OLEDB.12.0;Data Source= + op.FileName + ;Extended Properties='Excel 12.0 Xml;HDR=YES'; ; } } FillData(); } } } public string sConnectionString; private void FillData() { if (sConnectionString.Length > 0) { OleDbConnection cn = new OleDbConnection (sConnectionString); { cn.Open(); DataTable dt = new DataTable (); OleDbDataAdapter Adpt = new OleDbDataAdapter ( select * from [sheet1$]  cn); Adpt.Fill(dt); // Insert Your Data Into SqlData Base } catch ( Exception ex) { } } } Screen Shot

Vikrant More · Answer

use SSIS package to import excel to database table for reference use this link how to create SSIS package to import data from excel. http://bi-polar23.blogspot.in/2007/08/loading-multiple-excel-files-with-ssis.html