关于从EXCEL 导入到SQLSERVER 的问题。

Question

我现在要做了一个从 EXCEL导入SQLSERVER到的程序，
请问该如何实现？
如有一表结构如下：
CREATE TABLE CARDTYPE
(CARDNO VARCHAR(5) NOT IS NULL,
 WORKER_ID VARCHAR(3) ,
 DEPARTMENT_ID VARCHAR(2))
有EXCEL文档如下：
_____________________________________________________________
|cardno   |    worker_id  |        department_Id  |         |
|_________|_______________|_______________________|_________|
|00001    |    001        |        01             |         |
|_________|_______________|_______________________|_________|
|00002    |    002        |        01             |         |
|_________|_______________|_______________________|_________|
 如果用纯SQL脚本又要如何写呢？
 我用ODBC、DTS试了一下，好象也没试通，只好等各位老大帮忙了，谢谢了。

Answer 1

用SQL的OpenRowSet函数，保准没问题。CSDN上这类帖子太多了，用EXCEL作关键字搜索一下，保准结果奇多。

Answer 2

你可以看看
http://expert.csdn.net/Expert/topic/2579/2579346.xml?temp=.4775659

Answer 3

ar
 sSQL:string;
begin
 qry1.Close;
 qry1.SQL.Clear;
 sSQL :=
  'INSERT INTO cardTYPE  ( CARDNO,worker_id, DEPARTMENT_ID )'
  + ' SELECT CARDNO,worker_id, DEPARTMENT_ID '
  + ' FROM [excel 8.0;database=' + XlsName + '].[sheet1$]';
 qry1.SQL.Text := sSQL;
 qry1.Execsql;
end;

Answer 4

unit Unit1;interfaceuses
  Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
  StdCtrls, Db, ADODB,inifiles,comobj;type
  TForm1 = class(TForm)
    Button1: TButton;
    ADOQuery1: TADOQuery;
    OpenDialog1: TOpenDialog;
    procedure Button1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;var
  Form1: TForm1;implementation{$R *.DFM}procedure TForm1.Button1Click(Sender: TObject);
var ExcelApp:Variant;
    i,row_count,j:integer;
    Myini:TIniFile;
    str_file,str_excel:string;
begin
  str_file:=ExtractFilePath(ParamStr(0))+'ImportFromExcel.ini';
  Myini:=TIniFile.create(str_file);
  OpenDialog1.FileName:=Myini.ReadString('Excel文件','路径',str_excel);
  try
      if OpenDialog1.Execute then
      begin
        Myini.writestring('Excel文件','路径',OpenDialog1.FileName);
        try
            ExcelApp:=createoleobject('Excel.application');
        except
            application.Messagebox('无法打开Xls文件，请确认已经安装EXCEL！','提示',mb_OK+mb_IconStop);
            Exit;
        end;
        //ExcelApp.Visible:=true;
        ExcelApp.workbooks.open(opendialog1.FileName);
        row_count:=ExcelApp.worksheets[1].UsedRange.Rows.Count;       //*******************以下是导入信息******************
            for i:=2 to row_count do
            begin
               with adoquery1 do
               begin
                  if not ControlsDisabled then disablecontrols;
                  if not (state in [dsinsert]) then insert;
                  fieldbyname('card_no').value:=copy(ExcelApp.cells[i,1].value,1,5);
                  fieldbyname(' worker_id').value:=copy(ExcelApp.cells[i,2].value,1,3);
                  fieldbyname('department_Id ').value:=copy(ExcelApp.cells[i,3].value,1,2);
                  post;
               end;
            end;
     end;
  finally
        Myini.free;
  end;
end;end.