将excel数据导入ms sql数据库时,excel有一列数据中间有空格,导致这一列空格后的数据无法导入,请求帮助。先谢谢。
代码如下:
string strpath = Server.MapPath("~/up_ls/") + "xxxxxx.xls";
string mystring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + strpath + "';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
string ConnectionString = ConfigurationManager.AppSettings["DBConnectionString"];
SqlConnection Connection = new SqlConnection(ConnectionString); ;
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
Connection.Open();
}
SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT Id,villages,question FROM DateTable", Connection);
DataTable dataTable1 = new DataTable();
SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
sqlDA1.Fill(dataTable1);
int zd=1;
foreach (DataRow dataRow in myDs.Tables[0].Rows)
{
dataRow1["Id"] = dataRow["Id"].ToString().Trim();
dataRow1["villages"] = dataRow["villages"].ToString().Trim();
dataRow1["question"] = dataRow["question"].ToString().Trim();
dataTable1.Rows.Add(dataRow1);
zd++;
} //Console.WriteLine("新插入 " + dataTable1.Rows.Count.ToString() + " 条记录");
sqlDA1.Update(dataTable1);
代码如下:
string strpath = Server.MapPath("~/up_ls/") + "xxxxxx.xls";
string mystring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + strpath + "';Extended Properties=Excel 8.0";
OleDbConnection cnnxls = new OleDbConnection(mystring);
OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);
DataSet myDs = new DataSet();
myDa.Fill(myDs);
string ConnectionString = ConfigurationManager.AppSettings["DBConnectionString"];
SqlConnection Connection = new SqlConnection(ConnectionString); ;
if (Connection == null)
{
Connection = new SqlConnection(ConnectionString);
}
if (Connection.State.Equals(ConnectionState.Closed))
{
Connection.Open();
}
SqlDataAdapter sqlDA1 = new SqlDataAdapter(@"SELECT Id,villages,question FROM DateTable", Connection);
DataTable dataTable1 = new DataTable();
SqlCommandBuilder sqlCB1 = new SqlCommandBuilder(sqlDA1);
sqlDA1.Fill(dataTable1);
int zd=1;
foreach (DataRow dataRow in myDs.Tables[0].Rows)
{
dataRow1["Id"] = dataRow["Id"].ToString().Trim();
dataRow1["villages"] = dataRow["villages"].ToString().Trim();
dataRow1["question"] = dataRow["question"].ToString().Trim();
dataTable1.Rows.Add(dataRow1);
zd++;
} //Console.WriteLine("新插入 " + dataTable1.Rows.Count.ToString() + " 条记录");
sqlDA1.Update(dataTable1);
这句excel的question这一类中有空格
要是手动控制下 感觉工作量 也不会大多少
另外 数据库中 是否对question列 有什么设置 看看插入数据库的时候 返回值 是什么
和table的行数 是否一致
比如excel中的列question中有如下字符“我 们”,中间有一个空格。那道ms sql中只有“我”,后面的没有了。
谢谢您的回复!