Import data from excel to sql server

Posted by Sathya4260 under ASP.NET on 12/21/2010 | Points: 10 | Views : 4563 | Status : [Member] | Replies : 4
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


Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response