Exporting Excel data directly into SQL table
Conversion of excel sheet data into SQL table is one of the most common and important issue we have in development. Often clients send their in the form of excel sheets and we need to transfer those data into an SQL table.
The real issue happens when the data to be transferred is very huge, say around 20,000 records. It is purely not practical to do it manually. There may be many solutions for this. Am presenting here one among them.
You just need a button in the design side and in the OnClick property of that button, write the name of the function in the code-behind. Let that function be "btnExport_Click".
Here is the function in the code behind. It is coded in c#.Net.
protected void btnExport_Click(object sender, EventArgs e)
{
try
{
// Connection String to Excel Workbook
string excelConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "D:\\Test.xls" + "; Extended Properties='Excel 8.0; IMEX=1; HDR=YES'";
// Create Connection to Excel Workbook
using (OleDbConnection connection = new OleDbConnection(excelConnectionString))
{
OleDbCommand command = new OleDbCommand("Select city_state,neighborhood FROM [Data$]", connection);
connection.Open();
// Create DbDataReader to Data Worksheet
using (DbDataReader dr = command.ExecuteReader())
{
// SQL Server Connection String
string sqlConnectionString = "server=PRADEEP\\SQLEXPRESS;initial catalog=DataBaseName;uid=USERID;pwd=PASSWORD;";
// Bulk Copy to SQL Server
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString))
{
bulkCopy.DestinationTableName = "Test";
bulkCopy.ColumnMappings.Add("city_state", "city_state");
bulkCopy.ColumnMappings.Add("neighborhood", "neighborhood");
bulkCopy.WriteToServer(dr);
}
}
}
Response.Write("Data exported successfully");
}
catch (Exception ex)
{
Response.Write(ex.ToString());
}
}
Things to be remembered
1. Change the excelConnectionString accrodingly.
2. Change the sqlConnectionString accordingly.
3. Change the command accordingly. Here [Data$] is the data sheet name in the excel document.
4. Be careful when specifying bulkCopy.ColumnMappings.Add(). Its general format is
bulkCopy.ColumnMappings.Add(column name in excel sheet, corresponding column name in table);
Mapping should be done correctly, otherwise wrong data will enter into wrong column.
Working
The working of this code is very simple. It just reads the excel document, maps corresponding columns in excel document and table, transfers the data fields one by one.
This code snippet is very useful indeed for the purpose I mentioned in the beginning. Try this once and get back to me in this post. I will be there to assist you.
Happy coding !!
|
No responses found. Be the first to respond and make money from revenue sharing program.
|