Thursday, February 19, 2015

Import data from MS Excel to SQL Server in C#



In many occasions, we need to get data from Microsoft Excel to SQL Server Database for displaying, calculating and manipulating data when developing .NET Applications. So in this case, you can follow this post to import data from MS Excel file.


 
At the beginning create a database in SQL Server using Microsoft SQL Server Management Studio in my case I created my database name as “test”.  Design and create the table using following Query.
 CREATE TABLE student
(
Student_id int,
first_name VARCHAR(255),
last_name VARCHAR(255),

); 
Create MS Excel file and save anywhere ( in my case D:\\text.xls) as shown in figure below.

 Create a new Windows Form Application Project in Visual Studio, add a button and DataGridView. 
Generate event by double clicking the button, under this method we can write the code for Import Excel File.
Add these two namespaces to your class file.
using System.Data.OleDb;
using System.Data.SqlClient;

Add the below code into the event handling method of the button.


 
private void btnImportExcelFile_Click(object sender, EventArgs e)
        {
            //database name
            string table = "student";
            // Excel sheet name
            string excelQuery = "select * from [Sheet1$]";
            try
            {
                //create  connection strings
                string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\test.xls;Extended Properties=\"Excel 8.0;HDR=Yes;\";";
                string sqlConnectionstring = "Server=localhost\\sqlexpress;Database=test;User Id=sa;Password=abc123";
                //To clear the prevois data in the database
                string resetQuery = "delete from " + table;
                SqlConnection connection = new SqlConnection(sqlConnectionstring);
                SqlCommand cmd = new SqlCommand(resetQuery, connection);
                connection.Open();
                cmd.ExecuteNonQuery();
                connection.Close();
                //copy data from the excel file
                OleDbConnection con = new OleDbConnection(connectionString);
                OleDbCommand cmdOLEDB = new OleDbCommand(excelQuery, con);
                con.Open();
                OleDbDataReader dataReader = cmdOLEDB.ExecuteReader();
                SqlBulkCopy bulkcopy = new SqlBulkCopy(sqlConnectionstring);
                bulkcopy.DestinationTableName = table;
                while (dataReader.Read())
                {
                    bulkcopy.WriteToServer(dataReader);
                }

                con.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
            fillDataGridView();
        }


// fill the data from database to datagridview
private void fillDataGridView() {
            string connectionString = "Server=localhost\\sqlexpress;Database=test;User Id=sa;Password=abc123;";
            string sql = "SELECT * FROM student";
            SqlConnection connection = new SqlConnection(connectionString);
            SqlDataAdapter dataadapter = new SqlDataAdapter(sql, connection);
            DataSet ds = new DataSet();
            connection.Open();
            dataadapter.Fill(ds, "test");
            connection.Close();
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "test";
        }

Test your project and see the result also checking in the Database.








Do not forget to leave your comments below.
 

10 comments:

  1. How to edit or change alignment in Data Grid View and export to excel.

    ReplyDelete
  2. I have tried and i getting this exception
    The Microsoft Jet database engine could not find the object 'sheet1$'. Make sure the object exists and that you spell its name and the path name correctly."}

    ReplyDelete
    Replies
    1. I have same problem with this also I changed a directory but is again I got same exception.

      Delete
    2. you need to download microsoft excel engine, then it will work

      Delete
  3. As you can see from picture first two rows in excel were not imported into table !! Why ?

    ReplyDelete
    Replies
    1. No, Check the picture clearly. Every raw was added successfully. Student_id is from 3 to 8 not 1 to 8.

      Delete
    2. Why are 1st and 2nd row not added in ?
      I want to add them all actually ?

      Delete
    3. I have kept first row as header, left second row empty then it worked.

      Delete
  4. I have read your blog its very attractive and impressive. I like it your blog.

    Java Online Training Java EE Online Training Java EE Online Training Java 8 online training Core Java 8 online training

    Java Online Training from India Java Online Training from India Core Java Training Online Core Java Training Online Java Training InstitutesJava Training Institutes

    ReplyDelete