i am trying to transfer my data from excel 2007 to SQL. But i am facing the following error.
"The Microsoft Office Access database engine could not find the object 'NewEmployees'. Make sure the object exists and that you spell its name and the path name correctly."
Even that my excel sheet name is NewEmployees. i don't understand why i am facing this error. i have the follwing connection string in web.config.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnImport_Click(object sender, EventArgs e)
{
using (OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["ExcelCon"].ConnectionString))
{
con.Open();
OleDbCommand com = new OleDbCommand("Select * from [NewEmployees]", con);
OleDbDataReader dr = com.ExecuteReader();
using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString))
{
sqlcon.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon))
{
bulkCopy.ColumnMappings.Add("[Employee Name]", "EmpName");
bulkCopy.ColumnMappings.Add("Department", "Department");
bulkCopy.ColumnMappings.Add("Address", "Address");
bulkCopy.ColumnMappings.Add("Age", "Age");
bulkCopy.ColumnMappings.Add("Sex", "Sex");
bulkCopy.DestinationTableName = "Employees";
bulkCopy.WriteToServer(dr);
}
}
dr.Close();
dr.Dispose();
}
Response.Write("Upload Successfull!");
}
}
now please tell me why i cannot access my excel file named "NewEmployees".
What is SqlBulkCopy & ColumnMappings ??? What does 'WriteToServer' Do ?
//Source DB : this reads from excel file ...
string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Database\\Book81.xlsx;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";
string select = "SELECT * FROM [Sheet1$]";
OleDbConnection ExcelConnection = new OleDbConnection(ConnectionString);
OleDbCommand Command = new OleDbCommand(select, ExcelConnection);
ExcelConnection.Open();
OleDbDataAdapter DataAdapter = new OleDbDataAdapter(Command);
DataTable table = new DataTable();
DataAdapter.Fill(table);
//Target DB : preparing the connection for the SQL ...
const string ConnectionString = @"Data Source=<SERVER_NAME>;Initial Catalog=<DATABASE_NAME>;User ID=<LOGIN_ID>;Password=<PASSWORD>";
SqlConnection Connection = new SqlConnection(ConnectionString);
SqlCommand InsertCommand = Connection.CreateCommand();
InsertCommand.CommandText = @"INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES (";
foreach (DataRow row in table.Rows)
{
//Insert the data the SQL Statement
for (int i = 0; i < table.Columns; i++, InsertCommand.CommandText += ",")
{
InsertCommand.CommandText += row[i].ToString();
}
// we would have one more extra commna, removed it and completing the query with ')'
InsertCommand.CommandText.Remove(str.Length -1, 1) + ")";
//Now execute the query to perform the insert operation ...
}
Adnan dani
Member
9 Points
55 Posts
From Excel to SQL
Dec 09, 2012 04:06 PM|LINK
i am trying to transfer my data from excel 2007 to SQL. But i am facing the following error.
"The Microsoft Office Access database engine could not find the object 'NewEmployees'. Make sure the object exists and that you spell its name and the path name correctly."
Even that my excel sheet name is NewEmployees. i don't understand why i am facing this error. i have the follwing connection string in web.config.
<connectionStrings>
<add name="ExcelOld" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|NewEmployees.xls;Extended Properties=Excel 8.0"/>
<add name="ExcelCon" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|NewEmployees.xlsx;Extended Properties=Excel 12.0"/>
<add name="Sql" connectionString=" Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|Database.mdf; Integrated Security=True; User Instance=True;"/>
</connectionStrings>
Please guide me if any body have any knowledge
ignatandrei
All-Star
134960 Points
21632 Posts
Moderator
MVP
Re: From Excel to SQL
Dec 10, 2012 03:53 AM|LINK
Code , please.
Adnan dani
Member
9 Points
55 Posts
Re: From Excel to SQL
Dec 10, 2012 11:43 AM|LINK
Following is the code of default.aspx code.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <asp:Button ID="btnImport" runat="server" Text="Import Excel Sheet" onclick="btnImport_Click" /> </div> </form> </body> </html>and here is the code of default.aspx.cs
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void btnImport_Click(object sender, EventArgs e) { using (OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["ExcelCon"].ConnectionString)) { con.Open(); OleDbCommand com = new OleDbCommand("Select * from [NewEmployees]", con); OleDbDataReader dr = com.ExecuteReader(); using (SqlConnection sqlcon = new SqlConnection(ConfigurationManager.ConnectionStrings["Sql"].ConnectionString)) { sqlcon.Open(); using (SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlcon)) { bulkCopy.ColumnMappings.Add("[Employee Name]", "EmpName"); bulkCopy.ColumnMappings.Add("Department", "Department"); bulkCopy.ColumnMappings.Add("Address", "Address"); bulkCopy.ColumnMappings.Add("Age", "Age"); bulkCopy.ColumnMappings.Add("Sex", "Sex"); bulkCopy.DestinationTableName = "Employees"; bulkCopy.WriteToServer(dr); } } dr.Close(); dr.Dispose(); } Response.Write("Upload Successfull!"); } }now please tell me why i cannot access my excel file named "NewEmployees".
aarsh
Participant
1543 Points
427 Posts
Re: From Excel to SQL
Dec 10, 2012 08:58 PM|LINK
What is SqlBulkCopy & ColumnMappings ??? What does 'WriteToServer' Do ?
//Source DB : this reads from excel file ... string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Database\\Book81.xlsx;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; string select = "SELECT * FROM [Sheet1$]"; OleDbConnection ExcelConnection = new OleDbConnection(ConnectionString); OleDbCommand Command = new OleDbCommand(select, ExcelConnection); ExcelConnection.Open(); OleDbDataAdapter DataAdapter = new OleDbDataAdapter(Command); DataTable table = new DataTable(); DataAdapter.Fill(table); //Target DB : preparing the connection for the SQL ... const string ConnectionString = @"Data Source=<SERVER_NAME>;Initial Catalog=<DATABASE_NAME>;User ID=<LOGIN_ID>;Password=<PASSWORD>"; SqlConnection Connection = new SqlConnection(ConnectionString); SqlCommand InsertCommand = Connection.CreateCommand(); InsertCommand.CommandText = @"INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES ("; foreach (DataRow row in table.Rows) { //Insert the data the SQL Statement for (int i = 0; i < table.Columns; i++, InsertCommand.CommandText += ",") { InsertCommand.CommandText += row[i].ToString(); } // we would have one more extra commna, removed it and completing the query with ')' InsertCommand.CommandText.Remove(str.Length -1, 1) + ")"; //Now execute the query to perform the insert operation ... }aarsh
Participant
1543 Points
427 Posts
Re: From Excel to SQL
Dec 10, 2012 09:01 PM|LINK
OR ....
//Source DB : this reads from excel file ... string ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\Database\\Book81.xlsx;Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";"; string select = "SELECT * FROM [Sheet1$]"; OleDbConnection ExcelConnection = new OleDbConnection(ConnectionString); OleDbCommand Command = new OleDbCommand(select, ExcelConnection); ExcelConnection.Open(); OleDbDataAdapter DataAdapter = new OleDbDataAdapter(Command); DataTable table = new DataTable(); DataAdapter.Fill(table); //Target DB : preparing the connection for the SQL ... const string ConnectionString = @"Data Source=<SERVER_NAME>;Initial Catalog=<DATABASE_NAME>;User ID=<LOGIN_ID>;Password=<PASSWORD>"; SqlConnection Connection = new SqlConnection(ConnectionString); SqlCommand InsertCommand = Connection.CreateCommand(); InsertCommand.CommandText = @"INSERT INTO TABLE_NAME (COLUMN1, COLUMN2, COLUMN3) VALUES (@col1, @col2, @colN")"; foreach (DataRow row in table.Rows) { InsertCommand.CommandText.Parameters.Clear(); InsertCommand.CommandText.Parameters.AddWithValue("col1","<SOME VALUE 1>"); InsertCommand.CommandText.Parameters.AddWithValue("col2","<SOME VALUE 2>"); InsertCommand.CommandText.Parameters.AddWithValue("colN","<SOME VALUE N>"); //Now execute the query to perform the insert operation ... }Adnan dani
Member
9 Points
55 Posts
Re: From Excel to SQL
Dec 11, 2012 11:31 AM|LINK
To understand SqlBulkCopy and CoulmnMapping visit
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopycolumnmappingcollection(v=VS.80).aspx
and for WriteToServer visit
http://msdn.microsoft.com/en-us/library/434atets.aspx
aarsh
Participant
1543 Points
427 Posts
Re: From Excel to SQL
Dec 11, 2012 12:47 PM|LINK
Well, try the provided solutions if you get a chance, and if that works, go for bulk copy, makes sense ?