Je bent nu offline; er wordt gewacht tot er weer een internetverbinding is

Gegevens importeren vanuit Excel naar SQL Server

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.

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

back to the top

Beschrijving van de techniek

In de voorbeelden in dit artikel worden Excel-gegevens geïmporteerd met behulp van:
  • SQL Server Data Transformation Services (DTS)
  • Microsoft SQL Server 2005 Integration Services (SSIS)
  • SQL Server gekoppelde servers
  • Query's voor SQL Server gedistribueerd
  • ActiveX Data Objects (ADO) en de Microsoft OLE DB-Provider voor SQL Server
  • ADO en de Microsoft OLE DB-Provider voor Jet 4.0
back to the top

Vereisten

De volgende lijst bevat een overzicht van de aanbevolen hardware, software, netwerkinfrastructuur en servicepacks die u nodig heeft:
  • 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
Gedeelten van dit artikel wordt ervan uitgegaan dat u bekend met de volgende onderwerpen bent:
  • Data Transformation Services
  • Gekoppelde servers en gedistribueerde query 's
  • Ontwikkeling van ADO in Visual Basic
back to the top

Voorbeelden

Import versus toevoegen

De 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... FROM de syntaxis. Met deze instructies kunt u converteren naar toevoegquery's met behulp van de INSERT INTO... SELECTEER... VAN syntaxis terwijl u nog steeds verwijzen naar de bron- en doeladressen objecten zoals in deze codevoorbeelden.

back to the top

Gebruik DTS of SSI 's

Kunt u 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 worden doorlopen en de brontabellen Excel selecteert, houd er rekening mee dat namen van Excel-object dat met een dollarteken ($) zijn toegevoegd (bijvoorbeeld Blad1$) werkbladen vertegenwoordigen en dat duidelijke namen zonder het dollarteken Excel vertegenwoordigen bereiken benoemde.

back to the top

Een gekoppelde Server gebruiken

Om query's te vereenvoudigen , kunt u een Excel-werkmap configureren als een gekoppelde server in SQL Server.Voor meer informatie klikt u op het onderstaande artikelnummer om het artikel in de Microsoft Knowledge Base:
306397 Procedure: Excel gebruiken met SQL Server gekoppelde Servers en gedistribueerde query's
Met 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 geladen:
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$]')				
back to the top

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 ook importeren de gegevens van de klanten van de Excel-werkblad in 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$]')				
back to the top

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 het project een verwijzing 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				
back to the top

ADO en de Jet-Provider gebruiken

Het monster in de voorgaande sectie maakt ADO met de SQLOLEDB-Provider verbinding met het doel van het importeren van Excel naar 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]. [Naam van de tabel]
  • [De naam ISAM;ISAM-verbindingsreeks]. [Naam van de tabel]
  • [ODBC;ODBC-verbindingsreeks]. [Naam van de tabel]
In deze sectie wordt de indeling van derde naar een ODBC-verbinding maken 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) ("") bureaubladdatabases of ODBC-databases.

back to the top

Het oplossen van problemen

  • Houd er rekening mee dat namen van Excel-object dat met een dollarteken ($) zijn toegevoegd (bijvoorbeeld Blad1$) werkbladen vertegenwoordigen en dat gewone objectnamen Excel benoemde bereiken voor.
  • In sommige gevallen, vooral wanneer u de Excel-brongegevens aanwijzen met de naam van de tabel in plaats van een SELECT-query de kolommen in de doeltabel voor SQL Server zijn opnieuw gerangschikt in alfabetische volgorde.Voor meer informatie over dit probleem met de Jet-Provider, klikt u op het onderstaande artikelnummer om het artikel in de Microsoft Knowledge Base:
    299484 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, kan 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 onderstaande artikelnummer om het artikel in de Microsoft Knowledge Base:
    194124 PRB: Excel-waarden worden geretourneerd als NULL met OpenRecordset van DAO
back to the top
Referenties
Voor meer informatie over hoe u Excel als gegevensbron gebruikt, klikt u op het onderstaande artikelnummer om het artikel in de Microsoft Knowledge Base:
257819 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 Procedure: Gegevens uit een ADO-gegevensbron in Excel met behulp van ADO
247412 INFO: Methoden voor het overdragen van gegevens naar Excel vanuit Visual Basic
246335 Procedure: Gegevens uit een ADO-Recordset naar Excel automatisering
319951 Procedure: Gegevens naar Excel overbrengen met behulp van SQL Server Data Transformation Services
306125 Procedure: Gegevens uit SQL Server importeren in Microsoft Excel
back to the top
jet sql ado dts-sqloledb in Excel

Waarschuwing: dit artikel is automatisch vertaald

Eigenschappen

Artikel-id: 321686 - Laatst bijgewerkt: 09/05/2015 08:17:00 - Revisie: 6.0

  • 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
  • kbhowtomaster kbjet kbmt KB321686 KbMtnl
Feedback