Excel 和 SQL
来源:互联网 发布:人类曾经被毁灭 知乎 编辑:程序博客网 时间:2024/06/07 00:56
1.
SQL Server 与 Excel 相互调用
SQL Server 与 Excel 之间直接调用,可以直接完成相互导入导出。基本思想是把Excel作为数据库进行处理,通过调用不同数据库来完成数据处理。
1.从excel直接读入数据库
- insert into t_test ( 字段 )
- select 字段
- FROM OpenDataSource( @#Microsoft.Jet.OLEDB.4.0@#,
- @#Data Source="C:/test.xls";
- User ID=Admin;Password=;
- Extended properties=Excel 8.0@#)...[sheet1$]
2.从数据库直接写入excel
- exec master..xp_cmdshell @# bcp "SELECT au_fname, au_lname FROM pubs..authors ORDER BY au_lname" queryout c:/test.xls -c -S"soa" -U"sa" -P"sa" @#
注意:参数的大小写,另外这种方法写入数据的时候没有标题。
================================================================================================================
2.http://bbs.csdn.net/topics/300032366
SQL SERVER 与ACCESS、EXCEL的数据转换
熟悉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电子表格软件中的数据进行转换,为我们提供了极大方便!
EXEC
master..xp_cmdshell
'bcp "select OrderID,ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind.dbo.orders" queryout "d:\Oreders.txt" -t"|" -c -q -S"127.0.0.1" -U"sa" -P""'
SELECT
*
FROM
OpenDataSource(
'Microsoft.Jet.OLEDB.4.0'
,
'Data Source="xxxx.xls";Extended Properties="Excel 8.0";IMEX=1;Persist Security Info=False'
)...[a1$]
二、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文件到SQL Server 2005
http://topic.csdn.net/u/20081125/18/9007115f-b276-4ca6-b3a7-12db9aa983d5.html
/*
一个目录中有多个Excel文件,每个EXCEL文件中有不定个数的工作表,工作表名称也可能不同,
但是每个工作表的结构是相同的,所有文件中的工作表都导入同一个表中。如何实现?
我现在采用两个FOREACH循环嵌套的方式,可以循环目录中所有的文件,但是仅限于所有的工作表跟源表具有相同的架构,
即有同样数量的工作表,相同的顺序和相同的工作表名称。
多谢!
*/
你先保证能够实现如下两篇文章的内容
批量导入excel文件
http://blog.csdn.net/jinjazz/archive/2008/07/25/2710169.aspx
循环工作表
http://blog.csdn.net/jinjazz/archive/2008/07/28/2722987.aspx
然后理解一下其中变量的用法,批量导入的时候用到一个用户::xlsFile变量,循环工作表使用了一个用户::
table
变量,
在第二篇文章中有一个细节是编辑excel数据源链接的Expressions,第16到18步。这个用法是把excel的链接改为变量定义。
同理在枚举excel文件之后,嵌套一个枚举excel架构的容器,然后把枚举链接的Expressions里面的servername
属性改为用户::xlsFile就可以了。没有这个步骤你会永远钉着一个excel来枚举所以会出错
图解,我已经测试通过了
--------------------------------------------------------------------
0029
-- CSDN 精华帖之 关于循环导入excel文件到SQL Server 2005_1.jpg
--------------------------------------------------------------------
如果是sql2005,作etl或者普通数据迁移的时候还是提倡多使用ssis,他在管理,可配置和迁移上都要远远方便于dts或者普通的sql语句.
这是我工作中的一个ssis包,虽然算不上复杂,但是如果不用ssis做资源整合将会很难管理.
--------------------------------------------------------------------
0029
-- CSDN 精华帖之 关于循环导入excel文件到SQL Server 2005_2.jpg
--------------------------------------------------------------------
我上传了ssis设计包,感兴趣的朋友可以下载看下
http://download.csdn.net/source/810313
- SQL命令 和 Excel
- Excel 和 SQL
- SQL SERVER 和EXCEL转换
- [SQL]联系使用Excel和SQL
- 使用PL/SQL Developer工具导入excel和导出excel
- 使用PL/SQL Developer工具导入excel和导出excel
- sql向 access和excel转换
- SQL 2005导入Access和Excel文件
- sql server 2005和excel互导
- sql和Excel,access之间的操作
- sql excel 的导入和导出
- sql语句导入和导出到excel
- SQL,Excel
- excel.SQL
- sql excel
- 使用SQL Server對Excel進行查看資料和Excel 導入SQL Server
- 直接复制Excel表格数据到PL/SQL和导入Excel文件到PL/SQL
- 直接复制Excel表格数据到PL/SQL和导入Excel文件到PL/SQL
- uva 1484 Alice and Bob's Trip (树形dp)
- [leetcode刷题系列]Sort Colors
- hrbust1828 剪纸条(DP)
- [Leap Motion开发系列]理解C++应用例子
- CSS hack原理
- Excel 和 SQL
- 从DC中得到位图数据
- hrbust1818 石子合并问题--直线版 (经典区间DP)
- 分类:决策树—— 对连续属性进行“二元转化”划分,根据增益量公式求最佳划分点
- poj-2406 Power Strings睡前一水~
- Maven深入学习(四)- 知识总结
- String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path
- 如今建立新的CDN是一个利润丰厚的商业模式吗?
- 简单匹配算法测试