I have a problem when uploading Excel file to database there is a column in which i have a alpha-numeric values or only numeric values, When reading first row if there is numeric value in my column then it only stores numeric value through out the sheet it does not pick the alpha-numeric values, But if it gets alpha-numeric value in first row then it stores all values through out the sheet
This is My Excel Sheet:
AS you can see in Picture In TRAN DETAIL column ,it easily uploads value to database but when there is alpha numeric value like marked in image it save as null.
This is my code how i am uploading excel file to database,
[HttpPost]
public JsonResult UploadExcel(SBP_NEW users, HttpPostedFileBase FileUpload)
{
List<string> data = new List<string>();
if (FileUpload != null)
{
// tdata.ExecuteCommand("truncate table OtherCompanyAssets");
if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
{
string filename = FileUpload.FileName;
string targetpath = Server.MapPath("~/Doc/");
FileUpload.SaveAs(targetpath + filename);
string pathToExcelFile = targetpath + filename;
var connectionString = "";
if (filename.EndsWith(".xls"))
{
connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
}
else if (filename.EndsWith(".xlsx"))
{
connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
}
var adapter = new OleDbDataAdapter("SELECT * FROM [SBP OST Entries$]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "ExcelTable");
DataTable dtable = ds.Tables["ExcelTable"];
string sheetName = "SBP OST Entries";
var excelFile = new ExcelQueryFactory(pathToExcelFile);
var artistAlbums = from a in excelFile.Worksheet<SBP_NEW>(sheetName) select a;
foreach (var a in artistAlbums)
{
try
{
if (a.SEQN_NUMB != "")
{
SBP_NEW TU = new SBP_NEW();
TU.SEQN_NUMB = a.SEQN_NUMB;
TU.VALU_DATE = a.VALU_DATE;
TU.DESCRIPTION = a.DESCRIPTION;
TU.TRAN_DETAIL = a.TRAN_DETAIL;
TU.TRAN_AMNT = a.TRAN_AMNT;
TU.OTST_AMNT = a.OTST_AMNT;
TU.BRAN_DEPT = a.BRAN_DEPT;
TU.OUR_THEIR = a.OUR_THEIR;
TU.REMARKS = a.REMARKS;
TU.INST_NUMB = a.INST_NUMB;
TU.TRAN_DATE = a.TRAN_DATE;
TU.RECON_MODE = a.RECON_MODE;
TU.USER_ID = a.USER_ID;
TU.SUPR_RQRD = a.SUPR_RQRD;
TU.SUPR_BY = a.SUPR_BY;
TU.SUPR_DATE = a.SUPR_DATE;
db.SBP_NEW.Add(TU);
db.SaveChanges();
}
else
{
//data.Add("<ul>");
//if (a.ID == 0 || a.ID == null) data.Add("<li> ID is required</li>");
//if (a.NAME == "" || a.NAME == null) data.Add("<li> Name is required</li>");
//data.Add("</ul>");
data.ToArray();
return Json(data, JsonRequestBehavior.AllowGet);
}
}
catch (DbEntityValidationException ex)
{
foreach (var entityValidationErrors in ex.EntityValidationErrors)
{
foreach (var validationError in entityValidationErrors.ValidationErrors)
{
Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
}
}
}
}
//deleting excel file from folder
if ((System.IO.File.Exists(pathToExcelFile)))
{
System.IO.File.Delete(pathToExcelFile);
}
return Json("success", JsonRequestBehavior.AllowGet);
}
else
{
//alert message for invalid file format
data.Add("<ul>");
data.Add("<li>Only Excel file format is allowed</li>");
data.Add("</ul>");
data.ToArray();
return Json(data, JsonRequestBehavior.AllowGet);
}
}
else
{
data.Add("<ul>");
if (FileUpload == null) data.Add("<li>Please choose Excel file</li>");
data.Add("</ul>");
data.ToArray();
return Json(data, JsonRequestBehavior.AllowGet);
}
}
