My code as follows
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);
}
private void connection()
{
sqlconn = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
con = new SqlConnection(sqlconn);
}
private void InsertExcelRecords(string FilePath)
{
ExcelConn(FilePath);
Query = string.Format("select * FROM [{0}]", "Sheet1$");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
connection();
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "tblfarmer";
objbulk.ColumnMappings.Add("farmerctscode", "farmerctscode");
objbulk.ColumnMappings.Add("farmersapcode", "farmersapcode");
objbulk.ColumnMappings.Add("farmerseason", "farmerseason");
objbulk.ColumnMappings.Add("dateofjoinseco", "dateofjoinseco");
objbulk.ColumnMappings.Add("governmentcode", "governmentcode");
objbulk.ColumnMappings.Add("firstname", "firstname");
objbulk.ColumnMappings.Add("lastname", "lastname");
objbulk.ColumnMappings.Add("surname", "surname");
objbulk.ColumnMappings.Add("gender", "gender");
objbulk.ColumnMappings.Add("dateofbirth", "dateofbirth");
objbulk.ColumnMappings.Add("birthlocation", "birthlocation");
con.Open();
objbulk.WriteToServer(Exceldt);
con.Close();
}
protected void btnimport_Click(object sender, EventArgs e)
{
string CurrentFilePath = string.Concat(Server.MapPath("~/masterdata/" +
FileUpload1.FileName));
InsertExcelRecords(CurrentFilePath);
}
when i run and select the file and upload, then error shows as follows
External table is not in the expected format.
how to solve this error "External table is not in the expected format".
What I have tried:
My code as follows
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);
}
private void connection()
{
sqlconn = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
con = new SqlConnection(sqlconn);
}
private void InsertExcelRecords(string FilePath)
{
ExcelConn(FilePath);
Query = string.Format("select * FROM [{0}]", "Sheet1$");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
DataSet ds = new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
connection();
SqlBulkCopy objbulk = new SqlBulkCopy(con);
objbulk.DestinationTableName = "tblfarmer";
objbulk.ColumnMappings.Add("farmerctscode", "farmerctscode");
objbulk.ColumnMappings.Add("farmersapcode", "farmersapcode");
objbulk.ColumnMappings.Add("farmerseason", "farmerseason");
objbulk.ColumnMappings.Add("dateofjoinseco", "dateofjoinseco");
objbulk.ColumnMappings.Add("governmentcode", "governmentcode");
objbulk.ColumnMappings.Add("firstname", "firstname");
objbulk.ColumnMappings.Add("lastname", "lastname");
objbulk.ColumnMappings.Add("surname", "surname");
objbulk.ColumnMappings.Add("gender", "gender");
objbulk.ColumnMappings.Add("dateofbirth", "dateofbirth");
objbulk.ColumnMappings.Add("birthlocation", "birthlocation");
con.Open();
objbulk.WriteToServer(Exceldt);
con.Close();
}
protected void btnimport_Click(object sender, EventArgs e)
{
string CurrentFilePath = string.Concat(Server.MapPath("~/masterdata/" +
FileUpload1.FileName));
InsertExcelRecords(CurrentFilePath);
}
when i run and select the file and upload, then error shows as follows
External table is not in the expected format.
how to solve this error "External table is not in the expected format".