如Student.txt如下
Code Name Score
001 aa 99
002 bb 65
003 cc 87如何导入数据表Student.dbo
Code Varchar(20),
Name Vaechar(20),
Score Varchar(10)
Code Name Score
001 aa 99
002 bb 65
003 cc 87如何导入数据表Student.dbo
Code Varchar(20),
Name Vaechar(20),
Score Varchar(10)
以用户指定的格式复制一个数据文件至数据库表或视图中。语法
BULK INSERT [ [ 'database_name'.][ 'owner' ].]{ 'table_name' FROM 'data_file' }
[ WITH
(
[ BATCHSIZE [ = batch_size ] ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]
[ [ , ] DATAFILETYPE [ =
{ 'char' | 'native'| 'widechar' | 'widenative' } ] ]
[ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]
[ [ , ] FIRSTROW [ = first_row ] ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]
[ [ , ] LASTROW [ = last_row ] ]
[ [ , ] MAXERRORS [ = max_errors ] ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]
[ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]
[ , [ TABLOCK ] ]
)
] 在ADO控件里写SQL语句执行。比如:strSQL := 'BULK INSERT 数据库名.表名 FROM '数据文件.dat'
WITH (FORMATFILE = 格式文件')ADOQuery.SQL.Add(strSQL);
......
With (FORMATFILE = 格式文件')指的是什么,能详细一点吗?我的Text文件的格式如下:如Student.txt如下
Code Name Score
001 aa 99
002 bb 65
003 cc 87如何导入数据表Student.dbo
Code Varchar(20),
Name Vaechar(20),
Score Varchar(10)
unit Ufile;interfaceuses
Windows, Messages,Dialogs, SysUtils, Classes, Controls;type
Tfile = class
private
FFileName:String;
CurRow:integer;
CurCol:integer;
ListField:TStringList;
ListRecord:TStringList;
procedure SetFileName(FileName:string);
function GetFileName:string;
function GetCell(row,col:integer):string;
function GetRecordCount:integer;
function GetFieldCount(row:integer):integer;
public
{ Public declarations }
constructor Create;
destructor Destroy;
procedure Open();
procedure Clear;
property Cell[row:integer;col:integer]: String read getCell;
property FileName : string read GetFileName write SetFileName ;
property RecordCount :integer read GetRecordCount;
property FieldCount[row :integer] :integer read GetFieldCount;
end;
implementationConst BUFFER_COUNT = 1024000;constructor Tfile.Create;
begin
ListField := TStringList.Create;
ListRecord := TStringList.Create;
CurRow := -1;
CurCol := -1;
end;destructor Tfile.Destroy;
begin
ListField.Free;
ListRecord.Free;
end;procedure Tfile.Open;
var
objFile:TFileStream;
StrBuffer: array[1..BUFFER_COUNT] of char;
strch:char;
strLine:string;
iCount:integer;
begin
try
objfile := TFileStream.Create(FFileName,fmOpenRead);
except
ShowMessage('文件打开失败');
end;
Clear;
strLine := '';
// repeat
iCount := objfile.Read(strBuffer,BUFFER_COUNT-1);
// strBuffer[iCount] := '\0';
strLine := strLine+String(strBuffer);
// until
// iCount = BUFFER_COUNT-1;
ListRecord.Text := strLine; objFile.Free;
end;procedure Tfile.Clear;
begin
ListField.Clear;
ListRecord.Clear;
CurRow := -1;
CurCol := -1;
end;
procedure Tfile.SetFileName(FileName:string);
begin
FFileName:=FileName;
Open;
end;function Tfile.GetFileName:string;
begin
result:=FFileName;
end;function Tfile.GetRecordCount:integer;
begin
result := ListRecord.Count;
end;function Tfile.GetCell(row,col:integer):string;
var
value,strField:string;
ch:char;
i,Len:integer;
begin
if CurRow<>row then
begin
CurRow := row;
ListField.Clear;
if row < ListRecord.Count then
begin
value := trim(ListRecord.Strings[CurRow]);
Len := Length(value);
strField := '';
for i := 0 to len-1 do
begin
ch := value[i];
if ch=' 'then
begin
ListField.Add(strField);
strField :='';
end
else
strField := strField + ch
end;
ListField.Add(strField);
end;
end;
value := '';
if col < ListField.Count then
value := trim(ListField.Strings[col]);
result := value;
end;
function Tfile.GetFieldCount(row: integer): integer;
begin
GetCell(row,0);
result := ListField.Count;
end;end.