我这里只有一个Execl到VFP的例子,你自己改改就可用于SQL function XLS2DBF(SourceFile, SheetName, TargetFile, ConvertOption: PChar): Integer; //SourceFile 要导入的源Excel文件名和路径 //SheetName 要导入的工作表名 //TargetFile 要导出的Foxpro DBF文件名和路径 //ConvertOption 转换选项: // 'Data' 1stLineAsData 第一行作为数据行(缺省值) // 'Field' 1stLineAsField 第一行作为字段名var RecordSet, ComConnection: Variant; FieldNames, FieldDefines, FieldValues: String; i, FieldCount, FieldSize: Integer; begin Result := 0; //创建到导入Excel文件的数据集 RecordSet := CreateOleObject('ADODB.RecordSet'); try RecordSet.Open('Select * From [' + SheetName + '$]', 'Driver={Microsoft Excel Driver (*.xls)};DBQ=' + SourceFile); except Result := -1; //无法打开导入Execl源文件 Exit; end; //得到字段列表和字段定义 FieldNames := ''; FieldDefines := ''; FieldCount := 0; if ConvertOption = 'Data' then for i := 0 to RecordSet.Fields.Count-1 do begin if (RecordSet.Fields[i].ActualSize = 0) and (RecordSet.Fields[i].Name = 'F' + IntToStr(i+1)) then Continue; Inc(FieldCount); FieldSize := RecordSet.Fields[i].ActualSize; if Length(Trim(RecordSet.Fields[i].Name))>FieldSize then FieldSize := Length(Trim(RecordSet.Fields[i].Name)); if FieldNames='' then begin FieldNames := 'Field' + Format('%.3d', [FieldCount]); FieldDefines := FieldNames + ' C(' + IntToStr(FieldSize) + ')'; end else begin FieldNames := FieldNames + ',Field' + Format('%.3d', [FieldCount]); FieldDefines := FieldDefines + ',Field' + Format('%.3d', [FieldCount]) + ' C(' + IntToStr(FieldSize) + ')'; end; end else for i := 0 to RecordSet.Fields.Count-1 do begin if (RecordSet.Fields[i].ActualSize = 0) and (RecordSet.Fields[i].Name = 'F' + IntToStr(i+1)) then Continue; FieldSize := RecordSet.Fields[i].ActualSize; if FieldSize = 0 then FieldSize := 1; if FieldNames='' then begin FieldNames := Trim(RecordSet.Fields[i].Name); FieldDefines := FieldNames + ' C(' + IntToStr(FieldSize) + ')'; end else begin FieldNames := FieldNames + ',' + Trim(RecordSet.Fields[i].Name); FieldDefines := FieldDefines + ',' + Trim(RecordSet.Fields[i].Name) + ' C(' + IntToStr(FieldSize) + ')'; end; end; //到VFP的连接 ComConnection := CreateOleObject('ADODB.Connection'); try ComConnection.Open('Driver={Microsoft FoxPro VFP Driver (*.dbf)};' + 'SourceType=DBF;SourceDB=' + ExtractFileDir(TargetFile)); except Result := -2; //无法连接到VFP Exit; end; //创建VFP DBF表 try ComConnection.Execute('Create Table ' + TargetFile + '(' + FieldDefines + ')'); except Result := -3; //无法创建临时表 Exit; end; //往临时表导入数据 if ConvertOption = 'Data' then begin FieldValues := ''; for i := 0 to RecordSet.Fields.Count-1 do begin if (RecordSet.Fields[i].ActualSize = 0) and (RecordSet.Fields[i].Name = 'F' + IntToStr(i+1)) then Continue; if FieldValues = '' then FieldValues := '''' + RecordSet.Fields[i].Name + '''' else FieldValues := FieldValues + ',''' + RecordSet.Fields[i].Name + ''''; end; try ComConnection.Execute('Insert Into ' + TargetFile + '(' + FieldNames + ') Values(' + FieldValues + ')'); except Result := -4; Exit; end; end;
while not RecordSet.Eof do begin FieldValues := ''; for i := 0 to RecordSet.Fields.Count-1 do begin if (RecordSet.Fields[i].ActualSize = 0) and (RecordSet.Fields[i].Name = 'F' + IntToStr(i+1)) then Continue; if FieldValues = '' then FieldValues := '''' + VarToStr(RecordSet.Fields[i].Value) + '''' else FieldValues := FieldValues + ',''' + VarToStr(RecordSet.Fields[i].Value) + ''''; end; try ComConnection.Execute('Insert Into ' + TargetFile + '(' + FieldNames + ') Values(' + FieldValues + ')'); except Result := -4; Exit; end; RecordSet.MoveNext; end; RecordSet.Close; ComConnection.Close; end;
在dbf里建立好相应的字段 然后利用adoquery with adoquery1 do begin close; sql.clear; sql.text:='insert to *.dbf select * from (sql表)'; open; end;
每处理一行sql server 2000记录
就插入一行到DBF
这样就OK了
function XLS2DBF(SourceFile, SheetName, TargetFile, ConvertOption: PChar): Integer;
//SourceFile 要导入的源Excel文件名和路径
//SheetName 要导入的工作表名
//TargetFile 要导出的Foxpro DBF文件名和路径
//ConvertOption 转换选项:
// 'Data' 1stLineAsData 第一行作为数据行(缺省值)
// 'Field' 1stLineAsField 第一行作为字段名var
RecordSet, ComConnection: Variant;
FieldNames, FieldDefines, FieldValues: String;
i, FieldCount, FieldSize: Integer;
begin
Result := 0;
//创建到导入Excel文件的数据集
RecordSet := CreateOleObject('ADODB.RecordSet');
try
RecordSet.Open('Select * From [' + SheetName + '$]',
'Driver={Microsoft Excel Driver (*.xls)};DBQ=' + SourceFile);
except
Result := -1; //无法打开导入Execl源文件
Exit;
end; //得到字段列表和字段定义
FieldNames := '';
FieldDefines := '';
FieldCount := 0;
if ConvertOption = 'Data' then
for i := 0 to RecordSet.Fields.Count-1 do begin
if (RecordSet.Fields[i].ActualSize = 0) and (RecordSet.Fields[i].Name = 'F' + IntToStr(i+1)) then
Continue;
Inc(FieldCount);
FieldSize := RecordSet.Fields[i].ActualSize;
if Length(Trim(RecordSet.Fields[i].Name))>FieldSize then
FieldSize := Length(Trim(RecordSet.Fields[i].Name));
if FieldNames='' then begin
FieldNames := 'Field' + Format('%.3d', [FieldCount]);
FieldDefines := FieldNames + ' C(' + IntToStr(FieldSize) + ')';
end
else begin
FieldNames := FieldNames + ',Field' + Format('%.3d', [FieldCount]);
FieldDefines := FieldDefines + ',Field' + Format('%.3d', [FieldCount])
+ ' C(' + IntToStr(FieldSize) + ')';
end;
end
else
for i := 0 to RecordSet.Fields.Count-1 do begin
if (RecordSet.Fields[i].ActualSize = 0) and (RecordSet.Fields[i].Name = 'F' + IntToStr(i+1)) then
Continue;
FieldSize := RecordSet.Fields[i].ActualSize;
if FieldSize = 0 then
FieldSize := 1;
if FieldNames='' then begin
FieldNames := Trim(RecordSet.Fields[i].Name);
FieldDefines := FieldNames + ' C(' + IntToStr(FieldSize) + ')';
end
else begin
FieldNames := FieldNames + ',' + Trim(RecordSet.Fields[i].Name);
FieldDefines := FieldDefines + ',' + Trim(RecordSet.Fields[i].Name)
+ ' C(' + IntToStr(FieldSize) + ')';
end;
end; //到VFP的连接
ComConnection := CreateOleObject('ADODB.Connection');
try
ComConnection.Open('Driver={Microsoft FoxPro VFP Driver (*.dbf)};'
+ 'SourceType=DBF;SourceDB=' + ExtractFileDir(TargetFile));
except
Result := -2; //无法连接到VFP
Exit;
end; //创建VFP DBF表
try
ComConnection.Execute('Create Table ' + TargetFile + '(' + FieldDefines + ')');
except
Result := -3; //无法创建临时表
Exit;
end; //往临时表导入数据
if ConvertOption = 'Data' then begin
FieldValues := '';
for i := 0 to RecordSet.Fields.Count-1 do begin
if (RecordSet.Fields[i].ActualSize = 0) and (RecordSet.Fields[i].Name = 'F' + IntToStr(i+1)) then
Continue;
if FieldValues = '' then
FieldValues := '''' + RecordSet.Fields[i].Name + ''''
else
FieldValues := FieldValues + ',''' + RecordSet.Fields[i].Name + '''';
end;
try
ComConnection.Execute('Insert Into ' + TargetFile
+ '(' + FieldNames + ') Values(' + FieldValues + ')');
except
Result := -4;
Exit;
end;
end;
while not RecordSet.Eof do begin
FieldValues := '';
for i := 0 to RecordSet.Fields.Count-1 do begin
if (RecordSet.Fields[i].ActualSize = 0) and (RecordSet.Fields[i].Name = 'F' + IntToStr(i+1)) then
Continue;
if FieldValues = '' then
FieldValues := '''' + VarToStr(RecordSet.Fields[i].Value) + ''''
else
FieldValues := FieldValues + ',''' + VarToStr(RecordSet.Fields[i].Value) + '''';
end;
try
ComConnection.Execute('Insert Into ' + TargetFile
+ '(' + FieldNames + ') Values(' + FieldValues + ')');
except
Result := -4;
Exit;
end;
RecordSet.MoveNext;
end;
RecordSet.Close;
ComConnection.Close;
end;
然后利用adoquery
with adoquery1 do
begin
close;
sql.clear;
sql.text:='insert to *.dbf select * from (sql表)';
open;
end;
直接操作
每处理一行sql server 2000记录
就插入一行到DBF
这样就OK了