Connect Excel to SQL Server: 3 eenvoudige stappen

in een vorig artikel spraken we over waarom u zowel Excel als SQL nodig hebt in uw oplossingen voor gegevensbeheer, maar hoe verbindt u Excel met SQL Server? In dit artikel laten we u de stappen zien en markeren we een belangrijk voordeel van het gebruik van een Excel-invoegtoepassing zoals SQL-Spreads.

maar eerst gaan we de voordelen van het verbinden van Excel met gegevens in SQL Server opnieuw benutten.

voordelen van het verbinden van Excel met SQL Server

om de voordelen van het verbinden van Excel met SQL Server aan te tonen, laten we een voorbeeld bekijken.

deze spreadsheet wordt gebruikt door verkopers bij een productiebedrijf om offertes voor klanten op te stellen. Elke vertegenwoordiger heeft een kopie van de spreadsheet, die een lijst van de inventaris items en hun prijzen omvat. De lijst van inventaris items en prijzen wordt uit het financiële systeem gehaald door een beheerder. De offerte sheet maakt gebruik van een aantal data validation lookup formules om de prijzen op te halen wanneer een vertegenwoordiger selecteert een item uit de dropdown.

dit werkt goed totdat de organisatie de prijzen moet bijwerken. In dat geval moet elke gebruiker de nieuwe prijs in de spreadsheet kopiëren en plakken (of een nieuwe kopie van de spreadsheet gebruiken). Ook, wanneer de vertegenwoordiger maakt de offerte, ze weten niet of de items daadwerkelijk op voorraad.

een betere oplossing zou zijn om de Excel-spreadsheet te verbinden met de inventarislijst in de database van het financiële systeem. Elke keer dat de vertegenwoordiger opent de offerte spreadsheet, ze zijn het vernieuwen van de prijs gegevens uit de live-database. We kunnen nog een stap verder gaan en de verbinding met de database ook de huidige voorraadniveaus terugbrengen zodat offertes kunnen worden voorbereid voor items die daadwerkelijk op voorraad zijn.

kortom, de voordelen van het verbinden van Excel met SQL Server zijn:

• we hebben toegang tot en kunnen actuele informatie bekijken
• we gebruiken één enkele, vertrouwde gegevensbron
• we kunnen on-The-fly analyse of aggregatie van de brongegevens uitvoeren met behulp van eenvoudige Excel-functies zoals draaitabellen

Excel verbinden met een SQL Server-database : 3 eenvoudige stappen

de makkelijkste manier om Excel te verbinden met een SQL Server-database is met SQL spreads. SQL Spreads is een eenvoudige Excel-invoegtoepassing.

de SQL Spreads download is hier te vinden, en het installatieproces wordt hier beschreven.

Stap 1: Verzamel de databaseverbindingsdetails

u hebt de volgende informatie nodig om Excel te verbinden met SQL Server, dus haal de details als eerste stap:

• SQL-servernaam
• databasenaam
• tabel of weergave die de informatie bevat die u nodig hebt
• aanmeldgegevens (als u geen Windows-authenticatie gebruikt)

de vorm van de SQL Server-naam is afhankelijk van de vraag of SQL Server lokaal, op een netwerk of op een externe locatie is geïnstalleerd (let op: dit is dezelfde naam als wanneer u verbinding maakt met SQL Server in SQL Server Management Studio).

Typisch server naam	Zaak
Localhost	Wanneer SQL Server lokaal is geïnstalleerd
localhost\sqlexpress	Wanneer SQL Server Express is geïnstalleerd lokaal
Domein\Servernaam	Wanneer SQL Server is geïnstalleerd in een bedrijfsnetwerk
IP-adres	Wanneer SQL Server is geïnstalleerd op een externe locatie

Stap 2: Maak verbinding met SQL Server

zodra SQL Spreads is geà nstalleerd, ziet u het als een nieuw lint tabblad; ga hier en klik op de Design Mode knop.

klik in het paneel SQL Spreads Designer 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-naam in:

Selecteer of u verbinding moet maken met uw Windows-login (Windows-verificatie) of voer een gebruikersnaam en wachtwoord in (SQL Server-verificatie). Windows authenticatie is de veiligste van de twee opties (zie hier voor meer informatie).

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.

Stap 3: Selecteer de relevante tabel in SQL Server

nu we de verbinding van Excel naar SQL Server hebben gemaakt, kunnen we selecteren welke tabel met gegevens we in Excel willen gebruiken.

in de SQL Spreads Designer, klik op de database en selecteer vervolgens uw tabel.

zodra u een tabel selecteert, worden de gegevens in de tabel ingevuld in het Excel-blad.

u kunt nu alle gegevens in uw SQL Server-tabel zien en deze gebruiken in uw Excel-werkmap. De echte kracht met SQL Spreads is de mogelijkheid om de gegevens in de tabel in SQL Server rechtstreeks vanuit Excel bij te werken. Ik kan bijvoorbeeld de prijzen in de producttabel rechtstreeks in Excel bijwerken en de wijzigingen opslaan naar SQL Server!

voorbeeld: een eenvoudig Aanhalingsformulier in Excel verbonden met SQL Server

laten we teruggaan naar ons eerdere voorbeeld van het aanhalingsformulier en de inventarislijst. We gaan dit veranderen zodat het aanhalingsformulier zijn gegevens krijgt van de tabel in de SQL server in plaats van een statische lijst in de spreadsheet.

Als u dit voorbeeld wilt volgen, moet u toegang hebben tot de AdventureWorks-voorbeelddatabase, die u vanaf hier kunt krijgen.

eerst maken we een nieuw blad aan in de quote_sample – werkmap en laten het achter met de standaardnaam van Sheet2-de gegevens van SQL Server worden hier ingevuld.

vervolgens gaan we verbinding maken met de AdventureWorks database met behulp van de stappen hierboven beschreven.

Stap (1) : Verbindingsdetails

hier zijn de verbindingsdetails die ik ga gebruiken om verbinding te maken met mijn SQL Server-database:

• SQL-servernaam: Andy \ SQLExpress
• databasenaam: AdventureWorks
• tabel of Weergave: productie.Product
• Login details: Windows authentication

Stap (2): Verbinding maken met SQL Server

we willen verbinding maken met de producttabel in SQL Server en het ‘Product’ – blad in het aanhalingswerkblad invullen. Om dit te doen klikken we op SQL Spreads > Design Mode en voeren de verbindingsdetails in en breiden de juiste database uit (in ons geval AdventureWorks) zodat we de relevante tabel kunnen selecteren (in ons geval productie.Product).

Stap (3): Selecteer de productie.Producttabel in SQL Server

klik op de productie.Producttabel in de lijst en de gegevens worden automatisch ingevuld in het productblad.

we kunnen deze gegevens nu gebruiken als bron voor onze lookups in het citaat sjabloon, maar voordat we dat doen, gaan we kijken hoe we de gegevens kunnen filteren die worden geretourneerd van SQL zodat alleen in-stock items worden weergegeven.

klik op het tabblad Filters in de SQL Spreads Designer. Onder de filter type optie, we kunnen selecteren ‘kolom’ of ‘aangepaste SQL’. We gaan ‘Custom SQL’ selecteren omdat we een SQL-query moeten uitvoeren die alleen items in voorraad retourneert, en dat betekent dat we de producttabel moeten aansluiten bij de productinventory-tabel.

wanneer u op de optie ‘aangepaste SQL’ klikt, wordt een tekstvak weergegeven waar u de SQL-query kunt invoeren. Voor onze use case willen we een lijst met producten retourneren via een join query, we moeten een Sub Query gebruiken. Dus, we moeten het volgende plakken:

Name in (SELECT P.Name AS ProductFROM AdventureWorks.Production.Product AS PJOIN AdventureWorks.Production.ProductInventory AS PI ON P.ProductID = PI.ProductIDJOIN AdventureWorks.Production.Location AS L ON PI.LocationID = L.LocationIDwhere L.Name = 'Finished Goods Storage' AND PI.Quantity > 0GROUP BY P.Name, P.ListPrice)

in het vak Aangepaste SQL:

wanneer u op ‘Opslaan’ klikt, worden de gegevens in de producttabel gereduceerd tot de gefilterde records. Tot slot gaan we het benoemde bereik bijwerken dat werd gebruikt door de gegevensvalidatie-lookups in het Aanhalingsblad:

en, we moeten ook de xlookup functie die het citaat sheet gebruikt om de prijs te krijgen wanneer een gebruiker een item selecteert bijwerken-het moet verwijzen naar de nieuwe Table2 die de gegevens van SQL Server.

dat is het! Onze verkopers kunnen nu offertes maken op basis van actuele prijzen voor items die momenteel op voorraad zijn. Elke keer dat de vertegenwoordiger de spreadsheet opent, worden de gegevens vernieuwd van SQL Server.

Bonusfunctie! Prijzen in Excel bijwerken en opslaan in SQL Server

we hebben Excel verbonden met SQL Server, zodat onze verkopers offertes kunnen maken op basis van actuele prijzen en beschikbaarheid.

we kunnen een stap verder gaan en de mogelijkheid bieden voor een aangewezen gebruiker (bijvoorbeeld een beheerder) om de verkoopprijzen van de items direct in Excel bij te werken, en de updates terug te laten opslaan in de ‘productie.Product ‘ tabel in SQL Server.

bijvoorbeeld, als ik de prijs van het eerste item in de lijst wilde veranderen naar $39.99, ik kan typen in de listprice cel en klik op ‘Opslaan in Database’ – het is zo gemakkelijk! Natuurlijk, we zouden willen dat deze mogelijkheid vergrendeld voor bepaalde gebruikers alleen, en dit is gemakkelijk gedaan door SQL Spreads.

samenvatting

In dit artikel hebben we laten zien hoe eenvoudig het is om Excel te verbinden met SQL Server met behulp van de SQL Spreads add-in. We hebben ook een voorbeeld gehad om te laten zien hoe een eenvoudige quotes-spreadsheet up-to-date verkoopprijsgegevens uit een SQL-database kan gebruiken, en hoe de prijzen in de database ook rechtstreeks vanuit Excel kunnen worden bijgewerkt.

Download de proefversie van SQL Spreads en verbind Excel vandaag nog met SQL Server.