Hi,
I am trying to import data from Excel sheet to the database referring to http://www.c-sharpcorner.com/UploadFile/0c1bb2/inserting-excel-file-records-into-sql-server-database-using/ & other sites. This is the Code :
// Connection String to connect to EXCEL
private void ExcelConn(string FilePath)
{
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'", FilePath);
Econ = new OleDbConnection(constr);
}
// Read & Insert Excel File records to DB
private void InsertExcelRecords(string filePath)
{
// connect to Excel
ExcelConn(filePath);
string query = string.Format("Select * from Sheet1$");
OleDbCommand Ecom = new OleDbCommand(query, Econ);
Econ.Open();
//string sheet1 = Econ.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable sheets = Econ.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
/*
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(query, Econ);
oda.Fill(ds); // ERROR ON THIS LINE
Econ.Close();
DataTable dt = ds.Tables[0];
ImportedGrid.DataSource = dt;
ImportedGrid.DataBind();
oda = null;
ds = null;*/
Ecom = null;
Econ = null;
}
The Excel file has 1 sheet named "Sheet1", but yet I cannot get the sheet here. I get error
Syntax error in FROM clause. With this statement - string query = string.Format("Select * FROM [{0}]", "Sheet1$"); I get he following error :
The Microsoft Office Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly.In both the cases the DataTable sheets has 9 cols & 0 Rows.
Can you help me find this error cause ?
Also the article saves the records directly to DB. I wish to update the records to my existing DbSet & then save changes to the DB. How can that be achieved ? Which method is preferred ?
Kindly help me solve the problem.
Thanks