SQL Server与Excel、Access数据互导

最近用到SQL Server与Excel数据互导，网上有很多人也问到这个问题，但很多解答不够清楚，有些太过简略，于是结合自己实践的体会，和网上的总结，重新整理一下，加入了自己遇到问题的解决，算是方便自己以后查阅，也希望能对遇到类似问题的朋友有所帮助。

1、SQL Server导出为Excel：
        要用T-SQL语句直接导出至Excel工作薄，就不得不用借用SQL Server管理器的一个扩展存储过程：xp_cmdshell，此过程的作用为“以操作系统命令行解释器的方式执行给定的命令字符串，并以文本行方式返回任何输出。”下面为定义示例：
        2、Excel导入SQL Server表：
        在SQL Server中，有定义一个OpenDateSource函数，用于引用那些不经常访问的 OLE DB 数据源，而我们的数据互导操作，就是建立在这个函数之上。
        
        首先看一个T-SQL帮助中的示例，描述如下：

--下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。

SELECT * 
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:/Finance/account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')xactions

        如果你直接引用这个示例进行查询，那么肯定是通不过的。关键在于语句中的两个地方需要修改，一处在于Data Source处，双引号内为Excel表格的实际存放位置，要修改为你想查询的Excel表实际完整路径；二为最后的...xactions，其实这里代表的是要进行的某些动作，下面会讲，这里修改成用中括号包围的Excel表中工作表名字（加上一个$）就可以了，如[Sheet1$]。当然，还可以将Excel 5.0改为Excel 8.0，因为5.0是以前的老版本了。

        下面是实例说明：
    

/**//*1、插入Excel中的资料到现存的sql数据库表中（假设C盘有excel表book2.xls，book2.xls中有个工作表sheet1，sheet1中有两列id和FName；而同时sql数据库中也有一个表test）：*/
insert into test SELECT id,FName
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:/book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')[sheet1$]
--如果用select * ，则列的次序会乱，资料内容也会乱，无法插入成功，所以指定列名
－－－－－－－－－－－－－－－－－－－－－－－
/**//*2、插入excel表中资料到sql数据库并新建一个sql表（excel的定义和内容同上）：*/
select convert(int,id)as id,FName into test7
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:/book2.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')[sheet1$]
--在select 列中最好用convert进行显示类型转换，否则资料类型会不如预期。

特别注意！！！：1）如果是从数据库中导出的exel表，例如从jobs表导出的exel文件mytest.xls工作表默认是jobs上面例子中的[sheet1$] 应改为[jobs$]

2）如果出现“服务器:   消息   7399，级别   16，状态   1，行   1  
  OLE   DB   提供程序   'MICROSOFT.JET.OLEDB.4.0'   报错。提供程序未给出有关错误的任何信息。”  
   
  上面这个错误是因为你的EXECL   文件被打开着,关掉那个EXCEL文件再试试.

3）被导入的exel表第一行要有各列的列名如

id name age

1 tomclus 35

。。。

如果没有列名仅仅

1 tomclus 35

。。。

可能会出错

如果上面的例子中没有制定所有列，或select*,都会出错，如列不完全，或数据类型布匹

SQL Server与Excel的数据互导讲解完了，你明白了吗？而Access和Excel的基本一样，只是要去掉Extended properties声明。 

=======================
Delphi示例（導出為excel表）：

ADOQ1.Close;
       ADOQ1.SQL.Clear;
       sqltrs :=
         'INSERT INTO CTable (Name1,Sex,ID)'+
         ' SELECT'+
         ' 姓名,性别,身份证号'+
         ' FROM [excel 8.0;database=' + XlsName + '].[sheet1$]';
       ADOQ1.Parameters.Clear;
       ADOQ1.ParamCheck:=false;
       ADOQ1.SQL.Text := sqltrs;
       ADOQ1.Execsql;

 //中文字段两边不能有空格

另附：（下面的部分内容没有亲自实践）

熟悉SQL   SERVER   2000的数据库管理员都知道，其DTS可以进行数据的导入导出，其实，我们也可以使用Transact-SQL语句进行导入导出操作。在Transact-SQL语句中，我们主要使用OpenDataSource函数、OPENROWSET   函数，关于函数的详细说明，请参考SQL联机帮助。利用下述方法，可以十分容易地实现SQL   SERVER、ACCESS、EXCEL数据转换，详细说明如下：  
   
  一、SQL   SERVER   和ACCESS的数据导入导出  
  常规的数据导入导出：  
  使用DTS向导迁移你的Access数据到SQL   Server，你可以使用这些步骤:    
  　　○1在SQL   SERVER企业管理器中的Tools（工具）菜单上，选择Data   Transformation    
  　　○2Services（数据转换服务），然后选择     czdImport   Data（导入数据）。    
  　　○3在Choose   a   Data   Source（选择数据源）对话框中选择Microsoft   Access   as   the   Source，然后键入你的.mdb数据库(.mdb文件扩展名)的文件名或通过浏览寻找该文件。    
  　　○4在Choose   a   Destination（选择目标）对话框中，选择Microsoft   OLE　DB   Prov   ider   for   SQL　Server，选择数据库服务器，然后单击必要的验证方式。    
  　　○5在Specify   Table   Copy（指定表格复制）或Query（查询）对话框中，单击Copy   tables（复制表格）。    
          ○6在Select   Source   Tables（选择源表格）对话框中，单击Select   All（全部选定）。下一步，完成。  
   
  Transact-SQL语句进行导入导出：  
  1.在SQL   SERVER里查询access数据:  
   
  SELECT   *    
  FROM   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0',  
  'Data   Source="c:/DB.mdb";User   ID=Admin;Password=')...表名  
   
  2.将access导入SQL   server  
  在SQL   SERVER   里运行:  
  SELECT   *  
  INTO   newtable  
  FROM   OPENDATASOURCE   ('Microsoft.Jet.OLEDB.4.0',    
              'Data   Source="c:/DB.mdb";User   ID=Admin;Password='   )...表名  
   
  3.将SQL   SERVER表里的数据插入到Access表中  
  在SQL   SERVER   里运行：  
  insert   into   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0',  
      'Data   Source="   c:/DB.mdb";User   ID=Admin;Password=')...表名    
  (列名1,列名2)  
  select   列名1,列名2     from     sql表  
   
  实例：  
  insert   into     OPENROWSET('Microsoft.Jet.OLEDB.4.0',    
        'C:/db.mdb';'admin';'',   Test)    
  select   id,name   from   Test  
   
   
  INSERT   INTO   OPENROWSET('Microsoft.Jet.OLEDB.4.0',   'c:/trade.mdb';   'admin';   '',   表名)  
  SELECT   *  
  FROM   sqltablename  
   
  二、SQL   SERVER   和EXCEL的数据导入导出  
   
  1、在SQL   SERVER里查询Excel数据:  
   
  SELECT   *    
  FROM   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0',  
  'Data   Source="c:/book1.xls";User   ID=Admin;Password=;Extended   properties=Excel   5.0')...[Sheet1$]  
   
  下面是个查询的示例，它通过用于   Jet   的   OLE   DB   提供程序查询   Excel   电子表格。  
  SELECT   *    
  FROM   OpenDataSource   (   'Microsoft.Jet.OLEDB.4.0',  
      'Data   Source="c:/Finance/account.xls";User   ID=Admin;Password=;Extended   properties=Excel   5.0')...xactions  
   
  2、将Excel的数据导入SQL   server   :  
  SELECT   *   into   newtable  
  FROM   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0',  
      'Data   Source="c:/book1.xls";User   ID=Admin;Password=;Extended   properties=Excel   5.0')...[Sheet1$]  
   
  实例:  
  SELECT   *   into   newtable  
  FROM   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0',  
      'Data   Source="c:/Finance/account.xls";User   ID=Admin;Password=;Extended   properties=Excel   5.0')...xactions  
   
  3、将SQL   SERVER中查询到的数据导成一个Excel文件  
  T-SQL代码：  
  EXEC   master..xp_cmdshell   'bcp   库名.dbo.表名out   c:/Temp.xls   -c   -q   -S"servername"   -U"sa"   -P""'  
  参数：S   是SQL服务器名；U是用户；P是密码  
  说明：还可以导出文本文件等多种格式  
   
  实例:EXEC   master..xp_cmdshell   'bcp   saletesttmp.dbo.CusAccount   out   c:/temp1.xls   -c   -q   -S"pmserver"   -U"sa"   -P"sa"'  
   
    EXEC   master..xp_cmdshell   'bcp   "SELECT   au_fname,   au_lname   FROM   pubs..authors   ORDER   BY   au_lname"   queryout   C:/   authors.xls   -c   -Sservername   -Usa   -Ppassword'  
   
  在VB6中应用ADO导出EXCEL文件代码：    
  Dim   cn     As   New   ADODB.Connection  
  cn.open   "Driver={SQL   Server};Server=WEBSVR;DataBase=WebMis;UID=sa;WD=123;"  
  cn.execute   "master..xp_cmdshell   'bcp   "SELECT   col1,   col2   FROM   库名.dbo.表名"   queryout   E:/DT.xls   -c   -Sservername   -Usa   -Ppassword'"  
   
   
  4、在SQL   SERVER里往Excel插入数据:  
   
  insert   into   OpenDataSource(   'Microsoft.Jet.OLEDB.4.0',  
  'Data   Source="c:/Temp.xls";User   ID=Admin;Password=;Extended   properties=Excel   5.0')...table1   (A1,A2,A3)   values   (1,2,3)  
   
  T-SQL代码：  
  INSERT   INTO      
    OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',      
    'Extended   Properties=Excel   8.0;Data   source=C:/training/inventur.xls')...[Filiale1$]      
    (bestand,   produkt)   VALUES   (20,   'Test')      
   
  总结：利用以上语句，我们可以方便地将SQL   SERVER、ACCESS和EXCEL电子表格软件中的数据进行转换，为我们提供了极大方便！  
   
  参考：  
  http://www.itrain.de/knowhow/sql/transfer/adhoc/  

   cnblogs(bonny.wong)

欢迎补充新的内容，共同进步：-）

 

EXEC master..xp_cmdshell 'bcp 库名.dbo.表名out c:/Book3.xls -c -q -S"servername" -U"sa" -P""'

--参数：S 是SQL服务器名；U是用户名；P是密码,没有就空着

--说明：其实用这个过程导出的格式实质上就是文本格式的，不信的话在导出的Excel表中改动一下再保存看看。

    实际例子与说明如下：

/**//*如果要将表整个导出至Excel的话*/
EXEC master..xp_cmdshell 'bcp northwind.dbo.orders out c:/Book1.xls -c -q -S"(local)" -U"sa" -P""'

--注意句中的northwind.dbo.orders，为数据库名＋拥有者＋表名

--直接导出用“out”关健字

-------------------------------------------
/**//*如果要利用查询来导出部分字段至Excel的话*/
EXEC master..xp_cmdshell 'bcp "SELECT orderid,cutomerid,freight FROM northwind..orders ORDER BY orderid" queryout C:/ Book2.xls -c -S"(local)" -U"sa" -P""'

--这里在bcp后面加了一个查询语句，并用双引号括起来

--利用查询要用“queryout”关键字