DetailPage-MSS-KB

Knowledge Base

Artikel ID: 321686 - Laatste beoordeling: maandag 3 maart 2014 - Wijziging: 5.0

 

Op deze pagina

Samenvatting

In dit stapsgewijze artikel wordt beschreven hoe gegevens uit Microsoft Excel-werkbladen importeren in Microsoft SQL Server-databases met behulp van verschillende methoden.

Beschrijving van de techniek

De voorbeelden in dit artikel worden Excel-gegevens importeren met behulp van:
  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • SQL Server gekoppelde servers
  • SQL Server gedistribueerd-query 's
  • ActiveX Data Objects (ADO) en de Microsoft OLE DB-Provider voor SQL Server
  • ADO en de Microsoft OLE DB-Provider voor Jet 4.0

Vereisten

De volgende lijst bevat een overzicht van de aanbevolen hardware, software, netwerkinfrastructuur en servicepacks nodig:
  • Beschikbare exemplaar van Microsoft SQL Server 7.0 of Microsoft SQL Server 2000 of Microsoft SQL Server 2005
  • Microsoft Visual Basic 6.0 voor de voorbeelden van ADO met Visual Basic
Voor gedeelten van dit artikel wordt ervan uitgegaan dat u bekend bent met de volgende onderwerpen:
  • Data Transformation Services
  • Gekoppelde servers en gedistribueerde query 's
  • Ontwikkeling van ADO in Visual Basic

Voorbeelden

Import versus toevoegen

Het voorbeeld SQL-instructies die worden gebruikt in dit artikel demonstreren Create Table-query's die Excel-gegevens in een nieuwe SQL Server-tabel importeren met behulp van de component SELECT...IN...IN de syntaxis. U kunt deze instructies converteren naar toevoegquery's met behulp van de INSERT INTO...SELECTEER...Blijven verwijzen naar de bron- en doeladressen objecten zoals in deze codevoorbeelden uit de syntaxis tijdens.

Gebruik DTS of SSI 's

U kunt de Wizard SQL Server Data Transformation Services (DTS) importeren of de SQL Server importeren en de Wizard exporteren naar Excel-gegevens in SQL Server-tabellen importeren. Wanneer u de wizard doorloopt en één van de Excel-brontabellen selecteert, onthoud dan dat Excel-objectnamen die zijn aangevuld met een dollarteken ($) (bijvoorbeeld Blad1$) staan voor werkbladen en dat objectnamen zonder dollarteken staan voor bereiken.

Een gekoppelde Server gebruiken

Om query's vereenvoudigen, kunt u een Excel-werkmap als een gekoppelde SQL Server-server configureren.Voor meer informatie klikt u op het volgende artikelnummer om het artikel in de Microsoft Knowledge Base weer te geven:
306397  (http://support.microsoft.com/kb/306397/EN-US/ ) Procedure: Excel gebruiken met SQL Server gekoppelde Servers en gedistribueerde query's
De volgende code worden de gegevens uit het werkblad klanten op de gekoppelde Excel-server "EXCELLINK" in een nieuwe SQL Server-tabel met de naam XLImport1:
SELECT * INTO XLImport1 FROM EXCELLINK...[Customers$]
				
U kunt de query uit op de bron ook uitvoeren op een wijze die passthrough met QueryOpenen (OpenQuery) als volgt:
SELECT * INTO XLImport2 FROM OPENQUERY(EXCELLINK,
    'SELECT * FROM [Customers$]')
				

Gedistribueerde query's gebruiken

Als u niet een permanente verbinding met de Excel-werkmap als een gekoppelde server configureren wilt, kunt u de gegevens voor een bepaald doel importeren via de functie OPENROWSET of met de OPENDATASOURCE. De volgende codevoorbeelden importeren ook de gegevens van de klanten van het Excel-werkblad in de nieuwe SQL Server-tabellen:
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$]')
				

Gebruik ADO en SQLOLEDB

Als u bent verbonden met SQL Server in een ADO-toepassing met behulp van Microsoft OLE DB voor SQL Server (SQLOLEDB), kunt u dezelfde syntaxis 'gedistribueerde query' uit de Met behulp van gedistribueerde query 's sectie voor het importeren van Excel-gegevens in SQL Server.

In het volgende codevoorbeeld van Visual Basic 6.0 is vereist dat u een projectverwijzing naar ActiveX Data Objects (ADO) toevoegen. Dit codevoorbeeld laat ook zien OPENDATASOURCE en OPENROWSET via een SQLOLEDB-verbinding gebruiken.
    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
				

ADO en de Jet-Provider gebruiken

Het voorbeeld in het voorgaande gedeelte gebruikt ADO met de SQLOLEDB-Provider verbinding maken met het doel van het importeren van Excel-SQL. U kunt ook de OLE DB-Provider voor Jet 4.0 verbinding maken met het Excel-bron.

De Jet database engine kan verwijzen naar externe databases in SQL-instructies met behulp van een speciale syntaxis met drie verschillende indelingen:
  • [Volledig pad naar Microsoft Access-database].[Tabelnaam]
  • [ISAM-naam;ISAM-verbindingsreeks].[Tabelnaam]
  • [ODBC;ODBC-verbindingsreeks].[Tabelnaam]
In deze sectie wordt de derde indeling te maken van een ODBC-verbinding met de SQL Server-database. U kunt een ODBC Data Source Name (DSN) of een DSN-loze verbindingsreeks:
DSN:
    [odbc;DSN=<DSN name>;UID=<user>;PWD=<password>]

DSN-less:
   [odbc;Driver={SQL Server};Server=<server>;Database=<database>;
       UID=<user>;PWD=<password>]
				
In het volgende codevoorbeeld van Visual Basic 6.0 is vereist dat u een projectverwijzing toevoegen aan ADO. Dit codevoorbeeld wordt aangegeven hoe u Excel-gegevens importeren met SQL Server via een ADO-verbinding met de Provider van 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
				
Ook kunt u deze syntaxis die de Jet-Provider ondersteunt, Excel-gegevens importeren in andere Microsoft Access-databases, sequentiële toegang geïndexeerde methode (ISAM) ("bureaublad") databases of ODBC-databases.

Problemen oplossen

  • Houd er rekening mee dat namen van Excel-object dat met een dollarteken ($) zijn toegevoegd (bijvoorbeeld Blad1$) werkbladen vertegenwoordigen en dat gewone objectnamen voor Excel benoemde bereiken.
  • In sommige gevallen, vooral wanneer u de Excel-brongegevens aanwijzen met de naam van de tabel in plaats van een selectiequery, de kolommen in de doeltabel voor SQL Server gerangschikt in alfabetische volgorde.Voor meer informatie over dit probleem met de Jet-Provider, klikt u op het volgende artikelnummer om het artikel in de Microsoft Knowledge Base weer te geven:
    299484  (http://support.microsoft.com/kb/299484/EN-US/ ) PRB: Kolommen alfabetisch worden gesorteerd wanneer u met ADOX kolommen van de Access-tabel ophalen
  • Wanneer de Jet-Provider bepaalt dat een Excel-kolom gemengde tekst en numerieke gegevens bevat, wordt de Jet-Provider wordt het gegevenstype 'meeste' geselecteerd en niet-overeenkomende waarden als null-waarden als resultaat.Voor meer informatie over hoe u dit probleem wilt omzeilen, klikt u op het volgende artikelnummer om het artikel in de Microsoft Knowledge Base weer te geven:
    194124  (http://support.microsoft.com/kb/194124/EN-US/ ) PRB: Excel-waarden worden geretourneerd als NULL met OpenRecordset van DAO

Referenties

Voor meer informatie over hoe u Excel als gegevensbron gebruikt, klikt u op het volgende artikelnummer om het artikel in de Microsoft Knowledge Base weer te geven:
257819  (http://support.microsoft.com/kb/257819/EN-US/ ) Procedure: ADO gebruiken met Excel-gegevens vanuit Visual Basic of VBA
Voor meer informatie over het overdragen van gegevens naar Excel, klikt u op de volgende artikelnummers om de artikelen in de Microsoft Knowledge Base:
295646  (http://support.microsoft.com/kb/295646/EN-US/ ) Procedure: Gegevens uit een ADO-gegevensbron in Excel met behulp van ADO
247412  (http://support.microsoft.com/kb/247412/EN-US/ ) INFO: Methoden voor het overdragen van gegevens naar Excel vanuit Visual Basic
246335  (http://support.microsoft.com/kb/246335/EN-US/ ) Procedure: Gegevens uit een ADO-Recordset naar Excel automatisering
319951  (http://support.microsoft.com/kb/319951/EN-US/ ) Procedure: Gegevens naar Excel overbrengen met behulp van SQL Server Data Transformation Services
306125  (http://support.microsoft.com/kb/306125/EN-US/ ) Procedure: Gegevens uit SQL Server importeren in Microsoft Excel

De informatie in dit artikel is van toepassing op:
  • Microsoft Excel 2000 Standard Edition
  • Microsoft SQL Server 2000 Standard Edition
  • Microsoft SQL Server 2000 64-bit Edition
  • Microsoft SQL Server 7.0 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Workgroup Edition
Trefwoorden: 
kbhowtomaster kbjet kbmt KB321686 KbMtnl
Machine-translated ArticleMachine-translated Article
BELANGRIJK: Dit artikel is vertaald door middel van automatische vertalingssoftware van Microsoft en is mogelijk nabewerkt door de Microsoft Community via CTF-technologie (Community Translation Framework) of door een menselijke vertaler. Microsoft biedt zowel automatisch vertaalde, door mensen vertaalde en door de community nabewerkte artikelen aan, zodat er in meerdere talen toegang is tot alle artikelen in onze Knowledge Base. Een vertaald of bewerkt artikel kan fouten bevatten in vocabulaire, syntaxis of grammatica.. Microsoft is niet verantwoordelijk voor eventuele onjuistheden, fouten of schade ten gevolge van een foute vertaling van de inhoud van een bericht of het gebruik van deze vertaalde berichten door onze klanten.
De Engelstalige versie van dit artikel is de volgende: 321686  (http://support.microsoft.com/kb/321686/en-us/ )
Delen
Extra ondersteuningsopties
Microsoft Community Support-forums
Neem rechtstreeks contact met ons op
Een door Microsoft gecertificeerde partner zoeken
Microsoft Store