Zaloguj si�

Select the product you need help with

JAK: Importowanie danych z programu Excel do programu SQL Server

Numer ID artyku�u: 321686 - Zobacz jakich produkt�w dotycz� zawarte w tym artykule porady.

Ten artyku� zosta� opublikowany wcze�niej pod numerem PL321686

Streszczenie

W tym artykule opisano krok po kroku r�ne metody importowania danych z arkuszy programu Microsoft Excel do baz danych programu Microsoft SQL Server.

Opis techniki

W przyk�adach podanych w tym artykule, dane s� importowane do programu Excel za pomoc�:

Us�ugi Data Transformation Services (DTS) programu SQL Server
Serwer�w po��czonych programu SQL Server
Kwerend rozproszonych programu SQL Server
Obiekt�w ADO (ActiveX Data Objects) oraz dostawcy Microsoft OLE DB dla programu SQL Server
Obiekt�w ADO oraz dostawcy Microsoft OLE DB dla aparatu Jet 4.0

Wymagania

Na poni�szej li�cie wymieniono zalecany sprz�t, oprogramowanie, infrastruktur� sieci i wymagane dodatki Service Pack:

Program Microsoft SQL Server 7.0 lub Microsoft SQL Server 2000
Program Microsoft Visual Basic 6.0 dla przyk�ad�w obiekt�w ADO korzystaj�cych z programu Visual Basic

W cz�ci artyku�u za�o�ono, �e czytelnik zna nast�puj�ce tematy:

Us�ugi Data Transformation Services
Serwery po��czone oraz kwerendy rozproszone
Opracowywanie obiekt�w ADO w programie Visual Basic

Przyk�ady

Importowanie a do��czanie

U�yte w tym artykule przyk�adowe instrukcje j�zyka SQL korzystaj� z kwerend Create Table importuj�cych dane programu Excel do nowej tabeli programu SQL Server za pomoc� sk�adni SELECT...INTO...FROM. Instrukcje te mo�na przekonwertowa� do kwerend Append u�ywaj�c sk�adni INSERT INTO...SELECT...FROM i nadal odwo�ywa� si� do obiekt�w �r�d�owych i docelowych w spos�b pokazany w kodach przyk�adowych.

Korzystanie z us�ug DTS

Kreatora importu us�ug DTS (Data Transformation Services) programu SQL Server mo�na u�y� do importowania danych programu Excel do tabel programu SQL Server. Podczas wykonywania krok�w kreatora i wybierania tabel �r�d�owych programu Excel nale�y pami�ta�, �e nazwy obiekt�w programu Excel ko�cz�ce si� znakiem dolara ($) oznaczaj� arkusze (na przyk�ad Arkusz1$); nazwy obiekt�w bez znaku dolara oznaczaj� nazwane zakresy programu Excel.

Korzystanie z serwera po��czonego

Aby upro�ci� kwerendy, arkusz programu Excel mo�na skonfigurowa� w programie SQL Server jako serwer po��czony. Aby uzyska� dodatkowe informacje, kliknij numer artyku�u poni�ej w celu wy�wietlenia tego artyku�u z bazy wiedzy Microsoft Knowledge Base:

306397

(http://support.microsoft.com/kb/306397/PL/ )

HOWTO: Use Excel with SQL Server Linked Servers and Distributed Queries

Za pomoc� nast�puj�cego kodu importowane s� dane z arkusza Klienci znajduj�cego si� na serwerze po��czonym programu Excel o nazwie �EXCELLINK� do nowej tabeli programu SQL Server o nazwie XLImport1:

SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]

Mo�na r�wnie� wykona� kwerend� przekazuj�c� za pomoc� instrukcji OPENQUERY, jak nast�puje:

SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')

Korzystanie z kwerend rozproszonych

Je�li nie chce si� konfigurowa� sta�ego po��czenia z arkuszem programu Excel w postaci serwera po��czonego, mo�na zaimportowa� potrzebne dane z u�yciem funkcji OPENDATASOURCE lub OPENROWSET. Za pomoc� nast�puj�cego kodu przyk�adowego importowane s� dane z arkusza Klienci programu Excel do nowych tabel programu SQL Server:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')

Korzystanie z obiekt�w ADO oraz dostawcy SQLOLEDB

Po po��czeniu z programem SQL Server w aplikacji ADO za pomoc� dostawcy Microsoft OLE DB for SQL Server (SQLOLEDB), do importowania danych programu Excel do programu SQL Server mo�na u�ywa� tej samej sk�adni �kwerendy rozproszonej� co w cz�ci Korzystanie z kwerend rozproszonych.

Nast�puj�cy kod przyk�adowy programu Visual Basic 6.0 wymaga dodania w projekcie odwo�ania do obiekt�w ADO (ActiveX Data Objects). Za pomoc� nast�puj�cego kodu przyk�adowego pokazano spos�b u�ycia funkcji OPENDATASOURCE oraz OPENROWSET przez po��czenie SQLOLEDB.

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;Data Source=<server>;" & _
        "Initial Catalog=<database>;User ID=<user>;Password=<password>"

    'Import by using OPENDATASOURCE.
    strSQL = "SELECT * INTO XLImport6 FROM " & _
        "OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', " & _
        "'Data Source=C:\test\xltest.xls;" & _
        "Extended Properties=Excel 8.0')...[Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and object name.
    strSQL = "SELECT * INTO XLImport7 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "[Customers$])"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    'Import by using OPENROWSET and SELECT query.
    strSQL = "SELECT * INTO XLImport8 FROM " & _
        "OPENROWSET('Microsoft.Jet.OLEDB.4.0', " & _
        "'Excel 8.0;Database=C:\test\xltest.xls', " & _
        "'SELECT * FROM [Customers$]')"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff

    cn.Close
    Set cn = Nothing

Korzystanie z obiekt�w ADO oraz dostawcy aparatu Jet

W przyk�adzie podanym w poprzedniej cz�ci korzysta si� z obiekt�w ADO oraz dostawcy SQLOLEDB do ��czenia si� z miejscem docelowym importowania do programu SQL Server z programu Excel. Do po��czenia si� ze �r�d�em programu Excel mo�na r�wnie� u�y� dostawcy OLE DB dla aparatu Jet 4.0.

Aparat bazy danych Jet mo�e odwo�ywa� si� w instrukcji SQL do zewn�trznych baz danych za pomoc� specjalnej sk�adni wyst�puj�cej w trzech r�nych formatach:

[Pe�na �cie�ka do bazy danych programu Microsoft Access].[Nazwa tabeli]
[Nazwa metody ISAM;Ci�g po��czenia metody ISAM].[Nazwa tabeli]
[ODBC;Ci�g po��czenia ODBC].[Nazwa tabeli]

W tej cz�ci u�yto trzeciego formatu do utworzenia po��czenia ODBC z docelow� baz� danych programu SQL Server. Mo�na u�y� ci�gu po��czenia ODBC Data Source Name (DSN) lub bez nazwy DSN:

DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]

Nast�puj�cy kod przyk�adowy programu Visual Basic 6.0 wymaga dodania w projekcie odwo�ania do obiekt�w ADO. W kodzie pokazano spos�b importowania danych programu Excel do programu SQL Server przez po��czenie ADO za pomoc� dostawcy aparatu Jet 4.0.

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim lngRecsAff As Long
    Set cn = New ADODB.Connection
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\test\xltestt.xls;" & _
        "Extended Properties=Excel 8.0"
    
    'Import by using Jet Provider.
    strSQL = "SELECT * INTO [odbc;Driver={SQL Server};" & _
        "Server=<server>;Database=<database>;" & _
        "UID=<user>;PWD=<password>].XLImport9 " & _
        "FROM [Customers$]"
    Debug.Print strSQL
    cn.Execute strSQL, lngRecsAff, adExecuteNoRecords
    Debug.Print "Records affected: " & lngRecsAff
        
    cn.Close
    Set cn = Nothing

Do zaimportowania danych programu Excel do innych baz danych programu Microsoft Access, baz danych ISAM (indexed sequential access method) typu �desktop� lub baz danych ODBC, mo�na u�y� tej sk�adni (obs�ugiwanej przed dostawc� aparatu Jet).

Rozwi�zywanie problem�w

Nale�y pami�ta�, �e nazwy obiekt�w programu Excel ko�cz�ce si� znakiem dolara ($) oznaczaj� arkusze (na przyk�ad Arkusz1$); nazwy obiekt�w bez znaku dolara oznaczaj� nazwane zakresy programu Excel.
W niekt�rych przypadkach, zwykle podczas wyznaczania danych �r�d�owych programu Excel za pomoc� nazwy tabeli, a nie kwerendy SELECT, kolumny w tabeli docelowej programu SQL Server s� rozmieszczane w porz�dku alfabetycznym. Aby uzyska� dodatkowe informacje dotycz�ce problemu z dostawc� aparatu Jet, kliknij numer artyku�u poni�ej w celu wy�wietlenia tego artyku�u z bazy wiedzy Microsoft Knowledge Base:
299484
(http://support.microsoft.com/kb/299484/PL/ )
PRB: Columns Are Sorted Alphabetically When You Use ADOX to Retrieve Columns of Access Table
Je�eli dostawca aparatu Jet ustali, �e kolumna w programie Excel zawiera pomieszanie dane tekstowe i numeryczne, dostawca aparatu Jet wybiera �wi�kszo�ciowy� typ danych i zwraca niezgodne warto�ci jako NULL. Aby uzyska� dodatkowe informacje, jak obej�� ten problem, kliknij numer artyku�u poni�ej w celu wy�wietlenia tego artyku�u z bazy wiedzy Microsoft Knowledge Base:
194124
(http://support.microsoft.com/kb/194124/PL/ )
PRB: Excel Values Returned as NULL Using DAO OpenRecordset

Powr�t na g�r� | Przeka� opini�

Materia�y referencyjne

Aby uzyska� dodatkowe informacje dotycz�ce sposobu u�ywania programu Excel jako �r�d�a danych, kliknij numer artyku�u poni�ej w celu wy�wietlenia tego artyku�u z bazy wiedzy Microsoft Knowledge Base:

257819

(http://support.microsoft.com/kb/257819/PL/ )

HOWTO: Use ADO with Excel Data from Visual Basic or VBA

Aby uzyska� dodatkowe informacje dotycz�ce przesy�ania danych do programu Excel, kliknij numery artyku��w poni�ej w celu wy�wietlenia tych artyku��w z bazy wiedzy Microsoft Knowledge Base:

295646

(http://support.microsoft.com/kb/295646/PL/ )

HOWTO: Transfer Data from ADO Data Source to Excel with ADO

247412

(http://support.microsoft.com/kb/247412/PL/ )

INFO: Methods for Transferring Data to Excel from Visual Basic

246335

(http://support.microsoft.com/kb/246335/PL/ )

HOWTO: Transfer Data from an ADO Recordset to Excel with Automation

319951

(http://support.microsoft.com/kb/319951/PL/ )

HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services