Loading excel sheet (Workbook) data to SQL database using ASP.NET C#

This will describe about loading data from excel sheet (workbook) to SQL database from an asp.net application. It will also provide solutions to all the queries related to exporting excel to SQL, using bulk copy, loading excel data to SQL database etc.

There are many ways to load excel sheet (workbook) data to SQL database. Here I am describing about using bulk copy and data reader. Before proceeding further I would like to mention that we need to insert (define) name to rows and columns of excel sheet that contains the actual data. The first row will be treated as names of columns and rest as data.

Now loading excel sheet (workbook) data to SQL database using C#. Let we have Book1.xls excel file that has our data and we need to load this data to tbl_Emp_details table of our SQL database. Let the excel sheet has five columns i.e. name, sex, age, address and qualification. Also our SQL database has same columns along with ID column (primary key). Please note that if you are using bulk copy command then the column sequence of excel sheet and SQL data table must be same. If you are using primary key column (Identity column) then make this column the last column of the table because the bulk copy client will insert first column data of data reader into the first column of SQL data table and so on. If you have defined the first column as Identity column (primary key) then the data of first column of excel sheet will be skipped and the data of remaining columns will be inserted in the sequence of data reader columns. Before loading the excel file data do these things:

1. Select all rows and columns of excel sheet and click on ‘Insert’ menu and select ‘Names’ then ‘Define’.
2. In the popup window define (type) name of table (e.g. myExcelTable) in ‘Names in workBook’ and click ‘OK’.

We define name (Insere Name) is because the selected rows and columns will be treated as a table and we can execute select command on it. Now the defined name will be the name of the table and the first row of excel sheet will be treated as the names of columns and remaining rows as data. If you do not define name then you will get the error as “The Microsoft Jet database engine could not find the object 'myExcelTable'. Make sure the object exists and that you spell its name and the path name correctly.”

Define connection string in webconfig file for SQL database (In example it is for express database)

<connectionStrings> <add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=DataDirectory\ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient" /> </connectionStrings>

Now add references of Oledb and SqlClient to the code behind file.

using System.Data.OleDb;
using System.Data.SqlClient;


Now define a string variable to store connection string for Oledb connection and assign it the proper string. Define another string variable and assign sql connection string (connection string defined in webconfig file) to it. Now initialize a new Oledb connection using Oledb Connection string and open it. Define Oledb command and initialize it with select command. The table name in select command will be the name that we had defined (Inserted) by selecting rows and columns of excel sheet. Note: First row of excel sheet will be treated as the names of columns and we will select these columns only in our select command.
Now using data reader and bulk copy command we will insert data into SQL database.


//drive conniction string from webconfig file
string SQLconnStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
//define connectionstring and excel file name for Oledb database engine
string OledbConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=c:/Book1.xls;" + "Extended Properties=Excel 8.0;";
//Oledb connection
OleDbConnection myOLEDBConn = new OleDbConnection(OledbConStr);
try
{

myOLEDBConn.Open();
//select data (rows) from excel sheet (workbook)
OleDbCommand myOLEDBCmnd = new OleDbCommand("select name, sex, age, address, qualification from myExcelTable", myOLEDBConn);

using (OleDbDataReader dbRdr = myOLEDBCmnd.ExecuteReader())
{
//save to sql database
using (SqlBulkCopy mysqlblkcpy = new SqlBulkCopy(SQLconnStr))
{
//define destination table name
mysqlblkcpy.DestinationTableName = "tbl_Emp_Details";
//define batch size in order to make operation easy
mysqlblkcpy.BatchSize = 500;
mysqlblkcpy.WriteToServer(dbRdr);
}
}


}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}



If we want more reliable method to load excel data to SQL database then we should use simple insert command instead of bulk copy command.

Note: If you receive the error: "Could not find installable ISAM." It means that your Oledb Connection string is not correct. Either you have misspell something or
Extended Properties=Excel 8.0; version is incorrect. Try the version 11.0 in place of 8.0 or correct spellings.