JAK: Importowanie danych z programu Excel do programu SQL Server

Tłumaczenia artykułów Tłumaczenia artykułów
Numer ID artykułu: 321686 - Zobacz jakich produktów dotyczą zawarte w tym artykule porady.
Ten artykuł został opublikowany wcześniej pod numerem PL321686
Rozwiń wszystko | Zwiń wszystko

Na tej stronie

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 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 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 PRB: Excel Values Returned as NULL Using DAO OpenRecordset

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 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 HOWTO: Transfer Data from ADO Data Source to Excel with ADO
247412 INFO: Methods for Transferring Data to Excel from Visual Basic
246335 HOWTO: Transfer Data from an ADO Recordset to Excel with Automation
319951 HOW TO: Transfer Data to Excel by Using SQL Server Data Transformation Services
306125 JAK: Importowanie danych z programu Microsoft SQL Server do programu Microsoft Excel

Właściwości

Numer ID artykułu: 321686 - Ostatnia weryfikacja: 19 września 2003 - Weryfikacja: 3.3
Informacje zawarte w tym artykule dotyczą:
  • Microsoft Excel 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
Słowa kluczowe: 
kbatm kbgrpdsvbdb kbhowto kbhowtomaster kbjet kbsql KB321686

Przekaż opinię

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com