how to import excel data tosql
how to import an Excel file into Sql C# code windows
Loading
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
Abhishek JaiswalPosted Mar 17, 2015, 6:01 AM
You can use, any of these approaches
- SQL Insert Task based on SSIS
- SQL bulk query operations
- SQL Server configuration manager
- Import & Export option
- Using conversion codes at client side
For Further details go through my articlehttp://www.c-sharpcorner.com/UploadFile/2072a9/import-data-from-excel-to-sql-server-ssis/
:)
Rahul BansalPosted Mar 17, 2015, 4:47 AM
//-------------------------------------
string strConn = "";
switch (Path.GetExtension(openFileDialog.FileName))
{
case ".xls": //Excel 1997-2003
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + openFileDialog.FileName
+ ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\"";
break;
case ".xlsx": //Excel 2007-2010
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + openFileDialog.FileName
+ ";Extended Properties=\"Excel 12.0 xml;HDR=Yes;IMEX=1\"";
break;
}
//Get the data from the excel sheet1 which is default
string query = "select * from [Sheet1$]";
// Add System.Data.OleDb;
OleDbConnection objConn;
OleDbDataAdapter oleDA;
DataTable dt = new DataTable();
objConn = new OleDbConnection(strConn);
objConn.Open();
oleDA = new OleDbDataAdapter(query, objConn);
oleDA.Fill(dt);
objConn.Close();
oleDA.Dispose();
objConn.Dispose();
//Save the dt value in to the database
//------------------------------------