要求从EXCEL中商品列表的每条记录(拼命,数量,价格)导入到SQL SERVER 中,其中在SQL SERVER 中表tbl_stock已经存在了。
网上好多类似下面的根本不行:
Select * Into [Table1] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=C:\xxx.xls','Select * from [工作表1$]')
我需要的是一条条导入,Excel 和Sqlserver 表的字段不一定全部匹配。
而且Excel 中的数据格式不一定和Sqlserver中表的字段的格式一样。
有哪位大虾可帮忙啊!谢过了先!
网上好多类似下面的根本不行:
Select * Into [Table1] FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;DATABASE=C:\xxx.xls','Select * from [工作表1$]')
我需要的是一条条导入,Excel 和Sqlserver 表的字段不一定全部匹配。
而且Excel 中的数据格式不一定和Sqlserver中表的字段的格式一样。
有哪位大虾可帮忙啊!谢过了先!
改为INSERT INTO [Table1] ( f1,f2,f3,f4)
SELECT b1 as f1,b2 as f2 ,b3 as f3,b4 as f4
FROM [excel 8.0;database=c:\xxx.xls].[xxx$] AS t2;
--------------------first;
while not eof do
begin
...
next;
end;
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, Excel2000, OleServer, StdCtrls, Grids, DBGrids, Mask, ExtCtrls,
DB, ADODB;type
TForm1 = class(TForm)
ExcelApplication1: TExcelApplication;
ExcelWorkbook1: TExcelWorkbook;
ExcelWorksheet1: TExcelWorksheet;
Button1: TButton;
OpenDialog1: TOpenDialog;
Label3: TLabel;
Edit3: TEdit;
Button2: TButton;
ADOConnection1: TADOConnection;
ADOQuery1: TADOQuery;
Edit4: TEdit;
RadioButton1: TRadioButton;
RadioButton2: TRadioButton;
Button3: TButton;
Label1: TLabel;
Panel1: TPanel;
StringGrid1: TStringGrid;
ListBox1: TListBox;
Label2: TLabel;
Edit1: TEdit;
procedure Button1Click(Sender: TObject);
procedure FormCreate(Sender: TObject);
procedure Button2Click(Sender: TObject);
procedure Button3Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;
fields: array of string;
c: integer;implementation{$R *.dfm}uses ADOConEd;procedure TForm1.Button1Click(Sender: TObject);
var
i,j: integer;
Str : String;
begin
opendialog1.InitialDir:=ExtractFileDir(paramstr(0));//文件的打存放初始路径
opendialog1.Execute;
Try
ExcelApplication1.Connect;//EXCEL应用程序
Except
MessageDlg('Excel may not be installed',mtError, [mbOk], 0);
Abort;
End;
ExcelApplication1.Visible[0]:=True;
ExcelApplication1.Caption:='Excel Application';
try
excelapplication1.Workbooks.Open(opendialog1.FileName,
null,null,null,null,null,null,null,null,null,null,null,null,0);//打开指定的EXCEL 文件
except
begin
ExcelApplication1.Disconnect;//出现异常情况时关闭
ExcelApplication1.Quit;
showmessage('请选择EXCEL电子表格!');
exit;
end;
end;
//ExcelApp.WorksSheets[ 'Sheet2' ].Activate;
ExcelWorkbook1.ConnectTo(ExcelApplication1.Workbooks[1]);//ExcelWorkbook1与Eexcelapplication1建立连接
ExcelWorksheet1.ConnectTo(ExcelWorkbook1.Worksheets[1] as _Worksheet);//Excelworksheet1与Excelworkbook1建立连接
//取字段名:EXCEL的第一行
for j:=1 to 256 do
if trim(excelworksheet1.cells.item[1,j])<>'' then
begin
StringGrid1.ColCount := j;
c := j;
//Stringgrid1.rowCount := 0;
//StringGrid1.Cells[j-1,1] := ExcelWorksheet1.Cells.Item[1,j];
SetLength(fields,j);
fields[j-1] := ExcelWorksheet1.Cells.Item[1,j];
end;
//开始从EXCEL中取数,放到stringgrid1中,取完数后关闭EXCEL
for i:=1 to 65536 do//最大取值
for j:=0 to c-1 do
if trim(excelworksheet1.cells.item[i,1])<>'' then
begin
stringgrid1.rowCount := i+1;
Str := ExcelWorksheet1.Cells.Item[i,j+1];
stringgrid1.Cells[j,i] := Str;
if Str = '' then
ListBox1.Items.Add('行:' + IntToStr(i) + ' 列:' + IntToStr(j));
end
else
begin
label3.caption := '共有记录:' + IntToStr(i-2) + '条';
ExcelApplication1.Disconnect;
ExcelApplication1.Quit;
exit;
end;
end;procedure TForm1.FormCreate(Sender: TObject);
begin
Stringgrid1.RowHeights[0] := 2;
{ StringGrid1.FixedColor := clNavy ;
StringGrid1.FixedCols := 1; //列
StringGrid1.FixedRows := 2 //行
}
end;
procedure TForm1.Button2Click(Sender: TObject);
var
i,j,k: integer; //循环变量
maxline: integer; //最大行数
ErrStr : String;
TmpInsertStr: String;
Str1,Str2 : String;
begin
Try
maxline := StrToInt(copy(Label3.Caption,pos(':',Label3.Caption)+2,Length(Label3.Caption)-Length('共有记录:条')));
except
ShowMessage(#13'取行数出错或异常错误!'#13);
exit;
end;
if (not RadioButton1.Checked) and (not RadioButton2.Checked ) then
begin
ShowMessage('请选择导入方式!');
exit;
end
else if Edit3.Text = '' then
begin
ShowMessage('请输入表名!');
Edit3.SetFocus;
end
else
begin ADOConnection1.Close;
if Edit4.Text = '' then
begin
//PromptDataSource( GetActiveWindow, '');
EditConnectionString(ADOConnection1);
Edit4.Text := ADOConnection1.ConnectionString;
end
else
ADOConnection1.ConnectionString := Edit4.Text; ADOConnection1.Open;
ADOConnection1.BeginTrans; if RadioButton1.Checked then //创建新表
begin
ADOQuery1.Active := False;
//表存在则删除
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('if exists(select * from dbo.sysobjects where id = object_id(N''' + '[dbo].[' + Edit3.Text + ']''' + ') and OBJECTPROPERTY(id, N''' + 'IsUserTable''' + ') = 1 ) ');
ADOQuery1.SQL.Add('drop table [dbo].[' + Edit3.Text + ']');
ADOQuery1.ExecSQL; //创建表
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('CREATE TABLE [dbo].[' + Edit3.Text + '] (');
for i:=0 to Length(fields)-1 do
begin
ADOQuery1.SQL.Add('[' + fields[i] + '] [varchar] (255) NOT NULL ');
if i <> Length(fields) then
ADOQuery1.SQL.Add(' ,');
end; ADOQuery1.SQL.Add(') ON [PRIMARY]');
ADOQuery1.ExecSQL; //往表内写数据
for k:=2 to maxline+1 do //行 stringgrid1.Cells[j,i]
begin
ADOQuery1.Active := False;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Insert Into ' + Edit3.Text + '(');
for i:=0 to length(fields) - 1 do
begin
ADOQuery1.SQL.Add(fields[i]);
if i <> length(fields) - 1 then
ADOQuery1.SQL.Add(',');
end;
ADOQuery1.SQL.Add(') Values('); stringgrid1.rowCount := k + 1;
for j:=1 to c do //列
begin
TmpInsertStr := StringGrid1.Cells[j-1,k];
if Pos('''''',TmpInsertStr)>0 then
begin
Str1 := Copy(TmpInsertStr,1,Pos('''''',TmpInsertStr));
Str2 := Copy(TmpInsertStr,Pos('''''',TmpInsertStr)+1,Length(TmpInsertStr)-Pos('''',TmpInsertStr));
TmpInsertStr := Str1 + '''''' + Str2;
end
else if Pos('''',TmpInsertStr)>0 then
begin
Str1 := Copy(TmpInsertStr,1,Pos('''',TmpInsertStr));
Str2 := Copy(TmpInsertStr,Pos('''',TmpInsertStr)+1,Length(TmpInsertStr)-Pos('''',TmpInsertStr));
TmpInsertStr := Str1 + '''' + Str2;
end;
ADOQuery1.SQL.Add('''' + TmpInsertStr + '''');
if j <> c then
ADOQuery1.SQL.Add(',');
end;
ADOQuery1.SQL.Add(')');
ADOQuery1.Prepared;
try
ADOQuery1.ExecSQL;
except
ADOConnection1.RollbackTrans;
for i:= 0 to ADOConnection1.Errors.count-1 do
ErrStr := IntToStr(i) + ':' + Adoconnection1.Errors.item[i].description + #13 + ErrStr;
//showMessage(ADOConnection1.Errors.Item[0].Description);
ShowMessage(#13'数据导入失败!!'#13#13'失败原因:'#13#13+ ErrStr);
Exit;
end;
end;
end
else if RadioButton2.Checked then //导入已存在的表
begin
ADOQuery1.Active := False;
//找到现有表的字段类型
ADOQuery1.SQL.Clear;
//往表内写数据
for k:=2 to maxline+1 do //行 stringgrid1.Cells[j,i]
begin
ADOQuery1.Active := False;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Insert Into ' + Edit3.Text + '(');
for i:=0 to length(fields) - 1 do
begin
ADOQuery1.SQL.Add(fields[i]);
if i <> length(fields) - 1 then
ADOQuery1.SQL.Add(',');
end;
ADOQuery1.SQL.Add(') Values('); stringgrid1.rowCount := k + 1;
for j:=1 to c do //列
begin
TmpInsertStr := StringGrid1.Cells[j-1,k];
if Pos('''''',TmpInsertStr)>0 then
begin
Str1 := Copy(TmpInsertStr,1,Pos('''''',TmpInsertStr));
Str2 := Copy(TmpInsertStr,Pos('''''',TmpInsertStr)+1,Length(TmpInsertStr)-Pos('''',TmpInsertStr));
TmpInsertStr := Str1 + '''''' + Str2;
end
else if Pos('''',TmpInsertStr)>0 then
begin
Str1 := Copy(TmpInsertStr,1,Pos('''',TmpInsertStr));
Str2 := Copy(TmpInsertStr,Pos('''',TmpInsertStr)+1,Length(TmpInsertStr)-Pos('''',TmpInsertStr));
TmpInsertStr := Str1 + '''' + Str2;
end;
ADOQuery1.SQL.Add('''' + TmpInsertStr + '''');
if j <> c then
ADOQuery1.SQL.Add(',');
end;
ADOQuery1.SQL.Add(')');
ADOQuery1.Prepared;
//Memo1.Text := ADOQuery1.SQL.Text;
try
ADOQuery1.ExecSQL;
except
ADOConnection1.RollbackTrans;
Edit1.Text := IntToStr(k);
for i:= 0 to ADOConnection1.Errors.count-1 do
ErrStr := IntToStr(i) + ':' + Adoconnection1.Errors.item[i].description + #13 + ErrStr;
//showMessage(ADOConnection1.Errors.Item[0].Description);
ShowMessage(#13'数据导入失败!!'#13#13'失败原因:'#13#13+ ErrStr);
Exit;
end;
end;
end; if ADOConnection1.Errors.Count = 0 then
begin
ADOConnection1.CommitTrans;
ShowMessage(#13'数据已经成功导入数据库!'#13);
end
else
begin ADOConnection1.RollbackTrans;
for i:= 0 to ADOConnection1.Errors.count-1 do
ErrStr := IntToStr(i) + ':' + Adoconnection1.Errors.item[i].description + #13 + ErrStr;
//showMessage(ADOConnection1.Errors.Item[0].Description);
ShowMessage(#13'数据导入失败!!'#13#13'失败原因:'#13#13+ ErrStr);
end;
end;end;procedure TForm1.Button3Click(Sender: TObject);
begin
Edit4.Text := '';
end;end.
DECLARE ufanyifmdok SCROLL CURSOR
FOR
Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel ---- 在这里转换呀8.0;DATABASE=C:\xxx.xls','Select * from [工作表1$]') OPEN ufanyifmdok
FETCH NEXT FROM ufanyifmdok INTO @bill_no,@SEQU,@b_acc_code,@bg_deptcode,@pay_sum
while(@@fetch_status=0)
begin
insert into ufanyifmdmx( bill_no,SEQU,b_acc_code,bg_deptcode,pay_sum )
VALUES ( @bill_no,@SEQU,@b_acc_code,@bg_deptcode,@pay_sum)
FETCH NEXT FROM ufanyifmdok INTO @bill_no,@SEQU,@b_acc_code,@bg_deptcode,@pay_sumend
CLOSE ufanyifmdok
DEALLOCATE ufanyifmdok主要是要转换类型 cast (列名 as 数据类型 ),用游标
一个ADOConnection控件,专门用来连接Excel,一个OpenDialog1,用来选择你所需要的Excel表,两个ADOQuery,一个DBGrid,一个DataSource, ADOQuery1连ADOConnection,DataSource连ADOQuery,DBGrid连DataSource(这些你都知道怎么用吧^_^),ADOQuery2连接SQL Server
下面是关键代码:
//"选择"按钮代码:
var
dbname:string;beginif OpenDialog1.Execute then
edit1.Text:=OpenDialog1.FileName;//这是所要操作的EXCEL文件名if trim(edit1.Text)<>'' then
begin
//取得无扩展名文件名,打开Excel时用
dbname := LeftStr(ExtractFileName(trim(edit1.Text)),pos('.',ExtractFileName(trim(edit1.Text)))-1);ADOConnection1.Connected:=false;
ADOConnection1.ConnectionString:='Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+trim(edit1.Text)+';Extended Properties=EXCEL 8.0;Persist Security Info=False';
ADOConnection1.Connected:=true;with ADOQuery1 do
begin
Close;
SQL.Clear;
SQL.Add('select * from ['+dbname+'$]');
Open;
end;
end;通过上在,你已于你所需要的Execl表连接起来,既已将你所需要的Execl表置于DBGrid;
接下来,你只需要将DBGrid里的数据一条条导入SQL Servervar
a_filedNo,a_recno,j :integer;
sqlstr,sqlstr1:string;begina_filedNo :=ADOQuery1.FieldCount;//每一行的列数try
a_recno := 1;ADOQuery1.First;
while not ADOQuery1.Eof do
begin
for j :=1 to a_filedNo do //转化一个记录,你可以在此进行修改或什么的
begin
sqlstr1:=sqlstr1+''''+ADOQuery1.Fields[j-1].AsString+''''+',';
end;
sqlstr:='insert into 你要导入的表名 values('+sqlstr1+')'; with ADOQuery2 do
begin
close;
SQL.Clear;
SQL.Add(sqlstr);
ExecSQL;
end; ADOQuery1.Next; ;
a_recno := a_recno + 1;
sqlstr1:='';
sqlstr:='';
end;
application.MessageBox('数据导入成功!','系统提示',MB_Ok+MB_IconInformation+MB_ApplModal);
except
application.MessageBox('打开Excel文件失败!','系统提示',mb_ok or mb_iconstop);
end;
上面是经过测试的,绝对可行
先用ODBC建立一个数据源,数据源指向sql server的表,然后用TTABLE控件联上数据库,
再建立一个OleObject,然后用Append,Post把数据导入到EXCEL表。
实例:
var
MSExcel:Variant;//定义一个全程变量 MSExcel := CreateOleObject('Excel.Application');
MSExcel.WorkBooks.Open(Edit1.Text);//创建一个OleObject
Table.Active:=true;
Table.Append;
Table.FieldByName('字段名').Value:=MSExcel.Cells[rows_i,1].Value;
Table.Post;//实现导入如果有不明白给我mail: [email protected]
测试连接时提示:“测试连接失败,因为初始化提供程序时发生错误。找不到可安装的ISAM”
在在sql中进行更新另一个表
~~~~~~~~~这个地方应该是:excel 8.0,即在excel和8.0中间有一个空格,切记!