Importing Excel data into sql database server

See more:

C#

Hi,

I am trying to import excel data into sql server using c# and I am getting conflicts with data set column names which are having dots (like Lev. 1 Fam.,Lev. 1 Fam. Description). Can anyone please suggest me how to overcome from this with any solution asap.

OP code from comment (as is)

[AcceptVerbs(HttpVerbs.Post)]
        public ActionResult Upload(HttpPostedFileBase uploadFile)
        {
            StringBuilder strValidations = new StringBuilder(string.Empty);
            try
            {
                if (uploadFile.ContentLength > 0)
                {
                    string tempDirectory = "/TempImportedData"; // your code goes here

                    bool isExists = System.IO.Directory.Exists(Server.MapPath(tempDirectory));
                    if (!isExists)
                        System.IO.Directory.CreateDirectory(Server.MapPath(tempDirectory));

                    string filePath = Path.Combine(HttpContext.Server.MapPath(tempDirectory),
                    Path.GetFileName(uploadFile.FileName));
                    uploadFile.SaveAs(filePath);
                    DataSet ds = new DataSet();
                    //A 32-bit provider which enables the use of

                    string ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";
                    using (OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString))
                    {
                        conn.Open();
                        using (DataTable dtExcelSchema = conn.GetSchema("Tables"))
                        {
                            string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                            string query = "SELECT * FROM [" + sheetName + "]";
                            OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn);
                            //DataSet ds = new DataSet();
                            adapter.Fill(ds, "ProductImportDetails");
                            if (ds.Tables.Count > 0)
                            {
                                if (ds.Tables[0].Rows.Count > 0)
                                {
                                    for (int i = 1; i < ds.Tables[0].Rows.Count; i++)
                                    {
                                        string cmmfCode = ds.Tables[0].Rows[i]["CMMF Code"].ToString();
                                        ProductImportDetails ImportDetails = _productService.GetByCmmfCode(cmmfCode);
                                        if (ImportDetails == null)
                                        {
                                            ProductImportDetails newImportDetails = new ProductImportDetails();
                                            //newImportDetails.ProductId = 1;
                                            newImportDetails.CmmfCode = ds.Tables[0].Rows[i]["CMMF Code"].ToString();
                                            Product product = _productService.GetByDispatchCode(ds.Tables[0].Rows[i]["CMMF Code"].ToString());
                                            if (product != null)
                                            {
                                                newImportDetails.ProductId = product.Id;
                                            }
                                            else if (product == null)
                                            {
                                                continue;
                                            }
                                            newImportDetails.CmmfDescription = ds.Tables[0].Rows[i]["CMMF description (EN)"].ToString();
                                            newImportDetails.CmmfOwnerDesc = ds.Tables[0].Rows[i]["CMMF Description (Owner)"].ToString();
                                            newImportDetails.CommercialCode = ds.Tables[0].Rows[i]["Commercial Code"].ToString();
                                            newImportDetails.SimplifiedCode = ds.Tables[0].Rows[i]["Simplified Commercial code"].ToString();
                                            newImportDetails.IndustrialCode = ds.Tables[0].Rows[i]["Industrial Code"].ToString();
                                            newImportDetails.CmmfType = ds.Tables[0].Rows[i]["CMMF Type"].ToString();
                                            if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Product Owner"].ToString()))
                                            {
                                                newImportDetails.ProductOwner = int.Parse(ds.Tables[0].Rows[i]["Product Owner"].ToString());
                                            }
                                            newImportDetails.ProductLine = ds.Tables[0].Rows[i]["Product line"].ToString();
                                            if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["General Status"].ToString()))
                                            {
                                                newImportDetails.GeneralStatus = int.Parse(ds.Tables[0].Rows[i]["General Status"].ToString());
                                            }
                                            newImportDetails.Unit = ds.Tables[0].Rows[i]["Unit"].ToString();

                                            if (ds.Tables[0].Rows[i]["Reporting Flag"].ToString() == "Y")
                                            {
                                                newImportDetails.ReportingFlag = true;
                                            }
                                            else if (ds.Tables[0].Rows[i]["Reporting Flag"].ToString() == "N")
                                            {
                                                newImportDetails.ReportingFlag = false;
                                            }
                                            else
                                            {
                                                newImportDetails.ReportingFlag = false;
                                            }
                                            if (ds.Tables[0].Rows[i]["Sourced"].ToString() == "N")
                                            {
                                                newImportDetails.Sourced = false;
                                            }
                                            else if (ds.Tables[0].Rows[i]["Sourced"].ToString() == "Y")
                                            {
                                                newImportDetails.Sourced = true;
                                            }
                                            else
                                            {
                                                newImportDetails.Sourced = false;
                                            }
                                            if (ds.Tables[0].Rows[i]["Set Split"].ToString() == "N")
                                            {
                                                newImportDetails.SetSplit = false;
                                            }
                                            else if (ds.Tables[0].Rows[i]["Set Split"].ToString() == "Y")
                                            {
                                                newImportDetails.SetSplit = true;
                                            }
                                            else
                                            {
                                                newImportDetails.SetSplit = false;
                                            }
                                            newImportDetails.Model = ds.Tables[0].Rows[i]["Model"].ToString();
                                            newImportDetails.ModelDescription = ds.Tables[0].Rows[i]["Model description"].ToString();
                                            newImportDetails.Range = ds.Tables[0].Rows[i]["Range"].ToString();
                                            newImportDetails.RangeDescription = ds.Tables[0].Rows[i]["Range description"].ToString();
                                            newImportDetails.RRI = ds.Tables[0].Rows[i]["RRI"].ToString();
                                            newImportDetails.RRIDescription = ds.Tables[0].Rows[i]["RRI description"].ToString();
                                            newImportDetails.ProdSite = ds.Tables[0].Rows[i]["Prod Site"].ToString();
                                            newImportDetails.ProdSiteDescription = ds.Tables[0].Rows[i]["Prod Site Description"].ToString();
                                            newImportDetails.LevelOneFam = ds.Tables[0].Rows[i]["Lev. 1 Fam."].ToString();
                                            newImportDetails.LevelOneFamDescripiton = ds.Tables[0].Rows[i]["Lev. 1 Fam. Description"].ToString();
                                            newImportDetails.LevelTwoFam = ds.Tables[0].Rows[i]["Lev. 2 Fam."].ToString();
                                            newImportDetails.LevelTwoFamDescription = ds.Tables[0].Rows[i]["Lev. 2 Fam. Description"].ToString();
                                            newImportDetails.ValidationStatus = ds.Tables[0].Rows[i]["Validation status"].ToString();
                                            newImportDetails.Origin = ds.Tables[0].Rows[i]["Origin"].ToString();
                                            if (ds.Tables[0].Rows[i]["Product with packaging composition"].ToString() == "N")
                                            {
                                                newImportDetails.PackagingComposition = false;
                                            }
                                            else if (ds.Tables[0].Rows[i]["Product with packaging composition"].ToString() == "Y")
                                            {
                                                newImportDetails.PackagingComposition = true;
                                            }
                                            else
                                            {
                                                newImportDetails.PackagingComposition = false;
                                            }
                                            if (ds.Tables[0].Rows[i]["Product with sales"].ToString() == "N")
                                            {
                                                newImportDetails.ProductWithSales = false;
                                            }
                                            else if (ds.Tables[0].Rows[i]["Product with sales"].ToString() == "Y")
                                            {
                                                newImportDetails.ProductWithSales = true;
                                            }
                                            else
                                            {
                                                newImportDetails.ProductWithSales = false;
                                            }
                                            newImportDetails.MaterialCode = ds.Tables[0].Rows[i]["Material Code"].ToString();
                                            newImportDetails.MaterialDescription = ds.Tables[0].Rows[i]["Material Description"].ToString();
                                            // newImportDetails.ComponentWeight = null ? 0 : int.Parse(ds.Tables[0].Rows[i]["Component Weight"].ToString());
                                            if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Component Weight"].ToString()))
                                            {
                                                newImportDetails.ComponentWeight = Decimal.Parse(ds.Tables[0].Rows[i]["Component Weight"].ToString());
                                            }
                                            if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Packaging Level"].ToString()))
                                            {
                                                newImportDetails.PackagingLevel = int.Parse(ds.Tables[0].Rows[i]["Packaging Level"].ToString());
                                            }
                                            if (ds.Tables[0].Rows[i]["Without Level 1"].ToString() == "N")
                                            {
                                                newImportDetails.WithoutLevelOne = false;
                                            }
                                            else if (ds.Tables[0].Rows[i]["Without Level 1"].ToString() == "Y")
                                            {
                                                newImportDetails.WithoutLevelOne = true;
                                            }
                                            else
                                            {
                                                newImportDetails.WithoutLevelOne = false;
                                            }
                                            if (ds.Tables[0].Rows[i]["Without Level 2"].ToString() == "N")
                                            {
                                                newImportDetails.WithoutLevelTwo = false;
                                            }
                                            else if (ds.Tables[0].Rows[i]["Without Level 2"].ToString() == "Y")
                                            {
                                                newImportDetails.WithoutLevelTwo = true;
                                            }
                                            else
                                            {
                                                newImportDetails.WithoutLevelTwo = false;
                                            }

                                            _productService.AddImportDetails(newImportDetails, User.Identity.Name);
                                        }
                                        else
                                        {
                                            if (ImportDetails != null)
                                            {
                                                ImportDetails = new ProductImportDetails();
                                                //ImportDetails.ProductId = 1;
                                                ImportDetails.CmmfCode = ds.Tables[0].Rows[i]["CMMF Code"].ToString();
                                                Product product = _productService.GetByDispatchCode(ImportDetails.CmmfCode);
                                                if (product != null)
                                                {
                                                    ImportDetails.ProductId = product.Id;
                                                }
                                                else if (product == null)
                                                {
                                                    continue;
                                                }
                                                ImportDetails.CmmfDescription = ds.Tables[0].Rows[i]["CMMF description (EN)"].ToString();
                                                ImportDetails.CmmfOwnerDesc = ds.Tables[0].Rows[i]["CMMF Description (Owner)"].ToString();
                                                ImportDetails.CommercialCode = ds.Tables[0].Rows[i]["Commercial Code"].ToString();
                                                ImportDetails.SimplifiedCode = ds.Tables[0].Rows[i]["Simplified Commercial code"].ToString();
                                                ImportDetails.IndustrialCode = ds.Tables[0].Rows[i]["Industrial Code"].ToString();
                                                ImportDetails.CmmfType = ds.Tables[0].Rows[i]["CMMF Type"].ToString();
                                                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Product Owner"].ToString()))
                                                {
                                                    ImportDetails.ProductOwner = int.Parse(ds.Tables[0].Rows[i]["Product Owner"].ToString());
                                                }
                                                ImportDetails.ProductLine = ds.Tables[0].Rows[i]["Product line"].ToString();
                                                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["General Status"].ToString()))
                                                {
                                                    ImportDetails.GeneralStatus = int.Parse(ds.Tables[0].Rows[i]["General Status"].ToString());
                                                }
                                                ImportDetails.Unit = ds.Tables[0].Rows[i]["Unit"].ToString();

                                                if (ds.Tables[0].Rows[i]["Reporting Flag"].ToString() == "Y")
                                                {
                                                    ImportDetails.ReportingFlag = true;
                                                }
                                                else if (ds.Tables[0].Rows[i]["Reporting Flag"].ToString() == "N")
                                                {
                                                    ImportDetails.ReportingFlag = false;
                                                }
                                                else
                                                {
                                                    ImportDetails.ReportingFlag = false;
                                                }
                                                if (ds.Tables[0].Rows[i]["Sourced"].ToString() == "N")
                                                {
                                                    ImportDetails.Sourced = false;
                                                }
                                                else if (ds.Tables[0].Rows[i]["Sourced"].ToString() == "Y")
                                                {
                                                    ImportDetails.Sourced = true;
                                                }
                                                else
                                                {
                                                    ImportDetails.Sourced = false;
                                                }
                                                if (ds.Tables[0].Rows[i]["Set Split"].ToString() == "N")
                                                {
                                                    ImportDetails.SetSplit = false;
                                                }
                                                else if (ds.Tables[0].Rows[i]["Set Split"].ToString() == "Y")
                                                {
                                                    ImportDetails.SetSplit = true;
                                                }
                                                else
                                                {
                                                    ImportDetails.SetSplit = false;
                                                }
                                                ImportDetails.Model = ds.Tables[0].Rows[i]["Model"].ToString();
                                                ImportDetails.ModelDescription = ds.Tables[0].Rows[i]["Model description"].ToString();
                                                ImportDetails.Range = ds.Tables[0].Rows[i]["Range"].ToString();
                                                ImportDetails.RangeDescription = ds.Tables[0].Rows[i]["Range description"].ToString();
                                                ImportDetails.RRI = ds.Tables[0].Rows[i]["RRI"].ToString();
                                                ImportDetails.RRIDescription = ds.Tables[0].Rows[i]["RRI description"].ToString();
                                                ImportDetails.ProdSite = ds.Tables[0].Rows[i]["Prod Site"].ToString();
                                                ImportDetails.ProdSiteDescription = ds.Tables[0].Rows[i]["Prod Site Description"].ToString();
                                                ImportDetails.LevelOneFam = ds.Tables[0].Rows[i]["LevelOneFam"].ToString();
                                                ImportDetails.LevelOneFamDescripiton = ds.Tables[0].Rows[i]["LevelOneFamDescripiton"].ToString();
                                                ImportDetails.LevelTwoFam = ds.Tables[0].Rows[i]["LevelTwoFam"].ToString();
                                                ImportDetails.LevelTwoFamDescription = ds.Tables[0].Rows[i]["LevelTwoFamDescription"].ToString();
                                                ImportDetails.ValidationStatus = ds.Tables[0].Rows[i]["Validation status"].ToString();
                                                ImportDetails.Origin = ds.Tables[0].Rows[i]["Origin"].ToString();
                                                if (ds.Tables[0].Rows[i]["Product with packaging composition"].ToString() == "N")
                                                {
                                                    ImportDetails.PackagingComposition = false;
                                                }
                                                else if (ds.Tables[0].Rows[i]["Product with packaging composition"].ToString() == "Y")
                                                {
                                                    ImportDetails.PackagingComposition = true;
                                                }
                                                else
                                                {
                                                    ImportDetails.PackagingComposition = false;
                                                }
                                                if (ds.Tables[0].Rows[i]["Product with sales"].ToString() == "N")
                                                {
                                                    ImportDetails.ProductWithSales = false;
                                                }
                                                else if (ds.Tables[0].Rows[i]["Product with sales"].ToString() == "Y")
                                                {
                                                    ImportDetails.ProductWithSales = true;
                                                }
                                                else
                                                {
                                                    ImportDetails.ProductWithSales = false;
                                                }
                                                ImportDetails.MaterialCode = ds.Tables[0].Rows[i]["Material Code"].ToString();
                                                ImportDetails.MaterialDescription = ds.Tables[0].Rows[i]["Material Description"].ToString();
                                                // newImportDetails.ComponentWeight = null ? 0 : int.Parse(ds.Tables[0].Rows[i]["Component Weight"].ToString());
                                                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Component Weight"].ToString()))
                                                {
                                                    ImportDetails.ComponentWeight = Decimal.Parse(ds.Tables[0].Rows[i]["Component Weight"].ToString());
                                                }
                                                if (!string.IsNullOrEmpty(ds.Tables[0].Rows[i]["Packaging Level"].ToString()))
                                                {
                                                    ImportDetails.PackagingLevel = int.Parse(ds.Tables[0].Rows[i]["Packaging Level"].ToString());
                                                }
                                                if (ds.Tables[0].Rows[i]["Without Level 1"].ToString() == "N")
                                                {
                                                    ImportDetails.WithoutLevelOne = false;
                                                }
                                                else if (ds.Tables[0].Rows[i]["Without Level 1"].ToString() == "Y")
                                                {
                                                    ImportDetails.WithoutLevelOne = true;
                                                }
                                                else
                                                {
                                                    ImportDetails.WithoutLevelOne = false;
                                                }
                                                if (ds.Tables[0].Rows[i]["Without Level 2"].ToString() == "N")
                                                {
                                                    ImportDetails.WithoutLevelTwo = false;
                                                }
                                                else if (ds.Tables[0].Rows[i]["Without Level 2"].ToString() == "Y")
                                                {
                                                    ImportDetails.WithoutLevelTwo = true;
                                                }
                                                else
                                                {
                                                    ImportDetails.WithoutLevelTwo = false;
                                                }
                                                _productService.AddImportDetails(ImportDetails, User.Identity.Name);
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                TempData["ImportMessage"] = "Data import failed." + ex.ToString();
                return RedirectToAction("Index");
            }
            TempData["ImportMessage"] = "Data has been imported successfully.";
            return RedirectToAction("Index");
        }
    }
}