将EXCEL 导入到SQL
报这样的错:说外部表不是预期的格式,我写的EXCEL和数据库是一一对应的procedure TFrmExInput.Excel1Click(Sender: TObject);
var
sTableName, ErrInfo: string;
begin
try
ADOConn.Connected := False;
ADOConn.ConnectionString := Format(context, [Trim(edtPasswd.Text), Trim(edtUser.Text), Trim(edtServerName.Text), Trim(edtServerIP.Text)]);
ADOConn.Connected := True;
except
Application.MessageBox('数据库连接出错! ', '提示信息', MB_OK or MB_ICONERROR);
Abort;
end;
if Trim(edtTableName.Text) = '' then
begin
Application.MessageBox('选择数据表的栏位不能为空! ', '提示信息', MB_OK or MB_ICONERROR);
Abort;
end; OpenDialog1.DefaultExt := 'xls';
OpenDialog1.Filter := 'Excel File|*.xls';
OpenDialog1.InitialDir := ExtractFilePath(Application.ExeName);
if OpenDialog1.Execute then
begin
ADOCommand1.ConnectionString := Format(SExcelText, [OpenDialog1.FileName]);
try
sTableName := Trim(edtTableName.Text); ExcuteSQL(adoQuery, 'delete from ' + sTableName);
ADOCommand1.CommandText := 'insert into ' + sTableName + ' IN [ODBC]'
+ ' [ODBC; Driver=SQL Server; UID=' + ADOConn.Properties['User ID'].Value + '; PWD=' + ADOConn.Properties['Password'].Value
+ '; Server=' + ADOConn.Properties['Data Source'].Value + '; DataBase='
+ ADOConn.Properties['Initial Catalog'].Value + ';] select * from [' + sTableName + '$]';
showmessage(ADOCommand1.CommandText);
// 'SELECT * into SQLEXCEL FROM ' +
// 'OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="C:\temp\Temp.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'')...sheet1$'
ADOCommand1.Execute;
ADOCommand1.ConnectionString := '';
except
ErrInfo := '导入数据时出错!'; //导入数据时出错!
Application.MessageBox('导入数据时出错!', '提示信息', MB_OK or MB_ICONERROR);
Exit;
end;
if ErrInfo = '' then
begin
Application.MessageBox('导入数据已完成!', '提示信息', MB_OK or MB_ICONINFORMATION); //导入数据已完成!
end;
end;
end;
报这样的错:说外部表不是预期的格式,我写的EXCEL和数据库是一一对应的procedure TFrmExInput.Excel1Click(Sender: TObject);
var
sTableName, ErrInfo: string;
begin
try
ADOConn.Connected := False;
ADOConn.ConnectionString := Format(context, [Trim(edtPasswd.Text), Trim(edtUser.Text), Trim(edtServerName.Text), Trim(edtServerIP.Text)]);
ADOConn.Connected := True;
except
Application.MessageBox('数据库连接出错! ', '提示信息', MB_OK or MB_ICONERROR);
Abort;
end;
if Trim(edtTableName.Text) = '' then
begin
Application.MessageBox('选择数据表的栏位不能为空! ', '提示信息', MB_OK or MB_ICONERROR);
Abort;
end; OpenDialog1.DefaultExt := 'xls';
OpenDialog1.Filter := 'Excel File|*.xls';
OpenDialog1.InitialDir := ExtractFilePath(Application.ExeName);
if OpenDialog1.Execute then
begin
ADOCommand1.ConnectionString := Format(SExcelText, [OpenDialog1.FileName]);
try
sTableName := Trim(edtTableName.Text); ExcuteSQL(adoQuery, 'delete from ' + sTableName);
ADOCommand1.CommandText := 'insert into ' + sTableName + ' IN [ODBC]'
+ ' [ODBC; Driver=SQL Server; UID=' + ADOConn.Properties['User ID'].Value + '; PWD=' + ADOConn.Properties['Password'].Value
+ '; Server=' + ADOConn.Properties['Data Source'].Value + '; DataBase='
+ ADOConn.Properties['Initial Catalog'].Value + ';] select * from [' + sTableName + '$]';
showmessage(ADOCommand1.CommandText);
// 'SELECT * into SQLEXCEL FROM ' +
// 'OpenDataSource(''Microsoft.Jet.OLEDB.4.0'',''Data Source="C:\temp\Temp.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'')...sheet1$'
ADOCommand1.Execute;
ADOCommand1.ConnectionString := '';
except
ErrInfo := '导入数据时出错!'; //导入数据时出错!
Application.MessageBox('导入数据时出错!', '提示信息', MB_OK or MB_ICONERROR);
Exit;
end;
if ErrInfo = '' then
begin
Application.MessageBox('导入数据已完成!', '提示信息', MB_OK or MB_ICONINFORMATION); //导入数据已完成!
end;
end;
end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货