将数据从excel导入到sql [英] import data from excel to sql
本文介绍了将数据从excel导入到sql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
i想要将数据从excel导入到sql server但是有一个条件，如果数据已经是axist我将进行更新，否则我插入新数据，这里是我的代码
string srvxl = @Provider = Microsoft.ACE.OLEDB.12.0; Data Source = d：\ Suivi \ export1111.xlsx; Extended Properties =+\Excel 12.0 Xml; HDR = Yes; \\ \\ ;
public void updateinsertsql（string path）
{
SqlConnection cn = new SqlConnection（srv）;
OleDbConnection cnn = new OleDbConnection（srvxl）;
cn.Open（）;
cnn.Open（）;
string cmdd = @从[访问] v内连接OPENROWSET选择v.Num_Doss（'Microsoft.ACE.OLEDB.12.0'，'Excel 12.0 Xml; HDR = YES; IMEX = 1 \;数据源= d：\\Suivi \\export1111.xlsx;'，'SELECT Num_Doss FROM [sheet1 $]'）AS ExcelTable on d.Num_Doss = ExcelTable.Num_Doss;
SqlCommand verif = new SqlCommand（cmdd，cn）;
SqlDataReader logread = verif.ExecuteReader（）;
if（logread.HasRows）
{
while（logread.Read（））
{
// string update = @update [Visit] set v where v .Num_Doss = + logread.GetString（0）+;
string update = @UPDATE [Visit]
SET [Visit] .Date_visit_1mois_prevu = exl.Date_visit_1mois_prevu，
[Visit] .Date_visit_1mois = exl.Date_visit_1mois，
[Visit]。 Observation_visite_1mois = exl.Observation_visite_1mois，
[访问] .Date_visit_3mois_prevu = exl.Date_visit_3mois_prevu，
[访问] .Date_visit_3mois = exl.Date_visit_3mois，
[访问] .Observation_visite_3mois = exl.Observation_visite_3mois，
[访问] .Date_visit_6mois_prevu = exl.Date_visit_6mois_prevu，
[访问] .Date_visit_6mois = exl.Date_visit_6mois，
[访问] .Observation_visite_6mois = exl.Observation_visite_6mois，
[访问] .Date_visit_1an_prevu = exl.Date_visit_1an_prevu，
[Visit] .Date_visit_1an = exl.Date_visit_1an，
[Visit] .Observation_visite_1an = exl.Observation_visite_1an，
FROM [Visit]
INNER JOIN OPENROWSET（'Microsoft.AC E.OLEDB.12.0'，'Excel 12.0;数据源= C：\ Suivi \ export.xls;'，'SELECT Date_visit_1mois_prevu，Date_visit_1mois，Observation_visite_1mois，Date_visit_3mois_prevu，Date_visit_3mois，Observation_visite_3mois，Date_visit_6mois_prevu，Date_visit_6mois，Observation_visite_6mois，Date_visit_1an_prevu，Date_visit_1an ，Observation_visite_1an FROM [sheet1 $]'）AS exlON [Visit] .Num_Doss = exl.Num_Doss
WHERE [Visit] .Num_Doss =+ logread.GetString（0）+;
SqlCommand up = new SqlCommand（update，cn）;
up.ExecuteNonQuery（）;
MessageBox.Show（miseàjouréffectuéavecusucée）;
}
}
else
{
string insrt = @insert into [Visit] Select * FROM OPENROWSET（'Microsoft.ACE.OLEDB.12.0'，' Excel 12.0;数据源= D：\ Suivi \ export1111.xlsx; HDR = YES'，'SELECT * FROM [Sheet1 $]'）;
SqlCommand ins = new SqlCommand（insrt，cn）;
ins.ExecuteNonQuery（）;
MessageBox.Show（插入éffectuéavecsuccés）;
}
}
但是当我运行我的代码时出现此错误：
无法加载32位OLE DB提供程序 Microsoft.ACE.OLEDB.12.0 - 在64位SQL Server上进行处理。
此处
SqlDataReader logread = verif.ExecuteReader（）;
help plz
解决方案
'）在Excel.Dum_Doss = ExcelTable.Num_Doss上的ExcelTable;
SqlCommand verif = new SqlCommand（cmdd，cn）;
SqlDataReader logread = verif.ExecuteReader（）;
if（logread.HasRows）
{
while（logread.Read（））
{
// string update = @update [Visit] set其中v.Num_Doss =+ logread.GetString（0）+ ;
字符串update = @UPDATE [Visit]
SET [Visit] .Date_visit_1mois_prevu = exl.Date_visit_1mois_prevu，
[Visit] .Date_visit_1mois = exl.Date_visit_1mois，
[Visit] .Observation_visite_1mois = exl.Observation_visite_1mois ，
[访问] .Date_visit_3mois_prevu = exl.Date_visit_3mois_prevu，
[访问] .Date_visit_3mois = exl.Date_visit_3mois，
[访问] .Observation_visite_3mois = exl.Observation_visite_3mois，
[访问] .Date_visit_6mois_prevu = exl.Date_visit_6mois_prevu，
[访问] .Date_visit_6mois = exl.Date_visit_6mois，
[访问] .Observation_visite_6mois = exl.Observation_visite_6mois，
[访问] .Date_visit_1an_prevu = exl.Date_visit_1an_prevu，
[访问] .Date_visit_1an = exl.Date_visit_1an，
[访问] .Observation_visite_1an = exl.Observation_visite_1an，
FROM [访问]
INNER JOIN OPENROWSET（'Microsoft。 ACE.OLEDB.12.0'，'Excel 12.0;数据酸ce = C：\ Suivi \ export.xls;'，'SELECT Date_visit_1mois_prevu，Date_visit_1mois，Observation_visite_1mois，Date_visit_3mois_prevu，Date_visit_3mois，Observation_visite_3mois，Date_visit_6mois_prevu，Date_visit_6mois，Observation_visite_6mois，Date_visit_1an_prevu，Date_visit_1an，Observation_visite_1an FROM [sheet1
'）AS exlON [Visit] .Num_Doss = exl.Num_Doss
WHERE [Visit] .Num_Doss =+ logread.GetString（0）+;
SqlCommand up = new SqlCommand（update，cn）;
up.ExecuteNonQuery（）;
MessageBox.Show（miseàjouréffectuéavecusucée）;
}
}
else
{
string insrt = @insert into [Visit] Select * FROM OPENROWSET（'Microsoft.ACE.OLEDB.12.0'，' Excel 12.0;数据源= D：\ Suivi \ export1111.xlsx; HDR = YES'，'SELECT * FROM [Sheet1
'）;
SqlCommand ins = new SqlCommand（insrt，cn）;
ins.ExecuteNonQuery（）;
MessageBox.Show（插入éffectuéavecsuccés）;
}
}
但是当我运行我的代码时出现此错误：无法加载32位OLE DB提供程序 Microsoft.ACE.OLEDB.12.0 - 在64位SQL Server上进行处理。
此处SqlDataReader logread = verif.ExecuteReader（）;
help plz
salam
i want to import data from excel to sql server but there is a condition if the data already axist i will make an update else i insert the new data and here is my code
string srvxl = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=d:\Suivi\export1111.xlsx;Extended Properties="+"\"Excel 12.0 Xml;HDR=Yes;\"";
public void updateinsertsql(string path)
{
SqlConnection cn = new SqlConnection(srv);
OleDbConnection cnn=new OleDbConnection(srvxl);
cn.Open();
cnn.Open();
string cmdd = @"select v.Num_Doss from [Visit] v inner join OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;IMEX=1\;Data Source=d:\\Suivi\\export1111.xlsx;','SELECT Num_Doss FROM [sheet1$]')AS ExcelTable on d.Num_Doss=ExcelTable.Num_Doss";
SqlCommand verif = new SqlCommand(cmdd, cn);
SqlDataReader logread = verif.ExecuteReader();
if (logread.HasRows)
{
while (logread.Read())
{
//string update = @"update [Visit] set where v.Num_Doss="+logread.GetString(0)+"";
string update = @"UPDATE [Visit]
SET [Visit].Date_visit_1mois_prevu=exl.Date_visit_1mois_prevu,
[Visit].Date_visit_1mois=exl.Date_visit_1mois,
[Visit].Observation_visite_1mois=exl.Observation_visite_1mois,
[Visit].Date_visit_3mois_prevu=exl.Date_visit_3mois_prevu,
[Visit].Date_visit_3mois=exl.Date_visit_3mois,
[Visit].Observation_visite_3mois=exl.Observation_visite_3mois,
[Visit].Date_visit_6mois_prevu=exl.Date_visit_6mois_prevu,
[Visit].Date_visit_6mois=exl.Date_visit_6mois,
[Visit].Observation_visite_6mois=exl.Observation_visite_6mois,
[Visit].Date_visit_1an_prevu=exl.Date_visit_1an_prevu,
[Visit].Date_visit_1an=exl.Date_visit_1an,
[Visit].Observation_visite_1an=exl.Observation_visite_1an,
FROM [Visit]
INNER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Data Source=C:\Suivi\export.xls;','SELECT Date_visit_1mois_prevu,Date_visit_1mois,Observation_visite_1mois,Date_visit_3mois_prevu,Date_visit_3mois,Observation_visite_3mois,Date_visit_6mois_prevu,Date_visit_6mois,Observation_visite_6mois,Date_visit_1an_prevu,Date_visit_1an,Observation_visite_1an FROM [sheet1$]') AS exlON [Visit].Num_Doss = exl.Num_Doss
WHERE [Visit].Num_Doss=" + logread.GetString(0) + "";
SqlCommand up = new SqlCommand(update, cn);
up.ExecuteNonQuery();
MessageBox.Show("mise à jour éffectué avec succée");
}
}
else
{
string insrt = @"insert into [Visit] Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Data Source=D:\Suivi\export1111.xlsx;HDR=YES', 'SELECT * FROM [Sheet1$]')";
SqlCommand ins = new SqlCommand(insrt, cn);
ins.ExecuteNonQuery();
MessageBox.Show("insertion éffectué avec succés");
}
}
but when i run my code i get this error:
The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.
here
SqlDataReader logread = verif.ExecuteReader();
help plz
解决方案
')AS ExcelTable on d.Num_Doss=ExcelTable.Num_Doss"; SqlCommand verif = new SqlCommand(cmdd, cn); SqlDataReader logread = verif.ExecuteReader(); if (logread.HasRows) { while (logread.Read()) { //string update = @"update [Visit] set where v.Num_Doss="+logread.GetString(0)+""; string update = @"UPDATE [Visit] SET [Visit].Date_visit_1mois_prevu=exl.Date_visit_1mois_prevu, [Visit].Date_visit_1mois=exl.Date_visit_1mois, [Visit].Observation_visite_1mois=exl.Observation_visite_1mois, [Visit].Date_visit_3mois_prevu=exl.Date_visit_3mois_prevu, [Visit].Date_visit_3mois=exl.Date_visit_3mois, [Visit].Observation_visite_3mois=exl.Observation_visite_3mois, [Visit].Date_visit_6mois_prevu=exl.Date_visit_6mois_prevu, [Visit].Date_visit_6mois=exl.Date_visit_6mois, [Visit].Observation_visite_6mois=exl.Observation_visite_6mois, [Visit].Date_visit_1an_prevu=exl.Date_visit_1an_prevu, [Visit].Date_visit_1an=exl.Date_visit_1an, [Visit].Observation_visite_1an=exl.Observation_visite_1an, FROM [Visit] INNER JOIN OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Data Source=C:\Suivi\export.xls;','SELECT Date_visit_1mois_prevu,Date_visit_1mois,Observation_visite_1mois,Date_visit_3mois_prevu,Date_visit_3mois,Observation_visite_3mois,Date_visit_6mois_prevu,Date_visit_6mois,Observation_visite_6mois,Date_visit_1an_prevu,Date_visit_1an,Observation_visite_1an FROM [sheet1
') AS exlON [Visit].Num_Doss = exl.Num_Doss WHERE [Visit].Num_Doss=" + logread.GetString(0) + ""; SqlCommand up = new SqlCommand(update, cn); up.ExecuteNonQuery(); MessageBox.Show("mise à jour éffectué avec succée"); } } else { string insrt = @"insert into [Visit] Select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Data Source=D:\Suivi\export1111.xlsx;HDR=YES', 'SELECT * FROM [Sheet1
')"; SqlCommand ins = new SqlCommand(insrt, cn); ins.ExecuteNonQuery(); MessageBox.Show("insertion éffectué avec succés"); } }
but when i run my code i get this error:The 32-bit OLE DB provider "Microsoft.ACE.OLEDB.12.0" cannot be loaded in-process on a 64-bit SQL Server.
hereSqlDataReader logread = verif.ExecuteReader();
help plz
这篇关于将数据从excel导入到sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
