从Excel读取数据并将其存储在Sql服务器数据库中 [英] Read the data from Excel and store it in Sql server DB

问题描述

朋友，

任何人都可以帮助我，并告诉我如何解决以下问题:

我有一张Excel工作表.我需要从Excel工作表中读取数据.字段是制造商，零件号，描述和价格.我需要帮助来从Excel工作表中读取数据并将其存储在基于制造商和零件号的sqlserver数据库中.

我试图实现逻辑，但是当我从Excel工作表中读取数据并将其分配给数据集时，我遇到了问题.
问题1:
显示了行数2754，但是在我的excel工作表中只有5行可用.

OleDbConnection oledbCon =新的OleDbConnection(strCon);
oledbCon.Open();
OleDbCommand oledbCmd = oledbCon.CreateCommand();
oledbCmd.CommandText =选择*来自[Sheet1 $]";
DataSet ds = new DataSet();
OleDbDataAdapter oledbDa =新的OleDbDataAdapter(oledbCmd);
oledbDa.Fill(ds，"TempExcel");
ds.Tables [0] .Rows.Count

问题2:
如果Excel工作表中的价格字段包含字母letter，则当我从价格字段中获取记录时，它显示为空值.

Hi Friends,

Anyone please help me and tell me how to resolve the issues mentioned below:

I got an Excel sheet. I need to read data from excel sheet. Fields are manufacturer, partnumber, description, and price. I need help to read the data from Excel sheet and store it in a sqlserver database based on the manufacturer and partnumber.

I was trying to implement the logic but when I read the data from excel sheet and assign it into dataset i face issues.
Issue 1:
The row count 2754 is shown, but in my excel sheet only 5 rows are available.

OleDbConnection oledbCon = new OleDbConnection(strCon);
oledbCon.Open();
OleDbCommand oledbCmd = oledbCon.CreateCommand();
oledbCmd.CommandText = "SELECT * FROM [Sheet1$]";
DataSet ds = new DataSet();
OleDbDataAdapter oledbDa = new OleDbDataAdapter(oledbCmd);
oledbDa.Fill(ds, "TempExcel");
ds.Tables[0].Rows.Count

Issue 2:
If price fields in Excel sheet contains alphabet leter, when I fetch the record from price field it shows null value.

推荐答案

;
DataSet ds = new DataSet();
OleDbDataAdapter oledbDa =新的OleDbDataAdapter(oledbCmd);
oledbDa.Fill(ds，"TempExcel");
ds.Tables [0] .Rows.Count

问题2:
如果Excel工作表中的价格字段包含字母letter，则当我从价格字段中获取记录时，它显示为空值.

";
DataSet ds = new DataSet();
OleDbDataAdapter oledbDa = new OleDbDataAdapter(oledbCmd);
oledbDa.Fill(ds, "TempExcel");
ds.Tables[0].Rows.Count

Issue 2:
If price fields in Excel sheet contains alphabet leter, when I fetch the record from price field it shows null value.

您可以尝试在连接字符串中使用IMEX = 1扩展属性来解决第二个问题吗.

Can you try IMEX=1 extended property in connection string to solve the second issue.

您的临时表是根据在
中找到的值创建的 列.看来您的价格栏正在创建中，
不是没有原因的，作为数字字段.所以当尝试
如果要插入其中包含字母的价格值，插入将失败.尝试将价格列强制转换为
，而不是选择*" 一个char或varchar.那应该解决价格缺失的问题.第一个问题需要一个针对您一直存在的值的where子句(其中partnumber不为null或len(partnumber)> 0)

Your temp table is created based on the values found in the
columns. It appears your price column is being created,
not without cause, as a numeric field. So when the attempt
is made to insert the price value that has a letter in it, the insert fails. Instead of "Select *", try casting the price column into
a char or varchar. That should solve the missing price issue. The first issue requires a where clause against a value you know is always present (where partnumber is not null or where len(partnumber)>0)

这篇关于从Excel读取数据并将其存储在Sql服务器数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！