Import data from excel to sql server

Posted by Sathya4260 under ASP.NET on 12/21/2010 | Points: 10 | Views : 5470 | Status : [Member] | Replies : 4

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

Hi,

When i tried to import the data's from excel and then upload to database, I am getting error as below:even though the selected file is closed, i think while browsing the files it shows the preview, becoz of that this would may happen, kindly solve this..

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.

My code as fallows:

string sFilePath = "";
sFilePath = FileUpload1.PostedFile.FileName;
string sSourceConstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + sFilePath + "; Extended Properties=" + "\"Excel 8.0;HDR=YES;+\"";
// string sSourceConstr = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\sathya\Desktop\Employee.xls; Extended Properties=""Excel 8.0;HDR=YES;""";
string sDestConstr = ConfigurationManager.ConnectionStrings["setting"].ConnectionString;
OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);
using (sSourceConnection)
{
string sql = string.Format("Select [EmpId],[F_Name],[L_Name],[City],[EmailId],[EmpJoining] FROM [{0}]", "Sheet1$");
OleDbCommand command = new OleDbCommand(sql, sSourceConnection);
sSourceConnection.Open();
using (OleDbDataReader dr = command.ExecuteReader())
{
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))
{
bulkCopy.DestinationTableName = "Employee";
//You can mannualy set the column mapping by the following way.
bulkCopy.ColumnMappings.Add("EmpId", "EmpId");
bulkCopy.ColumnMappings.Add("F_Name", "F_Name");
bulkCopy.ColumnMappings.Add("L_Name", "L_Name");
bulkCopy.ColumnMappings.Add("City", "City");
bulkCopy.ColumnMappings.Add("EmailId", "EmailId");
bulkCopy.ColumnMappings.Add("EmpJoining", "EmpJoining");

bulkCopy.WriteToServer(dr);
}
}
}

Sathish Kumar S

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: T.saravanan on: 12/21/2010 [Member] [MVP] Silver | Points: 25

0	Hi Sathish, I think Connection cannot close it.Finally close the connection and then try it. string sFilePath = ""; sFilePath = FileUpload1.PostedFile.FileName; string sSourceConstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + sFilePath + "; Extended Properties=" + "\"Excel 8.0;HDR=YES;+\""; string sDestConstr = ConfigurationManager.ConnectionStrings["setting"].ConnectionString; OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr); using (sSourceConnection) { string sql = string.Format("Select [EmpId],[F_Name],[L_Name],[City],[EmailId],[EmpJoining] FROM [{0}]", "Sheet1$"); OleDbCommand command = new OleDbCommand(sql, sSourceConnection); sSourceConnection.Open(); using (OleDbDataReader dr = command.ExecuteReader()) { using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr)) { bulkCopy.DestinationTableName = "Employee"; //You can mannualy set the column mapping by the following way. bulkCopy.ColumnMappings.Add("EmpId", "EmpId"); bulkCopy.ColumnMappings.Add("F_Name", "F_Name"); bulkCopy.ColumnMappings.Add("L_Name", "L_Name"); bulkCopy.ColumnMappings.Add("City", "City"); bulkCopy.ColumnMappings.Add("EmailId", "EmailId"); bulkCopy.ColumnMappings.Add("EmpJoining", "EmpJoining"); bulkCopy.WriteToServer(dr); } } sSourceConnection.Close(); } Try this...I think that's your mistake... Thanks, T.Saravanan Sathya4260, if this helps please login to Mark As Answer. \| Alert Moderator

Hi Sathish,

I think Connection cannot close it.Finally close the connection and then try it.


string sFilePath = "";

sFilePath = FileUpload1.PostedFile.FileName;

string sSourceConstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + sFilePath + "; Extended Properties=" + "\"Excel 8.0;HDR=YES;+\"";

string sDestConstr = ConfigurationManager.ConnectionStrings["setting"].ConnectionString;

OleDbConnection sSourceConnection = new OleDbConnection(sSourceConstr);

using (sSourceConnection)

{

    string sql = string.Format("Select [EmpId],[F_Name],[L_Name],[City],[EmailId],[EmpJoining] FROM [{0}]", "Sheet1$");

    OleDbCommand command = new OleDbCommand(sql, sSourceConnection);

    sSourceConnection.Open();

   using (OleDbDataReader dr = command.ExecuteReader())

   {

     using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sDestConstr))

      {

       bulkCopy.DestinationTableName = "Employee";

     //You can mannualy set the column mapping by the following way.

       bulkCopy.ColumnMappings.Add("EmpId", "EmpId");

       bulkCopy.ColumnMappings.Add("F_Name", "F_Name");

     bulkCopy.ColumnMappings.Add("L_Name", "L_Name");

     bulkCopy.ColumnMappings.Add("City", "City");

      bulkCopy.ColumnMappings.Add("EmailId", "EmailId");

      bulkCopy.ColumnMappings.Add("EmpJoining", "EmpJoining");

      bulkCopy.WriteToServer(dr);

     }

  }

   sSourceConnection.Close(); 

}

Try this...I think that's your mistake...

Thanks,
T.Saravanan

Sathya4260, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sathya4260 on: 12/21/2010 [Member] Starter | Points: 25

0	no saravanan, I am still getting the error, I even used sSourceConnection.Close(); before opening the connection,,I think when selecting the file in fileupload it shows the preview whether that may be in opened state. Otherwise kindly let me know whether any other codes are there to import the data which works well. The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data. Sathish Kumar S Sathya4260, if this helps please login to Mark As Answer. \| Alert Moderator

no saravanan,

I am still getting the error, I even used sSourceConnection.Close(); before opening the connection,,I think when selecting the file in fileupload it shows the preview whether that may be in opened state.

Otherwise kindly let me know whether any other codes are there to import the data which works well.

The Microsoft Jet database engine cannot open the file ''. It is already opened exclusively by another user, or you need permission to view its data.

Sathish Kumar S

Sathya4260, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: T.saravanan on: 12/21/2010 [Member] [MVP] Silver | Points: 25

0	Hi, Try this link...http://www.aspsnippets.com/Articles/Read-and-Import-Excel-Sheet-into-SQL-Server-Database-in-ASP.Net.aspx Thanks, T.Saravanan Sathya4260, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Karthikanbarasan on: 1/6/2011 [Member] [Moderator] [Microsoft_MVP] [MVP] Silver | Points: 25

0	Hi Sathish, Try these 2 links from msdn http://support.microsoft.com/kb/321686 http://www.codeproject.com/KB/database/Excel2Sql.aspx Thanks Karthik www.f5Debug.net Sathya4260, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts