如何把excel文件导入到sql server数据表中,谢谢

Question

如何把excel文件导入到sql server数据表中,谢谢

Answer 1

Select * into <table name> from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\temp\b.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False')...sheet1$

Answer 2

var
    sfilename:string;
    function BlobContentTostring(const Filename:string):string;
    begin
      with Tfilestream.Create(filename,fmopenread)  do
      try
        setlength(result,size);
        read(pointer(result)^,size);
      finally
        free;
      end;
    end;
    begin
      if opendialog1.Execute then
      begin
        sfilename:=opendialog1.FileName;
        DataModule1.ADOQuery14.Edit;
        DataModule1.ADOQuery14.FieldByName('excel').AsString:=blobcontenttostring(sfilename);
        DataModule1.ADOQuery14.Post;
      end;
    end;

Answer 3

楼上用的是sqlserver的功能，你除了用opendatasource也可以用dts和bcp
看看sqlserver的帮助吧

Answer 4

//open excel
procedure TForm1.ConnectClick(Sender: TObject);
var
  str,path,xlsName:string;
begin
//connect excel
path:=extractfilepath(application.exename);
OpenDialog1.InitialDir :=path;
OpenDialog1.Filter :='*.xls|*.xls';
if OpenDialog1.Execute then
  xlsName :=extractfilename(OpenDialog1.FileName);str:='Provider=Microsoft.Jet.OLEDB.4.0;' +
     'Data Source= ' + path + xlsName + ';Extended Properties=Excel 8.0;' +
     'Persist Security Info=False';
conn.Close;
conn.ConnectionString :=str;
try
  conn.Connected :=true;
  Adotable1.Close;
  Adotable1.TableDirect:=True ;
  adotable1.tablename:='sheet1$';
  tType.ItemIndex :=-1;
  try
    adotable1.Open;
    dbgrid1.Columns[0].Width :=50;
    dbgrid1.Columns[1].Width :=50;
    dbgrid1.Columns[2].Width :=50;
    dbgrid1.Columns[3].Width :=80;
    dbgrid1.Columns[4].Width :=80;
    dbgrid1.Columns[5].Width :=200;
  except
    showmessage('Open Error');
  end;
except
  self.Caption :='Excel connect error';
  exit;
end;end;//导入,我使用批处理,adoquery1.locktype->ltBatchOptimistic
ADOQuery1.Close;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select * from tablename where 0=1');
ADOQuery1.Open;ADOTable1.First;
while not ADOTable1.Eof do
  begin
    ADOQuery1.Append;
    ADOQuery1.FieldByName('xx').AsString :=trim(Adotable1.fieldbyname('yy').AsString );
    //和上句相同,相应字段数据导入即可
    ADOQuery1.Post;
    ADOTable1.Next;
  end;
ADOQuery1.UpdateBatch();
showmessage('over');----------------------------
下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
INSERT INTO urtable
SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')
---------------------------
ADOConnection 指向excel, 用Jet4.0 ,Extended properties设为Excel 8.0
SELECT * into table  FROM Tab1 IN [ODBC]
[ODBC;Driver=SQL Server;UID=sa;PWD=;Server=127.0.0.1;DataBase=Demo;]
--------------------------------------------------
SELECT * 
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="C:\temp\b.xls";Extended Properties="Excel 5.0;HDR=Yes;";Persist Security Info=False')

Answer 5

这是我针对某个特殊的EXCEL文件对某个特殊的SQL数据库表写的导入程序，希望对你会有帮助
当然希望能更好的方法procedure TForm1.BitBtn1Click(Sender: TObject);
var
  i,j,k,tmpnum:integer;
  tmpstr:string;
  excelfile,Sheet,pvttable:Variant;
begin
    if not fileexists(edit1.Text) then
    begin
       showmessage('File not find!');
       exit;
    end;
    try
      excelfile:= CreateOleObject('Excel.Application');
      excelfile.Visible := false;
      excelfile.Workbooks.Open(edit1.text);
      Sheet := excelfile.Workbooks[1].WorkSheets[1];
      j:=1;
      //**************追加数据**********************//      while trim(sheet.cells.item[1,j])<>''  do
                j:=j+1;
      i:=2;
      PBAR1.Max:=j;
      with query1 do begin
        Close;
        sql.clear;
        sql.text:='insert into order11(or1,or8,material,or12,or22,or9,or21,ora,or31,or32) values(:or1,:or8,:material,:or12,:or22,:or9,:or21,''L'',getdate(),''F'')';
      while trim(sheet.cells.item[i,1])<>''  do   //行数   or tmpnum<3
        begin
          tmpnum:=0;
        for k:=1 to j-3 do                    //列数
      //还需加判断条件，去掉一些不必要的数据
          if (k=5) or (k=7) or (k=2) then
             continue
          else
          begin
             tmpstr:=trim(sheet.cells.item[i,k]);
             //sql.params[tmpnum].asstring:=sheet.cells.item[i,k];  //SQL语句得到参数
             if (k=8) or (k=9) then
               begin
                query1.Params[tmpnum].AsInteger :=strtoint(tmpstr);
                if k=8 then
                begin
                  tmpnum:=tmpnum+1;
                  tmpstr:=trim(sheet.cells.item[i,2])+'/'+trim(sheet.cells.item[i,6]);
                  query1.Params[tmpnum].AsString :=tmpstr;
                end;
                end
              else
                if k=1 then
                   if (i-1)<10 then
                   begin
                      or1arr[i-1] :=tmpstr+'/0'+inttostr(i-1);
                      query1.Params[tmpnum].AsString :=or1arr[i-1];
                   end
                   else
                   begin
                      or1arr[i-1]:=tmpstr+'/'+inttostr(i-1);
                      query1.Params[tmpnum].AsString :=or1arr[i-1];
                   end
                else
                   query1.Params[tmpnum].AsString :=tmpstr;
             tmpnum:=tmpnum+1;
           pbar1.stepit;
          end;
       execsql;
       pbar1.Max:=i;
       pbar1.StepIt ;
      i:=i+1;
      end;
    end;         //with  //  sql.text:='insert into order11(or1,or6,or8,material,or12,or22,or9,ora,or31,or32) values(:or1,:or6,:or8,:material,:or12,:or22,:or9,''L'',getdate(),''F'')';    except
      Showmessage('初始化Excel失败，可能没装Excel，或者其他错误；请重起再试。');
      excelfile.DisplayAlerts := false;
      excelfile.Quit;
      exit;
    end;
    Application.Restore;
    Application.BringToFront;
    pbar2.Max :=i-1;
    for i:=1 to pbar2.Max-1  do
    begin
        query2.Close;
        query2.sql.text:='insert into order2(or12,lh) values(:or12,:lh)';
        for j:=35 to 41 do
        begin
          query2.ParamByName('or12').AsString :=or1arr[i];
          query2.ParamByName('lh').AsString :=inttostr(j);
          query2.ExecSQL ;
        end;
      pbar2.StepIt ;
     
    end;    //********退出EXCEL应该程序*******************//
     excelfile.WorkBooks[1].Close(True, edit1.text); //取文件名退出
     excelfile.quit;
    showmessage('Import database success!');
end;

Answer 6

delphi6里可以直接用dbgridr打开excel用件的，然后换化就不用说了吧

Answer 7

思路：
1.将Excel的数据提取出来，方法ado、其他；
http://chinasmsd.com/myweb/jxguang/download/index.htm
有个控件，不知是否适合你的要求；
2.写入Sql Server中，如果数据已取得，相信ADO连接EXCEL的方法，我可以给你个历程，需要发消息。

Answer 8

procedure TLoad_Frm.Button1Click(Sender: TObject);
var 
   MSExcel: Variant;
   i: Integer;
   st_BH:integer;
   Max_Bh:string;  // 商品编号
   Temp_Bh:string; // 商品类别编号   SP_LB:string;   // 商品类别
   SP_MC:string;  // 商品名
   SP_GX:string;   // 商品规格型号
   SP_SM:string;   // 商品说明
   SP_JG:string;   // 商品价格
   SP_BZ:string;   // 商品备注
begin
  st_BH:=0;  //进程条
  Gauge1.Visible := True;  OpenDialog1.Filter:='*.XLS|*.XLS';
  OpenDialog1.DefaultExt:='XLS';
  if OpenDialog1.Execute then
  begin
    MSExcel:=CreateOLEObject('Excel.Application');
    MSExcel.WorkBooks.Open(OpenDialog1.FileName);
    Edit1.Text:= OpenDialog1.FileName;    MSExcel.Visible:=False;
    //进程条
    Gauge1.MaxValue:=MSExcel.ActiveSheet.UsedRange.Rows.Count;    //从有数据的行逐行读入数据
    try
      for i:=1 to MSExcel.ActiveSheet.UsedRange.Rows.Count do
      begin
        SP_MC:=MSExcel.Cells[i,1].Value;  // 商标名
        SP_GX:=MSExcel.Cells[i,2].Value;  // 商品规格型号
        SP_SM:=MSExcel.Cells[i,3].Value;  // 商品说明
        SP_JG:=MSExcel.Cells[i,4].Value;  // 商品价格
        SP_BZ:=MSExcel.Cells[i,5].Value;  // 商品备注        if (SP_MC='商标名') and (SP_GX='规格型号') and (SP_SM='商品说明') and (SP_JG='价格')and (SP_BZ='备注') then
        begin
          Gauge1.AddProgress(1);
          Continue;
        end;        if SP_MC='' then
        begin
          Gauge1.AddProgress(1);
          Continue;
        end;        if (SP_MC<>'') and (SP_GX='') then
        begin
          SP_LB:=SP_MC;  // 商品类别
           //商品编号
          ADO_Goods.Close;
          ADO_Goods.SQL.Clear;
          ADO_Goods.SQL.Text:='Select max(SP_SPBH) as SP_SPBH,max(SP_SPLBBH) as SP_SPLBBH from ASSERTS.PRODUCTINFO Where SP_SPSSLB='''+SP_LB+''' ';
          ADO_Goods.Open;
          if  ADO_Goods.FieldByName('SP_SPBH').AsString<>'' then
          begin
            Max_Bh:= IntToStr(StrToInt(ADO_Goods.FieldByName('SP_SPBH').AsString)+1);
            Temp_Bh:=ADO_Goods.FieldByName('SP_SPLBBH').AsString;
          end else
          begin
            ADO_Goods.Close;
            ADO_Goods.SQL.Clear;
            ADO_Goods.SQL.Text:='Select FL_FLBH from ASSERTS.PRODUCTCLASS Where FL_FLMC='''+SP_LB+''' ';
            ADO_Goods.Open;
            Max_Bh:= inttostr(strtoint(ADO_Goods.FieldByName('FL_FLBH').AsString+'001')+st_BH);
            Temp_Bh:=ADO_Goods.FieldByName('FL_FLBH').AsString;
          end;
          Gauge1.AddProgress(1);
          Continue;
        end;        if (SP_MC<>'') and (SP_GX<>'') then
        begin
          Max_Bh:=inttostr(strtoint(Max_Bh)+st_BH);
          st_BH:=st_BH+1;
          ADO_Goods.Close;
          ADO_Goods.SQL.Clear;
          ADO_Goods.SQL.Text:='Select SP_SPBH,SP_SPLBBH,SP_SPSSLB,SP_SPBP,SP_SPXH,SP_SPMC,SP_SPSM,SP_JGLY,SP_SPJG,SP_CJSJ,SP_TPDZ ';
          ADO_Goods.SQL.Text:=ADO_Goods.SQL.Text+' from ASSERTS.PRODUCTINFO Where SP_SPSSLB='''+SP_LB+''' and SP_SPBP='''+SP_MC+''' and SP_SPXH='''+SP_GX+''' and SP_JGLY=''参考价'' ';
          ADO_Goods.Open;
          if not ADO_Goods.IsEmpty then
          begin
             //编辑
            ADO_Goods.Edit;
            ADO_Goods.FieldByName('SP_SPSSLB').AsString:=SP_LB;
            ADO_Goods.FieldByName('SP_SPBP').AsString:=SP_MC;
            ADO_Goods.FieldByName('SP_SPXH').AsString:=SP_GX;
            ADO_Goods.FieldByName('SP_SPMC').AsString:=SP_MC+SP_LB;
            ADO_Goods.FieldByName('SP_SPSM').AsString:=SP_SM;
            ADO_Goods.FieldByName('SP_JGLY').AsString:='参考价';
            ADO_Goods.FieldByName('SP_SPJG').AsString:=SP_JG;
            ADO_Goods.FieldByName('SP_CJSJ').AsString:=DateToStr(now);
            ADO_Goods.FieldByName('SP_TPDZ').AsString:=SP_BZ;
            ADO_Goods.Post;
          end else
          begin
             //插入
            ADO_Goods.Insert;
            ADO_Goods.FieldByName('SP_SPBH').AsString:=Max_Bh;
            ADO_Goods.FieldByName('SP_SPLBBH').AsString:=Temp_Bh;
            ADO_Goods.FieldByName('SP_SPSSLB').AsString:=SP_LB;
            ADO_Goods.FieldByName('SP_SPBP').AsString:=SP_MC;
            ADO_Goods.FieldByName('SP_SPXH').AsString:=SP_GX;
            ADO_Goods.FieldByName('SP_SPMC').AsString:=SP_MC+SP_LB;
            ADO_Goods.FieldByName('SP_SPSM').AsString:=SP_SM;
            ADO_Goods.FieldByName('SP_JGLY').AsString:='参考价';
            ADO_Goods.FieldByName('SP_SPJG').AsString:=SP_JG;
            ADO_Goods.FieldByName('SP_CJSJ').AsString:=DateToStr(now);
            ADO_Goods.FieldByName('SP_TPDZ').AsString:=SP_BZ;
            ADO_Goods.Post;
          end;
          Gauge1.AddProgress(1);
        end;
      end;
      Gauge1.Hide;
      MSExcel.ActiveWorkBook.Close;
      MSExcel.Quit;
    Except
      Gauge1.Hide;
      Exit;
    end;
  end;
end;