我用程序把Excel文件中的数据导入到SqlServer的一个表中,但是发生数据丢失的现象!
就是在Excel里的有些单元格到了SqlServer里就变NULL,开始以为是程序问题,后来用SqlServer自带的工具导入,也发生同样的问题!大家帮忙找找原因(很急的!)!
Excel文件地址:http://www.hongchanggarlic.com/Book1.rar
就是在Excel里的有些单元格到了SqlServer里就变NULL,开始以为是程序问题,后来用SqlServer自带的工具导入,也发生同样的问题!大家帮忙找找原因(很急的!)!
Excel文件地址:http://www.hongchanggarlic.com/Book1.rar
解决方案 »
- 如何个cxGrid中如何增加一列,这列是一个button,我要用这个button的onclick事件写一下东东
- 请问大家,怎么让delphi编出的程序界面花哨一点?谢谢!
- 有那位做过视频方面的滤光处理功能呀?
- !!!!求购!!!!(100-300元RMB)
- Spread 6.0控件
- 求一条sql语句,高手帮帮忙!!!!!!!!!!
- 用visual chm做的帮助文件,编译后图象显示不出来
- 关于API函数
- 关于交换两个整数(不借助第三个临时数)... 及 Delphi、VC、Asm 效率对比
- 在Delphi下做的控件能拿到Kylix下用吗?需不需要修改?有没有谁用过?
- 怎样才能把一个列表中有的数据,而表的没有的数据选到一个结果集里?
- 请问~~哪里有胜天进销存源码分析 谢谢
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
unit main;interfaceuses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, ExtCtrls, ComCtrls, ToolWin, Grids, DBGrids, DB, ADODB;type
TForm1 = class(TForm)
ADOConnection1: TADOConnection;
DataSource1: TDataSource;
ADOQuery2: TADOQuery;
StatusBar1: TStatusBar;
ToolBar1: TToolBar;
ToolButton1: TToolButton;
ToolButton2: TToolButton;
ToolButton3: TToolButton;
ToolButton4: TToolButton;
Panel1: TPanel;
Edit1: TEdit;
Label1: TLabel;
Edit2: TEdit;
Label2: TLabel;
OpenDialog1: TOpenDialog;
Edit3: TEdit;
Label3: TLabel;
ToolButton5: TToolButton;
ADOStoredProc1: TADOStoredProc;
ADOStoredProc2: TADOStoredProc;
PageControl1: TPageControl;
TabSheet1: TTabSheet;
TabSheet2: TTabSheet;
DBGrid1: TDBGrid;
DBGrid2: TDBGrid;
DataSource2: TDataSource;
procedure ToolButton1Click(Sender: TObject);
procedure ToolButton2Click(Sender: TObject);
procedure ToolButton3Click(Sender: TObject);
procedure ToolButton4Click(Sender: TObject);
procedure ToolButton5Click(Sender: TObject);
procedure FormShow(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;var
Form1: TForm1;implementation{$R *.dfm}procedure TForm1.ToolButton1Click(Sender: TObject);
begin
ADOConnection1.Connected:=False;
ADOConnection1.ConnectionString:=PromptDataSource(Handle,ADOConnection1.ConnectionString);
ADOConnection1.Open;
end;procedure TForm1.ToolButton2Click(Sender: TObject);
begin
if OpenDialog1.Execute then
Edit3.Text:=OpenDialog1.FileName;
end;procedure TForm1.ToolButton3Click(Sender: TObject);
var
AdoQuery:TADOQuery;
begin
AdoQuery:=TADOQuery.Create(nil);
try
{删除存在的表}
AdoQuery.Connection:=ADOConnection1;
AdoQuery.SQL.Text:=Format('SELECT name FROM sysobjects WHERE name = ''%s'' AND type = ''U''',[Edit1.Text]);
AdoQuery.Open;
if AdoQuery.RecordCount>0 then
begin
AdoQuery.Close;
AdoQuery.SQL.Text:=('Drop Table ['+Edit1.Text+']');
AdoQuery.ExecSQL;
end;
{创建存储过程}
AdoQuery.SQL.Clear;
AdoQuery.SQL.Add('if exists (select * from sysobjects where id = object_id(N''[usp_TransExcel]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)');
AdoQuery.SQL.Add('drop procedure [dbo].[usp_TransExcel]');
AdoQuery.ExecSQL;
AdoQuery.SQL.Clear;
AdoQuery.SQL.Add('Create PROCEDURE usp_TransExcel(');
AdoQuery.SQL.Add(' @tablename VARCHAR(50),');
AdoQuery.SQL.Add(' @ExcelName VARCHAR(50),');
AdoQuery.SQL.Add(' @SheetName VarChar(50)) AS ');
AdoQuery.SQL.Add('declare @s nvarchar(4000)');
AdoQuery.SQL.Add('set @s=''');
AdoQuery.SQL.Add('select * into [''+@tablename+''] from opendataSource(''''Microsoft.Jet.OLEDB.4.0'''',''''Data Source="''+@ExcelName+''";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'''')...[''+@SheetName+''$]''');
AdoQuery.SQL.Add('exec sp_executesql @s');
AdoQuery.SQL.Add(' ,N''@tablename varchar(50),');
AdoQuery.SQL.Add(' @ExcelName varchar(50),');
AdoQuery.SQL.Add(' @SheetName varchar(50)''');
AdoQuery.SQL.Add(' ,@tablename,@ExcelName,@SheetName');
AdoQuery.ExecSQL;
finally
AdoQuery.Free;
end;{执行转换}
ADOStoredProc1.Parameters[0].Value:=Edit1.Text;
ADOStoredProc1.Parameters[1].Value:=Edit3.Text;
ADOStoredProc1.Parameters[2].Value:=Edit2.Text;
ADOStoredProc1.ExecProc;
ToolButton4.Click;
end;procedure TForm1.ToolButton4Click(Sender: TObject);
begin
ADOQuery2.Close;
ADOQuery2.SQL.Text:=Format('Select * from [%s]',[Edit1.text]);
adoquery2.Open;
end;procedure TForm1.ToolButton5Click(Sender: TObject);
var
AdoQuery:TADOQuery;
begin
AdoQuery:=TADOQuery.Create(nil);
try
AdoQuery.Connection:=ADOConnection1;
{创建存储过程}
AdoQuery.SQL.Clear;
AdoQuery.SQL.Add('if exists (select * from sysobjects where id = object_id(N''[usp_TransExcelLook]'') and OBJECTPROPERTY(id, N''IsProcedure'') = 1)');
AdoQuery.SQL.Add('drop procedure [dbo].[usp_TransExcelLook]');
AdoQuery.ExecSQL;
AdoQuery.SQL.Clear;
AdoQuery.SQL.Add('Create PROCEDURE usp_TransExcelLook(');
AdoQuery.SQL.Add(' @ExcelName VARCHAR(50),');
AdoQuery.SQL.Add(' @SheetName VarChar(50)) AS ');
AdoQuery.SQL.Add('declare @s nvarchar(4000)');
AdoQuery.SQL.Add('set @s=''');
AdoQuery.SQL.Add('select * from opendataSource(''''Microsoft.Jet.OLEDB.4.0'''',''''Data Source="''+@ExcelName+''";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False'''')...[''+@SheetName+''$]''');
AdoQuery.SQL.Add('exec sp_executesql @s');
AdoQuery.SQL.Add(' ,N''');
AdoQuery.SQL.Add(' @ExcelName varchar(50),');
AdoQuery.SQL.Add(' @SheetName varchar(50)''');
AdoQuery.SQL.Add(' ,@ExcelName,@SheetName');
AdoQuery.ExecSQL;
finally
AdoQuery.Free;
end;
ADOStoredProc2.Close;
ADOStoredProc2.Parameters[0].Value:=Edit3.Text;
ADOStoredProc2.Parameters[1].Value:=Edit2.Text;
ADOStoredProc2.Open;
end;procedure TForm1.FormShow(Sender: TObject);
begin
Edit3.Text:=ExtractFilePath(Application.ExeName)+'abc.xls';
end;end.