将Excel数据导入sql数据库服务器 [英] Importing Excel data into sql database server
本文介绍了将Excel数据导入sql数据库服务器的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试使用c＃将excel数据导入sql server并且我遇到了带有点的数据集列名冲突（如Lev .1 Fam。，Lev.1 Fam。Description）。任何人都可以建议我如何克服任何解决方案尽快。
来自评论的OP代码（原样）
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");
}
}
}
推荐答案
您好，现在您可以看到包含此内容的行数据集名称，导入时出错。错误是列名在当前表中不存在。
Hi, Now you can see the line which contains this dataset names and there is the error raised while importing. The error is "The column name does not exist in the current table."
这篇关于将Excel数据导入sql数据库服务器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
