gegevens exporteren van SQL Server naar Excel

gegevens exporteren van SQL Server naar Excel

in een vorig artikel, SQL en Excel: waarom u beide nodig hebt, hebben we aangetoond dat Voor veel bedrijven het gebruik van zowel SQL Server als Excel een optimale benadering van gegevensbeheer is. Voor organisaties die SQL Server en Excel uitgebreid gebruiken, is het vaak nodig om gegevens van SQL Server naar Excel te exporteren. De meest voorkomende redenen om de gegevens in Excel te krijgen zijn om:

• produceer datavisualisaties zoals grafieken en grafieken voor gebruik in rapporten
• verzamel of analyseer gegevens in draaigrafieken
• verzamel gegevens uit meerdere bronnen voor verdere transformatie
• maak de gegevens beschikbaar voor niet-databasegebruikers om

te bekijken of te verwerken In dit artikel gaan we kijken hoe gegevens van SQL Server naar Excel kunnen worden geëxporteerd. De eerste methode maakt gebruik van de Wizard Importeren en exporteren van SQL Server, die kan worden gebruikt om gegevens te exporteren naar een aantal doeltypen, niet alleen Excel. De tweede methode maakt gebruik van native functionaliteit in Excel. De derde methode maakt gebruik van de SQL Spreads Add-In voor Excel, die functionaliteit biedt buiten het exportproces. Er zijn voors en tegens van elke methode, die we zullen samenvatten aan het einde van het artikel.

Methode (1): exporteren met behulp van de Wizard Importeren en exporteren van SQL Server

de Wizard Importeren en exporteren van SQL Server kan worden gestart vanuit SQL Server Management Studio of als een zelfstandige toepassing.

vanuit SQL Server Management Studio klikt u op de database van waaruit u gegevens wilt exporteren. We gebruiken de demo-database in ons voorbeeld, dus klik daarop en klik met de rechtermuisknop en selecteer taken > gegevens exporteren.

de welkomstpagina wordt weergegeven, en u kunt doorgaan en op ‘Next’klikken.

Selecteer op het volgende scherm de gegevensbron. Om vanuit SQL Server te exporteren, selecteert u ‘SQL Server Native Client’.

zodra u ‘SQL Server Native Client’ hebt geselecteerd, worden de details over de gegevensbron ingevuld door de wizard.

klik op ‘Next’ om naar het volgende scherm te gaan, waar u de bestemming voor de export kiest, wat voor ons Excel is.

wanneer u Excel als bestemming selecteert, moet u de locatie van het Excel-bestand opgeven. Wanneer u vervolgens op ‘Next’ klikt, kunt u het volgende bericht zien:

de bewerking kon niet worden voltooid.
aanvullende informatie:
de ” Microsoft.ACE.OLEDB.16.0 ‘ provider is niet geregistreerd op de lokale machine. (Systeem.Gegevens)

de reden dat dit bericht wordt weergegeven is dat wanneer u de Wizard SQL Server importeren en exporteren vanuit SSMS Start, een 32-bits versie van de wizard wordt gestart (omdat SSMS 32-bits is) en uw besturingssysteem 64-bits is. Meer informatie hierover is hier te vinden.

om dit te omzeilen, moet u SSM ‘ s sluiten en naar het Windows Start menu gaan en zoeken naar de 64-bits versie van de SQL Server Import En Export Wizard vanuit het Start menu. Merk op dat deze versie alleen op uw computer wordt geïnstalleerd als u SQL Server hebt geïnstalleerd.

zodra de stand-alone versie van de Wizard is gestart, kunt u de tot nu toe besproken stappen doorlopen, namelijk het specificeren van de bron en de bestemming van de gegevenskopie. We moeten nu definiëren wat we willen kopiëren en (optioneel) de kopieerbewerking configureren.

na het klikken op’ Volgende ‘op het scherm’ Kies een bestemming’, geeft u aan of u een of meer volledige tabellen of weergaven wilt exporteren, of een subset van gegevens via een SQL-query. In ons geval exporteren we vanuit één tabel, zodat we de standaardoptie kunnen laten geselecteerd.

vervolgens gaan we de kolomtoewijzingen bewerken door op de knop ’toewijzingen bewerken…’ te klikken. Met het scherm Kolomtoewijzingen kunt u wijzigingen aanbrengen in de naam en het gegevenstype van de kolommen die in Excel worden gemaakt door de kopieerbewerking. In ons voorbeeld gaan we het gegevenstype voor de kolom ‘order_date’ wijzigen van VarChar naar DateTime. We laten de anderen als de standaard ingesteld door de wizard. Klik Op ‘Volgende’. Het volgende scherm in de wizard vat samen dat de bron een SQL-tabel met de naam sales is en de bestemming zal zijn naar een blad met de naam ‘sales’ in de Excel-werkmap. U kunt de naam van het blad indien nodig wijzigen.

als u klaar bent, klikt u op’ Ok ‘om het dialoogvenster te sluiten en vervolgens op’Volgende’.

als een van de toewijzingen die we hebben opgegeven mogelijk niet slaagt in het exportproces, toont de Wizard Importeren en exporteren van SQL Server de pagina toewijzing van gegevenstype controleren. Op deze pagina worden de conversies gemarkeerd die de wizard moet uitvoeren om de toewijzingen af te handelen die u hebt opgegeven. Het geeft ook aan wat de wizard zal doen als er een fout tijdens de conversie. Het kan ofwel de fout negeren of ervoor zorgen dat de export mislukt. In ons voorbeeld zijn verschillende kolommen gemarkeerd: die waar er een conversie is van nvarchar (max) naar het Excel-equivalent, lange tekst, en ook de conversie van VarChar naar DateTime voor de kolom ‘order_date’. De standaardinstelling is dat de wizard mislukt als er een fout is, dus we laten die optie op zijn plaats.

Opmerking: Deze Wizard SQL importeren en exporteren gebruikt SSIS onder de motorkap; met andere woorden, elke keer dat u de wizard uitvoert, maakt u een SSIS-pakket aan. Dit is de reden waarom u de mogelijkheid van het opslaan voor toekomstig gebruik.Klik Op ‘Volgende’. De wizard biedt u nu de kans om de Wizard Configuratie op te slaan als een SSIS-pakket in aanvulling op de standaard optie van het uitvoeren van de kopie onmiddellijk.

we laten gewoon de standaard optie aangevinkt en klik vervolgens op de knop’ Volgende’.

de wizard toont nu een samenvatting van de stappen die we hebben geconfigureerd.

klik op ‘Voltooien’, en de wizard zal nu het uiteindelijke scherm tonen, dat het resultaat is van de uitvoering. In ons geval was de uitvoering succesvol en we kunnen zien dat 5484 records zijn gekopieerd naar het ‘Query’ – blad in onze Excel-werkmap.

we kunnen controleren of de gegevens van SQL Server naar Excel zijn geëxporteerd door het bestand dat we hebben opgegeven te openen en het ‘sales’ – blad te controleren.

nu de gegevens in Excel staan, kunnen we enkele visualisaties of aggregaties maken, of welke andere verwerking dan ook die we willen uitvoeren. We kunnen bijvoorbeeld een draaigrafiek maken om het totaal per item per regio weer te geven.

Methode (2): Exporteren met behulp van native functionaliteit in Excel

hoewel de methode zelf vrijwel hetzelfde is, heeft Microsoft verbeteringen aangebracht in de laatste paar releases op de manier waarop u gegevens van andere bronnen in Excel krijgt. De belangrijkste verandering was de toevoeging van de Power Query data import wizards en tools als een native component in Excel 2016. Deze worden benaderd vanuit de Power Query-Editor, hoewel het niet als zodanig wordt weergegeven op het Excel – lint-in plaats daarvan wordt het gebrandmerkt als Get & Transform Data group op het tabblad Data van het Excel-lint.

de onderstaande stappen zijn geldig voor Excel 2016 en daarna.

om te beginnen met de export van SQL Server naar Excel, klikt u op Data > Get Data > from Database > From SQL Server Database.

u wordt vervolgens gevraagd de databaseserver te openen en eventueel een database op te geven. Als u geen database opgeeft, kunt u kiezen uit de beschikbare op het volgende scherm.

Klik Op ‘Ok’. Vervolgens wordt u gevraagd om authenticatiegegevens op te geven. De standaard is Windows, dus je kunt doorgaan en op ‘Connect’ klikken.

in het volgende scherm kunt u door de databases en tabellen navigeren op de databaseserver die u hebt opgegeven. Klik op de ‘ demo ‘database om het uit te breiden en klik vervolgens op de’ sales ‘ tabel. De gegevens voor de geselecteerde tabel worden weergegeven in het rechtervenster. We konden nu op de knop ‘Laden’ klikken om de gegevens onmiddellijk in Excel te krijgen. Als alternatief kunnen we op ‘data transformeren’ klikken om transformaties op de data uit te voeren (bijvoorbeeld gegevenstypen wijzigen, wat gegevens opschonen, berekeningen uitvoeren). Voor dit voorbeeld, we gaan gewoon om de gegevens te laden zoals het is, dus ga je gang en klik op ‘Laden’.

de gegevens worden nu geëxporteerd uit de tabel in SQL Server en in een nieuw blad in Excel geladen.

dat is alles wat we moeten doen om een eenvoudige export van een volledige tabel van SQL Server naar Excel uit te voeren. Afgezien van de eenvoudige procedure die we hier hebben beschreven, de macht Query data import wizards en tools bieden manieren om ‘vorm’ van de gegevens van externe bronnen. U kunt bijvoorbeeld een kolom verwijderen, een gegevenstype wijzigen, tabellen samenvoegen om aan uw specifieke behoeften te voldoen.

Methode (3): Exporteren met behulp van SQL Spreads Add-In voor Excel

Als u de SQL Spreads Add-in voor Excel nog niet hebt geïnstalleerd, kunt u hier een kopie downloaden.

zodra SQL Spreads is geïnstalleerd, zult u zien dat het is toegevoegd als een nieuw lint tabblad; ga hier en klik op de Design Mode knop.

klik in het Ontwerppaneel SQL Spreads aan de rechterkant op de knop Bewerken om het dialoogvenster SQL Server-verbinding te openen.

Voer de naam van uw SQL Server in het veld SQL Server name in. U moet ook een verificatiemethode kiezen: Windows-login (Windows-verificatie) of SQL Server-verificatie (met behulp van een naam en wachtwoord ingesteld in SQL Server). Windows-authenticatie is de veiligste van de twee opties.

klik op OK. SQL Spreads zal proberen om verbinding te maken met de database. Als de verbinding succesvol is, zullen uw databases worden weergegeven in het sql Spreads Designer panel.

we kunnen nu de demo-database uitbreiden en de verkooptabel selecteren.

wanneer u op de tabel klikt, worden de gegevens onmiddellijk geëxporteerd vanuit SQL Server en gekopieerd naar een blad met de naam ‘sales’ in Excel.

dat is het! We zijn erin geslaagd om gegevens te exporteren van SQL Server naar Excel met slechts een paar klikken.

er zijn verschillende opties beschikbaar voor ons in de SQL Spreads Designer. We kunnen bijvoorbeeld:

• filter de gegevens die worden geëxporteerd vanuit SQL Server
• stel in of bepaalde kolommen worden weergegeven en/of alleen-lezen
• gebruik een opzoekwaarde om tekst weer te geven in plaats van een id voor een kolom
• Sorteer gegevens
• maak updates in Excel en sla wijzigingen op in SQL Server

we zullen in de toekomst enkele van deze opties bekijken blog post.

samenvatting

de mogelijkheid om gegevens van SQL Server naar Excel te exporteren is een veel voorkomende vereiste in de meeste bedrijven. We hebben gekeken naar drie methoden voor het uitvoeren van deze dagelijkse taak. De eerste maakt gebruik van de Wizard Importeren en exporteren van SQL Server, een standaardfunctionaliteit in SQL Server. De tweede methode gebruikt de native ‘Get & Transform’ tools in Excel. De derde maakt gebruik van de SQL Spreads Add-In voor Excel.

dus welke methode moet u gebruiken?

als u normaal met databases werkt en SSMS uw tweede thuis is, zult u waarschijnlijk het gevoel hebben dat de Wizard Importeren en exporteren van SQL Server de beste optie voor u is. Als u een Excel-gebruiker bent, is het zinvol om vanuit Excel zelf te werken om gegevens van SQL Server te exporteren. Beide van de twee Excel-opties die we hebben gekeken naar zijn robuust en makkelijk te gebruiken, maar SQL Spreads heeft een duidelijk voordeel, omdat het gebruikers in staat stelt om updates te maken van gegevens rechtstreeks in Excel en vervolgens de gegevens terug te slaan naar SQL Server. Dit is een enorm voordeel voor die organisaties die van het beste van twee werelden houden: het gebruiksgemak en de vertrouwdheid van Excel, met de kracht en data-integriteit geassocieerd met SQL Server.

probeer SQL Spreads om te zien hoe snel en eenvoudig het is om gegevens van SQL Server naar Excel te exporteren; download vandaag nog een gratis proefversie.