Import data from Excel sheet to my database table in c # windows applications

Question

i need a help in how to import from excel sheet to my database table

I want to import from a form in my application by click on button to browse the file and select the excel file and then the file will imports to my database table
using c#

Satyapriya Nayak · Answer

Hi Younis, Try this... using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.Data.OleDb; namespace Insert_data_datagridview__access { public partial class Form1 : Form { public OleDbConnection con1; public OleDbConnection con; public void pintu(string s) { con = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0; " + "data source='" + s + " '; " + "Extended Properties=Excel 8.0;"); } public OleDbCommand com; public DataSet ds; public OleDbDataAdapter oledbda; public DataTable dt; public string str; public bool ExecuteBatchUpdate(string[] queries) { con1.Open(); OleDbTransaction trans = con1.BeginTransaction(); try { OleDbCommand cmd; foreach (string query in queries) { cmd = new OleDbCommand(query, con1, trans); cmd.ExecuteNonQuery(); } trans.Commit(); con1.Close(); return true; } catch (Exception) { trans.Rollback(); con1.Close(); return false; } } public Form1() { InitializeComponent(); string databaseName = "d:\emp.mdb"; string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName; con1 = new OleDbConnection(ConnectionString); } private void btnbrowse_Click(object sender, EventArgs e) { OpenFileDialog openfiledialog1 = new OpenFileDialog(); openfiledialog1.ShowDialog(); openfiledialog1.Filter = "allfiles|*.xls"; TextBox1.Text = openfiledialog1.FileName; } private void btndisplay_Click(object sender, EventArgs e) { pintu(TextBox1.Text); try { con.Open(); str = "select * from [sheet1$]"; com = new OleDbCommand(str, con); ds = new DataSet(); oledbda = new OleDbDataAdapter(com); oledbda.Fill(ds, "[sheet1$]"); con.Close(); DataGridView1.DataSource = ds; DataGridView1.DataMember = "[sheet1$]"; } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void btn_insert_Click(object sender, EventArgs e) { List queries = new List (); foreach (DataGridViewRow row in DataGridView1.Rows) { if (!row.IsNewRow) { queries.Add("Insert Into employee (empid,empname,sal) values ('" + row.Cells[0].Value.ToString() + "','" + row.Cells[1].Value.ToString() + "','" + row.Cells[2].Value.ToString() + "')"); } } if (ExecuteBatchUpdate(queries.ToArray())) { MessageBox.Show("Records Inserted Successful"); } else { MessageBox.Show("Inserted Failed!"); } } } } Thanks If this post helps you mark it as answer

Anil Kumar · Answer

@Satyapriya : Good Ansewer Can we do apply same with csv format ?

younis abdeljawwad · Answer

string databaseName = "d:\emp.mdb"; string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + databaseName; i use visual studio 2010 proffesional ,,how i get the name of the database like the above .mdb !!!! another question ,,the excel file extention should be in the name of the file !! i mean for example "students.xls" this is format should be in the name of the file or itis not important because the file is in excel formal @@

Import data from Excel sheet to my database table in c # windows applications

i need a help in how to import from excel sheet to my database table

Answers (3)