Jesteś obecnie w trybie offline. Czekamy na ponowne połączenie z Internetem.

JAK: Importowanie danych z programu Excel do programu SQL Server

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 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ęściKorzystanie 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
excel sql ado dts sqloledb jet
Właściwości

Identyfikator artykułu: 321686 — ostatni przegląd: 09/19/2003 19:50:46 — zmiana: 3.3

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

  • kbatm kbgrpdsvbdb kbhowto kbhowtomaster kbjet kbsql KB321686
Opinia