Invantive Control voor Excel maakt het mogelijk om gegevens te downloaden van meer dan 80 (cloud)platforms, maar maakt het ook mogelijk om real-time gegevensonderhoud te doen vanuit Excel. Invantive Control heeft veel functionaliteit om ervoor te zorgen dat het data onderhoud voldoet aan standaarden voor informatiebeveiliging en -kwaliteit. In dit artikel wordt een voorbeeld gegeven hoe u een SQL Server database tabel kunt bijwerken met behulp van de Excel frontend.
Introductie
In de huidige datagestuurde wereld is de mogelijkheid om SQL Server-data te kunnen verwerken van het groot belang voor financiële professionals met een dataspecialisatie. Excel, met zijn vertrouwde interface en krachtige mogelijkheden, komt naar voren als een aantrekkelijke gebruikersinterface. In deze handleiding wordt getoond hoe Invantive Control voor Excel u in staat stelt om SQL Server data moeiteloos te bij te werken en op te vragen, waarbij de veiligheid en kwaliteit van de data wordt gewaarborgd.
Vergeleken met traditionele Windows Forms-applicaties of websites, biedt het gebruik van Excel als gebruikersinterface duidelijke voordelen. Eerst en vooral biedt Excel een vertrouwde omgeving voor financiële professionals, waardoor de leercurve korter wordt en de productiviteit toeneemt. Gegevens kunnen snel worden ingevoerd, bewerkt en geanalyseerd zonder dat uitgebreide codering of ontwikkeling nodig is. Bovendien stelt de veelzijdigheid van Excel gebruikers in staat om aangepaste rapportages en dashboards te maken die zijn afgestemd op hun specifieke behoeften, waardoor gegevensgestuurde besluitvorming wordt bevorderd. Bovendien zorgen de offline mogelijkheden van Excel ervoor dat gegevens zelfs toegankelijk zijn in situaties met beperkte of geen internetverbinding. Deze combinatie van vertrouwdheid, flexibiliteit en offline toegang maakt Excel een krachtige keuze voor interactie met SQL Server gegevens, vooral in financiële functies.
Dit artikel gebruikt Invantive Control voor Excel als hulpmiddel voor real-time data-integratie tussen Excel en SQL Server. Als alternatief kun je ook de exceltable functionaliteit van Invantive’s UniversalSQL gebruiken. Volg deze link voor een voorbeeld: Topics met de tag exceltable. Tenslotte kunt u voor het uitlezen van SQL Server ook gebruik maken van Power Query (zie SQL Server Power Query-connector, 180 dagen gratis).
Voorbereidingen
Invantive Control voor Excel installeren
Installeer Invantive Control voor Excel van https://download.invantive.com. De gebundelde licentiesleutel voor “Probeer alle functies” kan 180 dagen gratis gebruikt worden zonder verdere verplichtingen.
Problemen oplossen
In het geval dat het Invantive Control-lint ooit verdwijnt, kunt u het opnieuw inschakelen zoals beschreven in Excel toont Invantive Control niet meer in het lint.
Database definitie
De eerste stap is het registreren van een SQL Server-database als datacontainer in een (virtuele) database. Kopieer en plak de volgende XML-code in een bestand met de naam settings-sample.xml in de map %USERPROFILE%\Invantive (zie Hoe vind ik %USERPROFILE%\invantive?):
<?xml version="1.0" encoding="UTF-8"?>
<settings
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
version="3"
forcedefault="false"
>
<group
name="Sample"
sortingOrder="0"
shortDescription="Sample change database"
>
<connection name="Sample" forceNative="true">
<database
order="0"
provider="SqlServer"
connectionString="Server=mydatabase.database.windows.net"
/>
</connection>
</group>
</settings>
In dit voorbeeld wordt een “native” verbinding met SQL Server gemaakt. Hierbij schakelt de SQL-engine om van Invantive’s UniversalSQL-syntax naar SQL Server-syntax. Daarnaast kan de database slechts één datacontainer bevatten; met een native verbinding is het dus niet mogelijk om een datacontainer zoals Odoo of Salesforce parallel aan SQL Server toe te voegen.
U kunt ook de Invantive UniversalSQL-engine gebruiken volgens precies dezelfde stappen, zoals in combinatie met Exact Online of Brevo.
Beveiligde verbindingen voor lokale SQL Server-instanties uitschakelen
In dit scenario wordt de database altijd aangesproken via een verbinding beveiligd via certificaten.
Lokale SQL Server-instanties hebben echter zelden een werkende set-up voor certificaten. Dit kan leiden tot een foutmelding zoals:
provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.
Als u verbinding maakt met een lokale SQL Server, verander dan de connectionString door ;encrypt=False toe te voegen zoals beschreven in Secure Data Connections (Engels).
Dit verandert de regel met de connectionstring in:
connectionString="Server=mydatabase.database.windows.net;Encrypt=false"
Tabellen
Kies een tabeleigenaar, bij voorkeur met authenticatie op basis van SQL Authentication in plaats van Windows Authentication.
Maak een tabel aan in SQL Server via SQL Server Management Studio of iets dergelijks. De tabel moet enkele rijen bevatten. Gebruik bijvoorbeeld de volgende SQL-statements:
create table data
( id uniqueidentifier default newid() primary key
, txt nvarchar(30)
)
insert into data (txt) values ( 'hello world')
insert into data (txt) values ( 'acme')
Model bouwen
Start nu Microsoft Excel, maak een lege werkmap en voer de onderstaande stappen uit.
Authenticatie
Na het plaatsen van het bestand in %USERPROFILE%Invantive, toont het verbindingsvenster van Invantive Control voor Excel de groep “Sample”.
Klik in het Invantive Control-lint op “Verbinden”:
Selecteer de Sample-database in de Sample-groep, voer de gebruikersgegevens in en kies “Verbinden”:
De verbinding wordt opgebouwd.
Repository Toevoegen
De onderstaande vraag wordt gesteld omdat de (lege) werkmap geen voorgedefinieerd repostiry bevat:
Kies “Ja”.
Het lint wijzigt nu om meer knoppen te bevatten:
Blok met gegevens definiëren
De volgende stap is het definiëren van een datablok ergens in de Excel-werkmap waar de gebruiker gegevens kan downloaden, wijzigen en uploaden.
Ga naar het Modelleur-lint:
Controleer of “Ontwerpmodus” is ingeschakeld. Wanneer de ontwerpmodus is ingeschakeld, is de knop “Bewerken” beschikbaar zoals in de bovenstaande afbeelding.
Kies de knop “Bewerken”.
Er verschijnt een venster:
Ga naar “Blokken” en kies “Toevoegen”:
Wijs optioneel een code, labels en benoemd bereiken toe. De benoemd bereiken worden meestal genoemd naar de code plus “_data” en “_border” respectievelijk voor het benoemd bereik met de gegevens als benoemd bereik voor de gegevens plus rand. De genoemde bereiken - wanneer ingesteld - worden automatisch beheerd zodat ze altijd de juiste grootte hebben en Excel-formules kunnen verwijzen naar het exacte datablok.
Controleer de toegangscontrole voor invoegen, bijwerken en verwijderen:
Laten we nu definiëren welke tabel moet worden onderhouden. Ga naar het tabblad “Feiten”:
Free-format SQL wordt meestal alleen gebruikt voor downloaddoeleinden en staat het onderhouden van data niet toe. Voor eenvoudig onderhoud en uploaden moet in plaats daarvan het bedrijfsobject worden opgegeven.
Kies de gewenste tabel, in dit geval smoke.dbo.data:
Voer dan de (unieke) primaire sleutelkolom in, in dit geval id:
Invantive Control for Excel maakt gebruik van [optimistic locking] (Optimistic concurrency control - Wikipedia) om meerdere gebruikers in staat te stellen rijen in dezelfde tabel gelijktijdig te wijzigen, zelfs wanneer de verbinding met het netwerk is verbroken. De waarde van de transactiekolom wordt gebruikt om te detecteren dat een rij is gewijzigd door een andere gebruiker tussen het downloaden en uploaden van gewijzigde data.
Als uw datamodel een kolom heeft die altijd van waarde verandert als een gebruiker de rij wijzigt: voer dan de naam van die kolom in als transactiekolom. Dit is meestal een numerieke (incrementele) teller of een datum-wijzigingsveld.
Als er geen transactiekolom is, gebruik dan gewoon de primaire sleutelkolom. Dit maakt het echter onmogelijk om gelijktijdige updates van dezelfde rij te detecteren in een omgeving met meerdere gebruikers.
Controleer dat de inhoud van de tabel kan worden opgehaald en overeenkomt met de verwachtingen door de knop “Feiten weergeven” te kiezen:
Presentatie van Datablock
De volgende stap is om aan te geven waar het blok in de Excel-werkmap moet komen te staan.
Ga naar het tabblad “Presentatie”:
Het blok wordt geplaatst op het eerste werkblad (reeds ingesteld), beginnend bij kolom 1 en rij 1 (cel A1).
Het blok krijgt een koprij met een hoogte van 1. Verander de hoogte van 0 in 1:
Voor dit voorbeeld wordt geen fancy opmaak toegepast op de gedownloade gegevensrijen.
Kies “Opslaan” en sluit de modeleditor.
Verlaat de “Ontwerpmodus” door de knop te selecteren.
Sla de werkmap op als sample.xlsx.
Initiële gegevensdownload
De volgende stap is het downloaden van de bestaande gegevensrijen uit de SQL Server database.
Ga naar het Invantive Control lint en kies “Sync”:
Na een paar seconden worden de gegevens weergegeven in het werkblad, beginnend bij rij 2:
Vergeet niet dat we ruimte hebben overgelaten voor een koprij in het model.
Voeg nu een aantal koppen toe, zoals ‘ID’ en ‘Tekst’, en pas de breedte van de kolommen aan:
Let op de rode driehoek in de rechterbovenhoek van de ID-kolom. Dit driehoekje is een Excel-commentaar dat aan de eerste kolom is gekoppeld. Het bevat de waarde van de ID- en transactiekolom van deze rij (deze hoeven niet opgenomen te zijn in het datablok). U kunt dit visueel bevestigen door met de muis over de rode driehoek te bewegen:
In dit geval zijn de waarden identiek omdat de primaire sleutel en transactiekolom beide id zijn.
Data Wijzigen
De volgende stap is het wijzigen en toevoegen van data die later moeten worden geüpload naar de Microsoft SQL Server-database.
Verander eerst “acme” in “Microsoft”.
Het winkelwagentje in het lint verandert plotseling en toont 1 te verwerken wijziging:
Volledige details zijn beschikbaar door op de knop te klikken:
Datarij toevoegen
Een rij toevoegen is ook eenvoudig. Plaats de focus op een rij in het blok. De knop “Rij toevoegen” wordt beschikbaar:
Kies de knop “Rij Toevoegen” en vul een waarde in voor “Text”.
Merk op dat de waarde van niet-verwerkte wijzigingen van 1 naar 2 gaat.
In het repository kunt u aangeven dat sommige kolommen gewijzigd kunnen worden en andere niet, evenals complexere validaties. In dit geval kan de ID gewijzigd worden, maar het is aan te raden om deze leeg te laten, omdat SQL Server deze standaard op een nieuwe ID zet bij het invoegen.
Wijzigingen uploaden
De laatste stap is het uploaden van de nieuwe rij en de gewijzigde inhoud van de SQL Server-tabel naar SQL Server, rechtstreeks vanuit Microsoft Excel.
Kies opnieuw de knop “Sync”:
Na enkele seconden is de synchronisatie voltooid. De nieuwe en gewijzigde rijen zijn geregistreerd in SQL Server en de nieuwe inhoud van SQL Server is gedownload naar Excel. Dit omvat ook wijzigingen door andere gelijktijdig actieve gebruikers.
De nieuwe inhoud van de tabel is ook zichtbaar in SQL Server Management Studio:
Problemen oplossen
Als u tegen problemen aanloopt, voeg dan hieronder een reactie toe of maak een nieuw onderwerp aan. Misschien heeft u ook baat bij de gratis persoonlijke opstartsessie van een uur die u online kunt reserveren.
Conclusie
In deze uitgebreide handleiding hebben we de naadloze integratie van Excel als gebruikersinterface voor het onderhouden van SQL Server-gegevens via Invantive Control voor Excel onderzocht. Voor financiële professionals met dataspecialisatie biedt deze aanpak een groot aantal voordelen. Het maakt gebruik van Excel’s vertrouwde omgeving, waardoor gebruikers efficiënt data kunnen beheren, manipuleren en analyseren zonder de complexiteit van traditionele applicaties. De mogelijkheid om aangepaste rapporten en dashboards te maken verbetert de datagestuurde besluitvorming, terwijl de offline mogelijkheden van Excel de toegankelijkheid in verschillende scenario’s garanderen. Door de kloof tussen SQL Server databases en Excel te overbruggen, vereenvoudigt Invantive Control voor Excel het databeheer en zorgt voor veiligheid en kwaliteit. Omarm deze krachtige tool om uw datamanagement capaciteiten te verhogen en uit te blinken in de financiële wereld.