hi
I use below code to import excel file into database
protected void ImgloadB_Click(object sender, ImageClickEventArgs e)
{
//Upload and save the file
string excelPath = Server.MapPath("~/Files/") + Path.GetFileName(fup5.PostedFile.FileName);
fup5.SaveAs(excelPath);
string conString = string.Empty;
string extension = Path.GetExtension(fup5.PostedFile.FileName);
switch (extension)
{
case ".xls": //Excel 97-03
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case ".xlsx": //Excel 07 or higher
conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
break;
}
conString = string.Format(conString, excelPath);
using (OleDbConnection excel_con = new OleDbConnection(conString))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
//[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default.
dtExcelData.Columns.AddRange(new DataColumn[77] {
new DataColumn("Id", typeof(int)), new DataColumn("code", typeof(string)),
new DataColumn("Type",typeof(string)), new DataColumn("Transfer", typeof(string)),
new DataColumn("rahn", typeof(decimal)), new DataColumn("ejare", typeof(decimal)),
new DataColumn("TotalPrice",typeof(decimal)), new DataColumn("MeterPrice", typeof(string)),
new DataColumn("vam", typeof(string)), new DataColumn("pardakhti", typeof(string)),
new DataColumn("zirbana",typeof(string)), new DataColumn("masahat", typeof(string)),
new DataColumn("age",typeof(string)), new DataColumn("TimeTahvil", typeof(string)),
new DataColumn("MalekName", typeof(string)), new DataColumn("Malekphone", typeof(string)),
new DataColumn("MalkeMobile",typeof(string)), new DataColumn("MosName", typeof(string)),
new DataColumn("MosPhone",typeof(string)), new DataColumn("MosMobile", typeof(string)),
new DataColumn("State", typeof(string)), new DataColumn("City", typeof(string)),
new DataColumn("Zone",typeof(string)), new DataColumn("district", typeof(string)),
new DataColumn("position",typeof(string)), new DataColumn("AddressCa", typeof(string)),
new DataColumn("AddressNa", typeof(string)), new DataColumn("TotalFloor", typeof(string)),
new DataColumn("UnitFloor",typeof(string)), new DataColumn("Totalvahed", typeof(string)),
new DataColumn("Unitvahed",typeof(string)), new DataColumn("bedroom", typeof(string)),
new DataColumn("elevator", typeof(string)), new DataColumn("parking", typeof(string)),
new DataColumn("Anbari",typeof(string)), new DataColumn("komod", typeof(string)),
new DataColumn("tars",typeof(string)), new DataColumn("pasio", typeof(string)),
new DataColumn("barberkio", typeof(string)), new DataColumn("kabinet", typeof(string)),
new DataColumn("jenskaf",typeof(string)), new DataColumn("nama", typeof(string)),
new DataColumn("gaz",typeof(string)), new DataColumn("shofaj", typeof(string)),
new DataColumn("pakage", typeof(string)), new DataColumn("coolerG", typeof(string)),
new DataColumn("coolerA",typeof(string)), new DataColumn("chiler", typeof(string)),
new DataColumn("estakhr",typeof(string)), new DataColumn("sona", typeof(string)),
new DataColumn("jakozi", typeof(string)), new DataColumn("irani", typeof(string)),
new DataColumn("farangi",typeof(string)), new DataColumn("DescriptionPu", typeof(string)),
new DataColumn("DescriptionKh",typeof(string)), new DataColumn("Image1", typeof(string)),
new DataColumn("Image2", typeof(string)), new DataColumn("Image3", typeof(string)),
new DataColumn("Image4",typeof(string)), new DataColumn("Image5", typeof(string)),
new DataColumn("Video",typeof(string)), new DataColumn("change", typeof(string)),
new DataColumn("metraj", typeof(string)), new DataColumn("date", typeof(DateTime)),
new DataColumn("aparat",typeof(string)), new DataColumn("areaM", typeof(string)),
new DataColumn("posSh",typeof(string)), new DataColumn("posJ", typeof(string)),
new DataColumn("posSha", typeof(string)), new DataColumn("posGh", typeof(string)),
new DataColumn("MalekNameN",typeof(string)), new DataColumn("MalekphoneN", typeof(string)),
new DataColumn("MalekMobileN", typeof(string)), new DataColumn("Rahgiri", typeof(string)),
new DataColumn("descriptionC",typeof(string)), new DataColumn("Contractdate", typeof(string)),
new DataColumn("Expiredate",typeof(DateTime))
});
using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
string consString = ConfigurationManager.ConnectionStrings["DigimasterConnectionString"].ConnectionString;
using (SqlConnection con = new SqlConnection(consString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name
sqlBulkCopy.DestinationTableName = "dbo.estate_Archive";
con.Open();
sqlBulkCopy.WriteToServer(dtExcelData);
con.Close();
}
}
}
}
but it make below error:
Server Error in '/' Application.
External table is not in the expected format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: External table is not in the expected format.
Source Error:
Line 346: using (OleDbConnection excel_con = new OleDbConnection(conString))
Line 347: {
Line 348: excel_con.Open();
Line 349: string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
Line 350: DataTable dtExcelData = new DataTable();
|
Source File: D:\ordered site\AmlakeAbdi\AmlakeAbdi\amlak\فایل-پشتیبان.aspx.cs Line: 348
best regards
neda