Transfer data from Excel to MS SQL in ASP.Net
2008-10-12
The function in this article demonstrates how to perform the following data transfer data tasks:
- Read the data from an Excel spreadsheet
- Build the data transfer SQL
Additional notes:
- You need to import/using the following namespaces
- System.IO
- System.Data.OleDb;
- You need to replace the Excel filename, columns, sheetname and database table with yours in the code.
- You need to write your own function to execute the built SQL
protected void ExcelReader()
{
string filename = Server.MapPath("") + "\\MyExcel.xls"; // Provide your own file here
OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filename + ";Extended Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("Select Col1, Col2, Col3, Col4 FROM [SheetName$]", con);
DataTable dt = new DataTable();
da.Fill(dt);
StringBuilder MyStringBuilder = new StringBuilder("");
foreach (DataRow dr in dt.Rows)
{
MyStringBuilder.Append("insert into MyTable (Col1, Col2, Col3, Col4) values (");
MyStringBuilder.Append(Str2DB(dr["Col1"].ToString()) + ", ");
MyStringBuilder.Append(Str2DB(dr["Col2"].ToString()) + ", ");
MyStringBuilder.Append(Str2DB(dr["Col3"].ToString()) + ", ");
MyStringBuilder.Append(Str2DB(dr["Col4"].ToString()) + ");");
}
ImportData(MyStringBuilder.ToString()); // Provide your own ImportData function to execute the SQL here
}
public static string Str2DB(string content)
{
content = content.Replace("'", "''");
content = "'" + content + "'";
return (content);
}
|
Previous Article:
Demonstration for web-based upload in ASP.Net
Next Article:
How to apply different master page template in condition to a webpage with ASP.Net