如何将现有的Excel表按照一定的要求批量追加到SQL Server表中

flyso 2006-04-24 04:36:05
现在有两个表,都有数据
一个Excel表
一个SQL Server表
两个表的字段都不一样，Excel表中的字段在第一行全是中文的
而SQL Server表全是英文的，字段位置也不一样
如何用SQL 语句来把Excel表中的数据批量追加的SQL Server表中，请高手指点！~
...全文
508 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
flyso 2006-05-18
  • 打赏
  • 举报
 回复
没人知道啊？
flyso 2006-04-27
  • 打赏
  • 举报
 回复
楼上的好像是Delphi的语法
我是说在SQL Server 2005中如何用SQL语句或其它方式实现！
没人知道吗？
djhai 2006-04-26
  • 打赏
  • 举报
 回复
不好意思～～好错了！！:<（以上做X)
djhai 2006-04-26
  • 打赏
  • 举报
 回复
Parameters.ParamByName('S5').Value:=ExcelApp.Cells[iPosRow,8];
Parameters.ParamByName('S6').Value:=ExcelApp.Cells[iPosRow,9];
Parameters.ParamByName('S7').Value:=ExcelApp.Cells[iPosRow,10];
Parameters.ParamByName('S8').Value:=ExcelApp.Cells[iPosRow,11];
ExecSQL;
end;
iPosRow:=iPosRow+1;
End;
ExcelApp.Quit;
With ADOQuery Do
Begin
SQL.Clear;
SQL.Add('Select Spid,BM,ZL,S1,S2,S3,S4,S5,S6,S7,S8 From #tmp');
Open;
With DBGridEh Do
Begin
Columns[0].FieldName:='Spid';
Columns[1].FieldName:='BM';
Columns[2].FieldName:='ZL';
Columns[3].FieldName:='S1';
Columns[4].FieldName:='S2';
Columns[5].FieldName:='S3';
Columns[6].FieldName:='S4';
Columns[7].FieldName:='S5';
Columns[8].FieldName:='S6';
Columns[9].FieldName:='S7';
Columns[10].FieldName:='S8';
end;
End;
With ADOQueryCM Do
Begin
SQL.Clear;
SQL.Add('Select VT,OP From _CM');
Open;
End;
With DBGridCM Do
Begin
Columns[0].FieldName:='VT';
Columns[1].FieldName:='OP';
End;
DBGridEh.AutoFitColWidths:=true;
DBGridCM.AutoFitColWidths:=true;
PC.ActivePageIndex:=PC.ActivePageIndex+1;

end;
except
On E:Exception do
Begin
Application.MessageBox(PAnsiChar(E.message),'数据导入导出',36);
End;
end;
End;
1:
Begin
With ADOQueryD Do
Begin
SQL.Clear;
SQL.Add('Insert Into #Tmp1 (Spid,CM,SL)'
+'Select Spid,1 as CM ,S1 From #TMP Where S1<>0');
SQL.Add('Insert Into #Tmp1 (Spid,CM,SL)'
+'Select Spid,2 as CM ,S2 From #TMP Where S2<>0');
SQL.Add('Insert Into #Tmp1 (Spid,CM,SL)'
+'Select Spid,3 as CM ,S3 From #TMP Where S3<>0');
SQL.Add('Insert Into #Tmp1 (Spid,CM,SL)'
+'Select Spid,4 as CM ,S4 From #TMP Where S4<>0');
SQL.Add('Insert Into #Tmp1 (Spid,CM,SL)'
+'Select Spid,5 as CM ,S5 From #TMP Where S5<>0');
SQL.Add('Insert Into #Tmp1 (Spid,CM,SL)'
+'Select Spid,6 as CM ,S6 From #TMP Where S6<>0');
SQL.Add('Insert Into #Tmp1 (Spid,CM,SL)'
+'Select Spid,7 as CM ,S7 From #TMP Where S7<>0');
SQL.Add('Insert Into #Tmp1 (Spid,CM,SL)'
+'Select Spid,8 as CM ,S8 From #TMP Where S8<>0');

ExecSQL;
SQL.Clear;
{DSDSDetailD}
SQL.Add('Select a.SPid as SPid,'
+' b.SPECid as YSdm,'
+' c.SPECid as CMdm ,'
+' a.SL as Total,'
+' 1 as InsertEdit '
+' From #TMP1 a '
+' Left Join '
+' (Select Spid,SPECid From ISPcsmgrp Where LBtype=0) b '
+' On a.Spid=b.Spid'
+' Left Join '
+' (Select SPid,SPECid,VT from ISPcsmgrp '
+' left join _cm on _cm.OP=ISPcsmgrp.SPECid Where LBtype=1) c '
+' On a.Cm=c.VT and a.Spid=c.Spid ');

Open;
End;
With ADOQueryM Do
Begin
SQL.Clear;
SQL.add('Select a.Spid as Spid,ZL as SLTotal,'+cmbPrice.Value+' as CKJmoney'
+',1 as ZZKdiscount,'+cmbPrice.Value+' as DJmoney'
+','+cmbPrice.Value+'*ZL as JEmoney'
+' From #Tmp a Left Join '
+' (Select Spid,CkJJ,CKjj1,CkJJ2 From ISPinfo) b '
+' on a.Spid=b.Spid');
OPen;
End;
//DSDSDETAILD SPid YSdm CMdm Total BYTotal InsertEdit
//ISPcsmgrp Spid,SPECid,LBtype
//#tmp1 HH,CM,SL
PC.ActivePageIndex:=Pc.ActivePageIndex+1;
DBGridEhR.AutoFitColWidths:=true;
DBGridEhM.AutoFitColWidths:=true;
DBGridEhD.AutoFitColWidths:=true;

End;
end;
end;

procedure TfrmIOData.txtServerPortKeyPress(Sender: TObject; var Key: Char);
begin
if not (key in ['0'..'9',#8,#13]) then
key:=#0;
Tab(Key);
end;

procedure TfrmIOData.txtServerNameKeyPress(Sender: TObject; var Key: Char);
begin
if not (key in ['0'..'9','a'..'z','A'..'Z','_','.',#8,#13]) then
key:=#0;
Tab(Key);
end;

procedure TfrmIOData.txtServerIDKeyPress(Sender: TObject; var Key: Char);
begin
if not (key in ['0'..'9','a'..'z','A'..'Z','_',#8,#13]) then
key:=#0;
Tab(Key);
end;

procedure TfrmIOData.txtServerPWDKeyPress(Sender: TObject; var Key: Char);
begin
Tab(Key);
end;

procedure TfrmIOData.FormShow(Sender: TObject);
begin
PC.ActivePageIndex:=0;
end;

procedure TfrmIOData.chkDJBHClick(Sender: TObject);
begin
if chkDJBH.Checked then
txtDJBH.Enabled:=false
else
txtDJBH.Enabled:=true;
end;

end.
djhai 2006-04-26
  • 打赏
  • 举报
 回复
上次做了个导入/导出excel的功能
//djhai@tom.com
//QQ:124112769 2005.12.12
unit pIOData;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, RzButton, ComCtrls, RzListVw, ExtCtrls, RzPanel, StdCtrls,
RzTabs, RzBtnEdt, Mask, RzEdit, RzCmboBx, DB, ADODB,pPublic, Grids,
DBGridEh,ComObj, RzRadChk;

type
TfrmIOData = class(TForm)
PC: TRzPageControl;
TabSheet: TRzTabSheet;
Image1: TImage;
lblSubTitle: TLabel;
Label1: TLabel;
TabSheet2: TRzTabSheet;
RzPanel1: TRzPanel;
Image2: TImage;
lblCaption1: TLabel;
lblCaption2: TLabel;
btnNext: TRzBitBtn;
btnCancel: TRzBitBtn;
GBServer: TRzGroupBox;
Label3: TLabel;
Label5: TLabel;
Label6: TLabel;
txtServerName: TRzButtonEdit;
txtServerPWD: TRzEdit;
txtServerID: TRzEdit;
txtServerPort: TRzEdit;
ADOQuery: TADOQuery;
ADOConn: TADOConnection;
OpenDialog: TOpenDialog;
DBGridEh: TDBGridEh;
DataSource: TDataSource;
DBGridCM: TDBGridEh;
ADOQueryCM: TADOQuery;
DataSourceCM: TDataSource;
TabSheet1: TRzTabSheet;
RzPanel2: TRzPanel;
Image3: TImage;
DBGridEhD: TDBGridEh;
DBGridEhM: TDBGridEh;
DBGridEhR: TDBGridEh;
Label7: TLabel;
txtDBName: TRzEdit;
Label8: TLabel;
DataSourceR: TDataSource;
ADOQueryR: TADOQuery;
ADOQueryM: TADOQuery;
DataSourceM: TDataSource;
DataSourceD: TDataSource;
ADOQueryD: TADOQuery;
Label4: TLabel;
chkDJBH: TRzCheckBox;
txtDJBH: TRzEdit;
cmbPrice: TRzComboBox;
procedure btnCancelClick(Sender: TObject);
procedure btnNextClick(Sender: TObject);
procedure txtServerPortKeyPress(Sender: TObject; var Key: Char);
procedure txtServerNameKeyPress(Sender: TObject; var Key: Char);
procedure txtServerIDKeyPress(Sender: TObject; var Key: Char);
procedure txtServerPWDKeyPress(Sender: TObject; var Key: Char);
procedure FormShow(Sender: TObject);
procedure chkDJBHClick(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;

var
frmIOData: TfrmIOData;

implementation

{$R *.dfm}

procedure TfrmIOData.btnCancelClick(Sender: TObject);
begin
close;
end;

procedure TfrmIOData.btnNextClick(Sender: TObject);
var
CONNECTSTRING:String;
Login_ServerName,Login_ServerPort,Login_ServerID,Login_ServerPWD:String;
Login_DBName:String;
mFileName:String;

ExcelApp:Variant;
iPosRow:integer;
begin
case PC.ActivePageIndex of
0:Begin
try
if NOT(ADOConn.Connected) then
Begin
Login_ServerName:=trim(txtServerName.Text);
Login_ServerPort:=trim(txtServerPort.Text);
Login_ServerID:=trim(txtServerID.Text);
Login_ServerPWD:=txtServerPWD.Text;
CONNECTSTRING:='Provider=SQLOLEDB.1;Persist Security Info=False;Data Source='
+Login_ServerName+','+Login_ServerPort;
ADOConn.Provider:='SQLOLEDB.1';
ADOConn.Connectionstring:=CONNECTSTRING;
ADOConn.Open(Login_ServerID,Login_ServerPWD);
End;
OpenDialog.Execute;
mFileName:=OpenDialog.FileName;
if mFileName<>'' then
begin
Login_DBName:=trim(txtDBName.Text);
ADOConn.Execute('Use '+Login_DBName);
ADOConn.Execute('IF EXISTS(SELECT Name FROM TempDb.Dbo.SysObjects '
+' WHERE Id = OBJECT_ID(''TempDb.Dbo.#Tmp''))'
+' DROP TABLE #Tmp'
+' Create Table #Tmp ('
+ 'Spid Varchar(30),'
+ 'BM Varchar(30),'
+ 'ZL integer,'
+ 'S1 integer,'
+ 'S2 integer,'
+ 'S3 integer,'
+ 'S4 integer,'
+ 'S5 integer,'
+ 'S6 integer,'
+ 'S7 integer,'
+ 'S8 integer)'
+'IF EXISTS(SELECT Name FROM TempDb.Dbo.SysObjects '
+' WHERE Id = OBJECT_ID(''TempDb.Dbo.#Tmp1''))'
+' DROP TABLE #Tmp1'
+' Create Table #Tmp1 ('
+ 'Spid Varchar(30),'
+ 'CM Varchar(6) ,'
+ 'SL integer)'
+'IF NOT EXISTS(SELECT Name FROM SysObjects '
+' WHERE NAME=''_CM'')'
+'Create Table _CM ('
+'VT integer,'
+'OP varchar(6))'
);
ExcelApp:=CreateOLEObject('Excel.Application');
ExcelApp.Workbooks.Open(mFileName);
iPosRow:=1;
While True Do
Begin
if trim(ExcelApp.Cells[iPosRow,1])='' then
Break;
With ADOQuery do
Begin
SQL.Clear;
SQL.Add('Insert Into #tmp ');
SQL.Add('(Spid,BM,ZL,S1,S2,S3,S4,S5,S6,S7,S8) ');
SQL.Add('Values (:SPid,:BM,:ZL,:S1,:S2,:S3,:S4,:S5,:S6,:S7,:S8)');
Parameters.ParamByName('Spid').Value:=ExcelApp.Cells[iPosRow,1];
Parameters.ParamByName('BM').Value:=ExcelApp.Cells[iPosRow,2];
Parameters.ParamByName('ZL').Value:=ExcelApp.Cells[iPosRow,3];
Parameters.ParamByName('S1').Value:=ExcelApp.Cells[iPosRow,4];
Parameters.ParamByName('S2').Value:=ExcelApp.Cells[iPosRow,5];
Parameters.ParamByName('S3').Value:=ExcelApp.Cells[iPosRow,6];
Parameters.ParamByName('S4').Value:=ExcelApp.Cells[iPosRow,7];
flyso 2006-04-26
  • 打赏
  • 举报
 回复
没人知道吗？
lanbo1021 2006-04-25
  • 打赏
  • 举报
 回复
我也遇到过类似问题
其实直接在EXCLE里面把字段名改好(改成和SQL SEVER里的一样)
再直接导入到数据库中就可以了
itblog 2006-04-25
  • 打赏
  • 举报
 回复
你可以先利用DTS导入导出向导把excel文件导出到SQL里,生成一个新表,然后再选择列导入到现有表中~也可以直接用函数导~
flyso 2006-04-25
  • 打赏
  • 举报
 回复
DTS 在 SQL Server2005好像没看到
还有我就是想知道怎么用语句实现这种特殊情况批量追加
如果改Excel字段，我也知道可以，不过实现不是科学的方法
MFC读取excel到SQLServer数据库
利用ODBC读取excel数据到Listcontrol，可以实现数据的增加、删除、修改等操作，还可以将excel的数据导入到SQLServer数据库
基于flask框架批量导入把excel表格到sqlserver，适合新手借鉴
基于flask框架批量导入把excel表格到sqlserver，包含pandas增删操作。查询和插入mssql数据库资料。适合新手借鉴，copy。
将Sql server中数据导出到excel.pdf
。。。
用EXCEL批量导入数据库.rar
用EXCEL批量导入数据库 , 经过测试的， 没有 问题！！
EXCEL数据输入到SQL数据库程序
将EXCEL电子数据输入到SQL数据库里的源代码程序，
应用实例

27,581

社区成员

68,548

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章