2006/06/22 | SQL SERVER 与ACCESS、EXCEL的数据转换
类别(数据库学习笔记) | 评论(0) | 阅读(225) | 发表于 11:35
Datenimport/-export - Werkzeuge
Ad-hoc Abfragen mit OPENROWSET und OPENDATASOURCE
[Achtung. Diese Seite wird zur Zeit noch bearbeitet...]

Ad-hoc Abfragen sind ein flexibles Mittel, um schnell innerhalb von Transact-SQL auf Daten einer fremden Datenquelle zuzugreifen. Da bei diesem Aufruf die Verbindungsoptionen (insbesondere der Sicherheitskontext) dynamisch angegeben wird, sollte in jedem Fall geprüft werden, ob solche Abfragen überhaupt zugelassen werden sollten (s.dazu auch: OPENROWSET-Problem in SQL Server 7.0).

OPENROWSET
Mit Hilfe der Funktion OPENROWSET kann ein Rowset von einer OLEDB-Datenquelle geöffnet werden. Diese Funktion benötigt als Parameter den zu verwendenden OLEDB-Provider und alle weiteren notwendigen Verbindungseigenschaften incl. des Sicherheitskontexts. Als letzter Parameter wird die auszuführende SELECT-Anweisung angegeben.

Syntax:
Die Grundsyntax für OPENROWSET sieht so aus:

OPENROWSET ('name_des_OLEDB_providers' , 'verbindungszeichenfolge', 'abfrage')


Die Parameter
name_des_OLEDB_providers

Der Name des OLEDB-Providers, z.B. SQLOLEDB für Zugriffe auf SQL Server, Microsoft.Jet.OLEDB.4.0 für den Zugriff auf Access-Datenbanken, Excel-Arbeitsmappen, Textdateien, dBase-Dateien etc.)

verbindungszeichenfolge

Die Verbindungszeichenfolge für den OLEDB-Provider. Die hier anzugebende Zeichenfolge wird vom SQL Server intern interpretiert und entspricht (leider) nicht genau dem "gewohnten" Aufbau.

abfrage

Die Abfrage auf die Daten in der fernen Datenquelle. In der Regel ein SELECT auf die gewünschte Tabelle

Beispiele SQL Server/SQLOLEDB
Zugriff auf einen anderen SQL Server mit vertrauter Sicherheit
-- Select auf anderen SQL Server mit vertrauter Verbindung
SELECT * FROM OPENROWSET
('SQLOLEDB',
'Server=anderer_server;database=Northwind;TRUSTED_CONNECTION=YES;',
'SELECT * FROM northwind.dbo.orders')


Vorsicht Falle!

Das folgende Beispiel gibt Server und Datenbank über die OLEDB-Properties an; diese Parameter werden jedoch ignoriert

-- vorsicht: Hier werden Data Source und Initial Catalog ignoriert...
-- stattdessen wird eine Verbindung zum lokalen Server aufgebaut...
SELECT * FROM
OPENROWSET('SQLOLEDB',
'Data Source=anderer_Server;Initial Catalog=beispiel5;TRUSTED_CONNECTION=YES;',
'SELECT @@servername, * FROM northwind.dbo.orders')
Update auf Daten eines anderen SQL Servers
Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

UPDATE
OPENROWSET('SQLOLEDB',
'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',
'SELECT * FROM northwind.dbo.orders')
SET orderdate = orderdate + 1

Einfügen neuer Datensätze in einer "entfernten" Tabelle
Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

INSERT INTO
OPENROWSET('SQLOLEDB',
'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',
'SELECT * FROM northwind.dbo.region')
valueS (99, 'Test')

Löschen von Daten in einer Tabelle
Vorsicht! Diese Anweisung ändert Daten auf dem angegebenen Server.

DELETE FROM
OPENROWSET('SQLOLEDB',
'Server=anderer_server;Database=Northwind;TRUSTED_CONNECTION=YES;',
'SELECT * FROM northwind.dbo.region')
WHERE RegionID = 99


Beispiele Access Datenbanken/JET
Zugriff auf Daten in einer Access-Datenbank mit SELECT
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
';Database=C:\Access-Datenbanken\nordwind.mdb',
'SELECT * FROM artikel')
Zugriff auf Daten in einer kennwortgeschützten Access-Datenbank mit SELECT
-- Select mit Kennwortgeschützter Datenbank
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
';Database=C:\Access-Datenbanken\vertraulich.mdb;pwd=geheim;',
'SELECT * FROM umsatz')
GO
Aktualisieren von Daten in einer Jet-Datenbank
Vorsicht! Diese Anweisung ändert Daten in der angegebenen Datenbank.

UPDATE
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
';Database=C:\Access-Datenbanken\nordwind.mdb',
'SELECT * FROM artikel')
SET einzelpreis = einzelpreis + 1
GO
Beispiele Excel-Arbeitsmappen/JET
Abfragen eines Arbeitsblatts
SELECT * FROM
OPENROWSET('Microsoft.JET.OLEDB.4.0',
'Excel 8.0;Database=C:\training\inventur.xls',
'SELECT * FROM [Filiale1$ ]')
Abfragen bestimmter Zellen in einem Arbeitsblatt
SELECT * FROM
OPENROWSET('Microsoft.JET.OLEDB.4.0',
'Excel 8.0;HDR=NO;Database=C:\training\inventur.xls',
'SELECT * FROM [Filiale1$ A2:B4]')
Aktualisieren von Zellen in einem Arbeitsblatt
Vorsicht! Dieses Beispiel ändert Daten im angegebenen Excel-Arbeitsblatt

UPDATE
OPENROWSET('Microsoft.JET.OLEDB.4.0',
'Excel 8.0;Database=C:\training\inventur.xls',
'SELECT * FROM [Filiale1$ ]')
SET Bestand = 1 WHERE Bestand = 2
Hinzufügen von Daten in ein Excel-Arbeitsblatt
Vorsicht! Dieses Beispiel ändert Daten im angegebenen Excel-Arbeitsblatt

INSERT INTO
OPENROWSET('Microsoft.JET.OLEDB.4.0',
'Excel 8.0;Database=C:\training\inventur.xls',
'SELECT * FROM [Filiale1$ ]')
(Produkt, Bestand) valueS ('Test', 27)
GO

Beispiele Textdateien/JET
Abfragen von Daten aus einer Textdatei
-- Abfragen einer Textdatei
SELECT * FROM
OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Text;Database=C:\;',
'SELECT * FROM [orders#txt]')
/* Inhalt der Datei SCHEMA.INI auf C:\
[orders.txt]
Format=TABDelimited
ColNameHeader=false
MaxScanRows=20
*/
Anfügen eines Satzes in eine Textdatei
-- Einfügen eines neuen Satzes
INSERT INTO
OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Text;Database=C:\;',
'SELECT * FROM [textdemo#txt]')
valueS ('XXX', 100)
-- Prüfen, ob der neue Satz angefügt wurde:
SELECT * FROM
OPENROWSET('MICROSOFT.JET.OLEDB.4.0',
'Text;Database=C:\;',
'SELECT * FROM [textdemo#txt]')

/* Zugehörige Schema.ini Datei
[textdemo.txt]
CharacterSet=ANSI
Format=TABDelimited
ColNameHeader=TRUE
MaxScanRows=20
*/
GO

Beispiele HTML-Dateien/Jet
Lesen der Daten aus einer HTML-Tabelle mit Caption
SELECT * FROM
OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'HTML Import;HDR=NO;Database=http://localhost/beispiele/tablemitcaption.htm;',
'SELECT * FROM Beispieltabelle')




OPENDATASOURCE
OPENDATASOURCE wurde im SQL Server 2000 eingeführt. Diese Funktion erlaubt die Verwendung einer OLEDB-Datenquelle in einem vierstelligem Namen. Im Gegensatz zu OPENROWSET werden hier die "echten" OLEDB-Provider Eigenschaften verwendet.

Syntax
OPENDATASOURCE ( provider, provider_zeichenfolge )

Parameter
Beispiele SQL Server/SQLOLEDB
Lesender Zugriff auf Daten eines anderen Servers
-- Integrierte Sicherheit
SELECT * FROM
OPENDATASOURCE('SQLOLEDB',
'Data Source=itrainbo2000;Initial Catalog=Northwind;Integrated Security=SSPI;').Northwind.dbo.orders
-- SQL Login
SELECT * FROM
OPENDATASOURCE('SQLOLEDB',
'Data Source=itrainbo2000;Initial Catalog=Northwind;User ID=student;Password=#student#;').Northwind.dbo.orders

Aufruf einer gespeicherten Prozedur mit Parameter auf einem entfernten SQL Server
EXEC
OPENDATASOURCE('SQLOLEDB',
'Data Source=itrainbo2000;Initial Catalog=Northwind;Integrated Security=SSPI;').Northwind.dbo.CustorderHist 'ALFKI'


Beispiele Access/JET
SELECT auf eine Tabelle in einer Access-Datenbank
SELECT * FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Access-Datenbanken\nordwind.mdb;')...artikel
SELECT auf eine kennwortgeschützte Tabelle in einer Access-Datenbank
SELECT * FROM
OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\Access-Datenbanken\umsatz.mdb;Jet OLEDB:Database Password=geheim')...tabzahlen


Beispiele Excel-Arbeitsmappe/JET
Abfragen des gesamten Inhalts eines Arbeitsblatts
SELECT * FROM
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$ ]
Der Zugriff auf einzelne Bereiche innerhalb des Arbeitsblatt über Zellenreferenzen ist in Kombination mit OPENDATASOURCE so nicht möglich:

-- Das geht nicht:
SELECT * FROM
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$ A1:B3]

Aktualisieren von Werten in einem Excel-Arbeitsblatt
Vorsicht! Dieses Beispiel verändert Daten in dem angegebenen Arbeitsblatt

UPDATE
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$ ]
SET bestand = bestand + 1
Einfügen einer Zeile in ein Excel-Arbeitsblatt
Vorsicht! Dieses Beispiel verändert Daten in dem angegebenen Arbeitsblatt

INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$ ]
(bestand, produkt) valueS (100, 'Test')

Einfügen von Daten aus einer SQL Server Tabelle in eine Excel-Arbeitsmappe
INSERT INTO
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=Excel 8.0;Data source=C:\training\inventur.xls')...[Filiale1$ ]
(Produkt, Bestand)
SELECT ProductName, 0 FROM northwind.dbo.products

Beispiele HTML-Dateien/JET
Abfrage einer HTML-Tabelle mit Caption und Spaltenüberschriften
SELECT * FROM
OPENDATASOURCE('Microsoft.JET.OLEDB.4.0',
'Extended Properties=HTML Import;Data Source=http://localhost/beispiele/tablemitcaption.htm;')...Beispieltabelle


Erlaubte Operationen nach Datenquelle
Provider SELECT INSERT UPDATE DELETE
SQLOLEDB ja ja ja ja
Jet/Access ja ja ja ja
Jet/Excel ja ja ja nein
Jet/Text ja ja nein nein
Jet/Lotus Worksheet ja ja ja nein
Jet/HTML ja nein nein nein
Jet/dBase ja ja ja ja
Jet/Paradox ja ja ja ja

Typische Fehlermeldungen in Zusammenhang mit OPENROWSET
[OLE/DB provider returned message: Deferred prepare could not be completed.]
Sie haben beim Zugriff auf die Tabelle nicht den voll-qualifierten Namen (datenbank.besitzer.tabelle) angegeben.

Der Ad-hoc-Zugriff auf den OLE DB-Provider 'Microsoft.Jet.OLEDB.4.0' wurde verweigert. Sie müssen auf diesen Provider über einen Verbindungsserver zugreifen.
Der Ad-hoc Zugriff auf den OLEDB-Provider Jet.OLEDB wurde aus Sicherheitsgründen deaktiviert. Greifen Sie entweder über einen Verbindungsserver auf die Daten zu oder aktivieren Sie Adhoc-Zugriffe für den JET-OLEDB Provider (Achtung! Dies beeinträchtigt die Sicherheit des Servers.)

So prüfen Sie, ob Ad-Hoc Abfragen zugelassen sind:


Öffnen Sie im Enterprise Manager den Unterpunkt Sicherheit.
Öffnen Sie das Kontextmenü des Unterpunkts "Verbindungsserver" und wählen Sie den Menüpunkt "Neuer Verbindungsserver" aus.
Wählen Sie in der Auswahlliste "Providername" den Provider "Microsoft.Jet.OLEDB.4.0" aus.
Klicken Sie auf die Schaltfläche "Provideroptionen".
Prüfen Sie in der Liste "Provideroptionen" den Wert für die Option "Ad-hoc Zugriffe nicht zulassen".
Die folgende Anweisung können Sie verwenden, um per Transact-SQL Script zu prüfen, ob Ad-hoc Zugriffe für den Microsoft Jet.4.0.OLEDB Provider zugelassen sind:

EXEC master..xp_instance_regread
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.Jet.OLEDB.4.0',
N'DisallowAdhocAccess'


Der OLE DB-Provider 'Microsoft.JET.OLEDB.4.0' meldete einen Fehler.
[OLE/DB provider returned message: Installierbares ISAM nicht gefunden.]
Prüfen Sie, ob der angegebene ISAM-Treiber auf dem Server verfügbar ist. In vielen Fällen führt auch eine fehlerhafte Angabe in der Verbindungszeichenfolge zu dieser Fehlermeldung.





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电子表格软件中的数据进行转换，为我们提供了极大方便！



参考：

www.itrain.de/knowhow/sql/transfer/adhoc/




作者：apple06 2003-12-23 20:45:47)
[求助]如何获取EXCEL文件的总记录数
我想编写VBA程序,能打开已有的EXCEL文件,并获得该文件sheet的总记录数,在最后添加一些记录
作者：mikewolf2k 2003-12-23 20:51:40)

我前段时间也问过同样的问题.试试usedrange.
作者：bluemoon 2003-12-24 8:50:30)

[转帖]
Public Function SheetRows(AcWorkSheet As Excel.Worksheet) As Long

Dim OldRange As Excel.Range

Set OldRange = pubExcelApp.ActiveCell
AcWorkSheet.Range("A1").Activate
SheetRows = pubExcelApp.ActiveCell.CurrentRegion.Rows.Count '有效数据行数
OldRange.Activate '恢复工作表原来的光标位置

End Function

添加记录你看看add方法吧

作者：efan2000 2003-12-24 12:53:47)



CurrentRegion 属性
返回 Range 对象，代表当前的区域。当前区域是一个边缘是任意空行和空列组合的范围。只读。
UsedRange 属性
返回一个 Range 对象，此对象代表指定工作表中的已用区域。只读。


从这一点可以看出，CurrentRegion是以空行和空列分隔而成的区域，并不能代表所使用的区域，使用UsedRange这有个缺点，有时它的起始单元格不是从A1开始的。因而如果正确表示所有的单元格，应该是从A1到UsedRange.Cells(1, 1).Offset(UsedRange.Rows.Count - 1, UsedRange.Columns.Count - 1)，这才包含了所有的单元格。
0

评论Comments

日志分类
首页[289]
教程习题[21]
数据库学习笔记[60]
语言类学习笔记[75]
网页类学习笔记[93]
其他相关笔记[40]