Export Import Excel Data into Sql Server Using SqlBulkCopy-ASP.NET

Export Import Excel Data into Sql Server Using SqlBulkCopy-ASP.NET

In this example i am going to describe how to Import or insert data into Sql server from Excel spreadsheet using sqlbulkcopy method.

First of all create a Excel workbook as shown in image below and insert some data into it.

Now write this code to insert data into SQL table

public partial class _Default : System.Web.UI.Page
{
string strConnection = ConfigurationManager.ConnectionStrings
["ConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
//Create connection string to Excel work book
string excelConnectionString =
@"Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=C:\Details.xls;
Extended Properties=""Excel 8.0;HDR=YES;""";

//Create Connection to Excel work book
OleDbConnection excelConnection =
new OleDbConnection(excelConnectionString);

//Create OleDbCommand to fetch data from Excel
OleDbCommand cmd = new OleDbCommand
("Select [ID],[Name],[Location] from [Detail$]",
excelConnection);

excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();

SqlBulkCopy sqlBulk = new SqlBulkCopy(strConnection);
sqlBulk.DestinationTableName = "Details";
//sqlBulk.ColumnMappings.Add("ID", "ID");
//sqlBulk.ColumnMappings.Add("Name", "Name");
sqlBulk.WriteToServer(dReader);
}
}

If there are more columns in your database table or excel workbook and you want to insert data in some of them than you need to add ColumnMappings like this

sqlBulk.ColumnMappings.Add("ID", "ID");
sqlBulk.ColumnMappings.Add("Name", "Name");

End result will be like this

Hope this helps

Hhow to export to excel workbook all work sheets into dataset  in C# 

 protected void Page_Load(object sender, EventArgs e)

        {

            string filename = @"D:\product.XLS";

            System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(

                            "Provider=Microsoft.ACE.OLEDB.12.0; " +

                             "data source='" + filename + "';" +

                                "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");

            myConnection.Open();

            DataTable mySheets = myConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

            DataSet ds = new DataSet();

            DataTable dt;

            for (int i = 0; i < mySheets.Rows.Count; i++)

            {

                dt = makeDataTableFromSheetName(filename, mySheets.Rows[i]["TABLE_NAME"].ToString());

                ds.Tables.Add(dt);

            }

        }

        private static DataTable makeDataTableFromSheetName(string filename, string sheetName)

        {

            System.Data.OleDb.OleDbConnection myConnection = new System.Data.OleDb.OleDbConnection(

                            "Provider=Microsoft.ACE.OLEDB.12.0; " +

                             "data source='" + filename + "';" +

                                "Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\" ");

            DataTable dtImport = new DataTable();

            System.Data.OleDb.OleDbDataAdapter myImportCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [" + sheetName + "]", myConnection);

            myImportCommand.Fill(dtImport);

            return dtImport;

        }