SQL、EXCEL、ACCESS之间的导入导出！

深碍√TFBOYSˉ_ 2022-05-19 11:48 185阅读 0赞

如何用SQL语句查询Excel数据？
    
    Q：如何用SQL语句查询Excel数据？
    A：下列语句可在SQL SERVER中查询Excel工作表中的数据。
    
    2007和2010版本:
    
     SELECT *
     FROM OpenDataSource( 'Microsoft.ACE.OLEDB.12.0',
     'Data Source="c:\book1.xlsx";User ID=Admin;Password=;Extended properties=Excel 12.0')...[Sheet1$]
    
    
    
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\book1.xlsx', 'SELECT * FROM [Sheet1$A1:D100]')
    
    
    
    SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;Database=C:\book1.xlsx', 'SELECT * FROM [Sheet1$]')
    2003版本：
    
    
    SELECT *
    FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
    'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[Sheet1$]
    
    
    
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\book1.xls', 'SELECT * FROM [Sheet1$]')
    
    
    
    SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;Database=C:\book1.xls', 'SELECT * FROM [Sheet1$A1:D100]')
    
    
    
    而且，还需要用拥有服务器sysadmin角色权限的登录帐号，先执行下列语句打开SQL SERVER的'Ad Hoc Distributed Queries'开关。
    
    exec sp_configure 'show advanced options',1;reconfigure;
    exec sp_configure 'Ad Hoc Distributed Queries',1;reconfigure;
    
    
    
    执行查询后，再用相反顺序的语句，关闭'Ad Hoc Distributed Queries'开关。
    
     
    
    exec sp_configure 'Ad Hoc Distributed Queries',0;
    reconfigure;exec sp_configure 'show advanced options',0;reconfigure;

所谓的数据传输，其实是指SQLServer访问Access、Excel间的数据。
    为什么要考虑到这个问题呢？
    由于历史的原因，客户以前的数据很多都是在存入在文本数据库中，如Acess、Excel、Foxpro。现在系统升级及数据库服务器如SQLServer、ORACLE后，经常需要访问文本数据库中的数据，所以就会产生这样的需求。前段时间出差的项目，就是面临这样的一个问题：SQLServer和VFP之间的数据交换。
    要完成标题的需要，在SQLServer中是一件非常简单的事情。
    通常的可以有3种方式：1、DTS工具 2、BCP 3、分布式查询
    DTS就不需要说了，因为那是图形化操作界面，很容易上手。
    
    这里主要讲下后面两们，分别以查、增、删、改作为简单的例子：
    下面废话就不说了，直接以T-SQL的形式表现出来。用T-SQL说明这个是只在sql server中可以使用，比如说openrowset和opendatasource是sqlserver支持的函数。纯粹的accessi环境下是不支持的。
    
    一、SQLServer和Access
    
    1、查询Access中数据的方法：
    
    select * from OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user'
    或
    
    
    select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB2.mdb";User ID=Admin;Password=')serv_user
    
    2、从SQLServer向Access写数据：
    
    
    insert into OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from Accee表')
    select * from SQLServer表
    或用BCP
    
    
    master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" out  "c:\db3.mdb" -c -q -S"." -U"sa" -P"sa"'
    
    上面的区别主要是：OpenRowSet需要mdb和表存在，BCP会在不存在的时候生成该mdb
    
    3、从Access向SQLServer写数据：有了上面的基础，这个就很简单了。一定注意，这个是在sql server中输入的。
    
    
    insert into SQLServer表 select * from 
    OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from Accee表')
    
    
    如果是在access中操作的话，可以用以下的语句完成。当然，我没有写完整的语句，只是示例性的给出了如何从一个文件中读取数据。
    
    
    SELECT   *   from   表名   in   'C:\db2.mdb'   
    
    select   *   from   [;database=C:\db2.mdb].tablename 
     
    
    或用BCP
    
    
    
    master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" in  "c:\db3.mdb" -c -q -S"." -U"sa" -P"sa"'
    
    4、删除Access数据：
    
    
    delete from OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user')
    where lock=0
    
    5、修改Access数据：
    
    
    update OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user')
    set lock=1
    
    SQLServer和Access大致就这么多。
    
    
    二、SQLServer和Excel
    
    
    1、向Excel查询
    
    
    select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select * from [Sheet1$]') where c like '%f%'
    
    select * from 
    OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
    ,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\book1.xls',[sheet1$])
    
    1)hdr=yes时可以把xls的第1行作为字段看待，如第1个中hdr=no的话，where时就会报错
    2)[]和美圆$必须要，否则M$可不认这个账
    
    2、修改Execl
    
    
    update OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;','select * from [Sheet1$]')
    set a='erquan' where c like '%f%'
    
    3、导入导出
    
    
    
    insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;','select * from [Sheet2$]')(id,name)
    select id,name from serv_user
    
    或BCP
    
    
    master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" out  "c:\book2.xls" -c -q -S"." -U"sa" -P"sa"'
    
    从Excel向SQLServer导入：
    
    
    select * into serv_user_bak 
    from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select * from [Sheet1$]')
    
    如果表serv_user_bak不存在，则创建
    
    有关BCP和分布式查询的详细解答，就查SQLServer自带的帮助吧。
    SQLServer和txt文件、HTML文件、VFP文件的数据交换都显得非常容易了。。。。
    
    
    
    
    三、Access和excel
    
    在Access中读取excel
    
    SELECT * from [Excel 8.0;Database=C:\Test.xls].[Sheet1]

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')

注意如果以上语句都没问题还是报错了，那应该检查一下EXCEL是否正在打开，如果是就关闭。