Er zijn verschillende manieren om SQL Server-gegevens te exporteren/importeren naar een Excel-bestand. In dit artikel worden een aantal manieren uitgelegd om SQL Server-gegevens te importeren en exporteren naar een Excel-bestand:
- SQL Server-gegevens exporteren naar een Excel-bestand met
- de SQL Server Import and Export Wizard
- T-SQL code
- Export naar Excel vanuit ApexSQL Complete
- SQL Server-gegevens in Excel importeren met behulp van de wizard Gegevensverbinding
SQL Server-gegevens naar een Excel-bestand exporteren met behulp van de wizard SQL Server Importeren en exporteren
In SQL Server Management Studio (SSMS), is er een functie die het mogelijk maakt gegevens van een gegevensbron naar een andere gegevensbron te exporteren; In dit geval worden de gegevens uit SQL Server gekopieerd naar een Excel-bestand via de Export Wizard.
Om deze functie te gebruiken, gaat u naar Object Explorer, klikt u met de rechtermuisknop op een willekeurige database (bijv. AdventureworksDW2016CTP3), kies onder Taken de opdracht Gegevens exporteren:
Hierdoor wordt het venster Wizard SQL Server importeren en exporteren geopend:
Om verder te gaan met het exporteren van SQL Server-gegevens naar een Excel-bestand, klikt u op de knop Volgende. Het venster Kies een gegevensbron wordt geopend. In dit venster kunt u een bron kiezen van waaruit u gegevens wilt kopiëren:
Kies in de vervolgkeuzelijst Gegevensbron het item SQL Server Native Client 11.0. Kies in de vervolgkeuzelijst Servernaam de SQL Server-instance waarvan de gegevens moeten worden gekopieerd. Kies in de sectie Authenticatie de authenticatiemodus voor de gegevensbronverbinding en kies in de vervolgkeuzelijst Database de database van waaruit de gegevens zullen worden gekopieerd. Nadat al deze opties zijn ingesteld, klikt u op de knop Volgende om verder te gaan.
Dit opent het venster Bestemming kiezen waarin kan worden aangegeven waarheen de gegevens van de gegevensbron moeten worden gekopieerd:
Kies in de vervolgkeuzelijst Bestemming het item Microsoft Excel. Kies onder Excel-bestandspad de bestemming voor de gekopieerde gegevens van de SQL Server-gegevensbron (SQL Data.xlsx). Kies in de vervolgkeuzelijst Excel-versie de versie van het Microsoft Excel-werkblad. Nadat dit allemaal is ingesteld, drukt u op de knop Volgende om verder te gaan. Maar nadat u op de knop Volgende hebt gedrukt, kan de volgende waarschuwing verschijnen:
De bewerking kon niet worden voltooid.
ADDITIONELE INFORMATIE:
De ‘Microsoft.ACE.OLEDB.12.0’ provider is niet geregistreerd op de lokale machine. (System.Data)
Dit gebeurt omdat SSMS een 32-bit toepassing is en wanneer u de Export Wizard start via SSMS, start deze een 32-bit versie van de Export Wizard. Aan de andere kant is de machine (het besturingssysteem) waarop SSMS is geïnstalleerd, een 64-bits versie en is de geïnstalleerde SQL Server een 64-bits versie.
Om dit probleem op te lossen, sluit u SSMS, gaat u naar het menu Start en zoekt u de 64-bits versie van SQL Server Import en Export Data:
Na het starten van deze SQL Server Import en Export Wizard verschijnt hetzelfde venster dat via SSMS wordt gebruikt. Nadat u alle voorgaande opties hebt ingesteld, klikt u op de knop Volgende. Het venster Tabel kopiëren of Query opgeven verschijnt:
In dit venster kan worden opgegeven of gegevens uit een of meer tabellen en weergaven moeten worden gekopieerd of dat de resultaten van een query moeten worden gekopieerd.
Selecteer het keuzerondje Kopieer gegevens uit een of meer tabellen of weergaven en klik op de knop Volgende. In het venster Bron tabel en weergaven selecteren kunt u een of meer tabellen en weergaven kiezen waarvan u de SQL Server-gegevens naar een Excel-bestand wilt exporteren door het selectievakje naast de naam van de tabel/weergave aan te vinken:
Zodra het selectievakje naast een tabel/weergave is aangevinkt, wordt de naam van de tabel/weergave gekopieerd onder de kolom Bestemming. Deze naam vertegenwoordigt de naam van het blad waar de gegevens uit de tabel zullen worden geplaatst, deze naam kan worden veranderd in wat u maar wilt, maar voor nu, zal het worden gelaten zoals het is:
Om te bekijken welke gegevens zullen worden gegenereerd naar een Excel-bestand, klikt u op de knop Voorbeeld:
Nadat u hebt gekozen welke gegevens uit tabellen/weergaven naar een Excel-bestand worden gekopieerd, klikt u op de knop Volgende:
In het venster Opslaan als uitvoerpakket vinkt u het selectievakje Onmiddellijk uitvoeren aan en klikt u op de knop Volgende:
In het venster Wizard voltooien worden alle opties weergegeven die zijn gekozen voor de exportbewerkingen. Klik op de knop Voltooien om het proces van het exporteren van SQL Server-gegevens naar een Excel-bestand af te ronden. Het laatste venster van de wizard SQL Server importeren en exporteren laat zien of de gegevens met succes zijn geëxporteerd of dat er fouten zijn opgetreden tijdens het exporteren van de gegevens:
In ons geval zijn de gegevens met succes gegenereerd in het SQL Data.xlsx-bestand in het DimScenario-sheet:
Exporteer SQL Server-gegevens naar een Excel-bestand met behulp van T-SQL-code
De Transact-SQL OPENROWSET kan worden gebruikt om SQL Server-gegevens via SSMS naar een Excel-bestand te exporteren. Typ in een query-editor de volgende code en voer deze uit:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ')
SELECT * FROM dbo.DimScenario
Maar bij het uitvoeren van de bovenstaande code kan de volgende fout optreden:
Msg 15281, Level 16, State 1, Line 1
SQL Server heeft de toegang geblokkeerd tot STATEMENT ‘OpenRowset/OpenDatasource’ van component ‘Ad Hoc Distributed Queries’ omdat deze component is uitgeschakeld als onderdeel van de beveiligingsconfiguratie voor deze server. Een systeembeheerder kan het gebruik van ‘Ad Hoc Distributed Queries’ inschakelen door sp_configure te gebruiken. Voor meer informatie over het inschakelen van ‘Ad Hoc Distributed Queries’, zoekt u naar ‘Ad Hoc Distributed Queries’ in SQL Server Books Online.
Om deze fout op te lossen, moet de optie ‘Ad Hoc Distributed Queries’ worden ingeschakeld. Dit kan worden gedaan door de procedure sp_configure te gebruiken en de volgende SQL-code uit te voeren in een query-editor:
EXEC sp_configure 'show advanced options', 1RECONFIGUREEXEC sp_configure 'Ad Hoc Distributed Queries', 1RECONFIGURE
Na het uitvoeren van bovenstaande code verschijnt het volgende bericht dat aangeeft dat de opties ‘show advanced options’ en ‘Ad Hoc Distributed Queries’ zijn ingeschakeld:
Configuratieoptie ‘show advanced options’ is gewijzigd van 0 in 1. Voer het RECONFIGURE commando uit om te installeren.
Configuratie optie ‘Ad Hoc Distributed Queries’ is gewijzigd van 0 naar 1. Voer het RECONFIGURE statement uit om te installeren.
Nu, wanneer de code opnieuw wordt uitgevoerd:
INSERT INTO OPENROWSET('Microsoft.ACE.OLEDB.12.0','Excel 12.0;
Database=C:\Users\Zivko\Desktop\SQL Data.xlsx;','SELECT * FROM ')
SELECT * FROM dbo.DimScenario
De volgende fout kan verschijnen:
OLE DB provider “Microsoft.ACE.OLEDB.12.0” voor gekoppelde server “(null)” heeft bericht teruggestuurd “De Microsoft Access-database-engine kan het bestand ” niet openen of ernaar schrijven. Het is al exclusief geopend door een andere gebruiker, of u hebt toestemming nodig om de gegevens te bekijken en te schrijven.”.
Msg 7303, Level 16, State 1, Line 1
Kan het gegevensbronobject van OLE DB provider “Microsoft.ACE.OLEDB.12.0” voor gekoppelde server “(null)
Dit gebeurt meestal vanwege onvoldoende rechten.
Meer informatie over het oplossen van deze problemen kunt u vinden op de pagina Hoe query’s uitvoeren op Excel-gegevens met SQL Server gekoppelde servers.
Eén manier om dit probleem op te lossen is om SSMS als administrator te openen en de code opnieuw uit te voeren. Maar deze keer kan een andere fout verschijnen:
Msg 213, Level 16, State 1, Line 1
Kolomnaam of aantal van de geleverde waarden komt niet overeen met de tabeldefinitie.
Om dit op te lossen, opent u een Excel-bestand (bijv. SQL Data.xlsx) waarin de gegevens uit de SQL Server-tabel (bijv. dbo.DimScenario) moeten worden opgeslagen en voer de kolomnamen in die overeenkomen met de kolomnamen uit de DimScenario-tabel:
Sluit het bestand SQL Data.xlsx bestand en voer de code nogmaals uit:
Nu verschijnt de volgende melding:
(3 rijen betrokken)
Tot slot worden de gegevens uit de SQL Server-tabel naar het Excel-bestand gekopieerd:
SQL Server-gegevens in Excel importeren met behulp van het dialoogvenster Data Connection Wizard
Een andere manier om gegevens uit de SQL Server-tabel naar een Excel-bestand te kopiëren, is het gebruik van het dialoogvenster Data Connection Wizard vanuit Excel.
Open daartoe een Excel-bestand (bijv. SQL Data.xlsx) waarin u gegevens wilt importeren. Kies op het tabblad Gegevens, onder het submenu Van andere bron, de opdracht Van SQL Server:
Hierdoor wordt het dialoogvenster Wizard gegevensverbinding geopend. Voer bij Servernaam de naam in van de SQL Server-instance waarvan de gegevens moeten worden gekopieerd. Kies in het gedeelte Log on credentials de authenticatiemodus voor de gegevensbronverbinding en klik op de knop Next:
Kies in de vervolgkeuzelijst Select the database that contains the data you want drop down box de database van waaruit de gegevens zullen worden gekopieerd. In het raster worden alle beschikbare tabellen en weergaven weergegeven. Kies een gewenste tabel / view (bijv. DimScenario) en klik op de knop Volgende om verder te gaan.
In dit venster laat u alles zoals het is en drukt u op de knop Voltooien:
Kies in het dialoogvenster Gegevens importeren het keuzerondje Tabel, kies onder de sectie Waar wilt u de gegevens plaatsen? kiest u het keuzerondje Bestaand werkblad en drukt u op OK:
Nadat u op OK hebt gedrukt, worden de gegevens uit de tabel DimScenario gekopieerd naar het bestand SQL Data.xslx-bestand:
Exporteer SQL Server-gegevens naar een Excel-bestand met behulp van Exporteren naar Excel van ApexSQL Complete
Met behulp van de functie Exporteren naar Excel van ApexSQL Complete, add-in voor SSMS en Visual Studio, kunnen de gegevens van de SQL Server-tabel met één klik naar een Excel-bestand worden geëxporteerd.
In een query-editor typt u de volgende code en voert u deze uit:
SELECT * FROM dbo.DimScenario ds
Selecteer in het resultatenscherm de gegevens die u naar een Excel-bestand wilt exporteren, klik met de rechtermuisknop in het resultatenscherm en kies in het contextmenu de optie Exporteren naar Excel:
Hierdoor wordt een nieuwe Excel-werkmap geopend en de geselecteerde gegevens uit het Resultatenraster worden gekopieerd naar het eerste werkblad:
Meer over exporteren naar Excel via ApexSQL Complete kunt u vinden op de pagina Exporteren naar Excel.
Zie ook:
- Handleiding: Importeer gegevens in Excel en maak een gegevensmodel
- Ga aan de slag met dit eenvoudige voorbeeld van de wizard Importeren en exporteren
- Hoe query’s op Excel-gegevens uit te voeren met SQL Server gekoppelde servers
- Exporteren naar Excel