Een stap-voor-stap handleiding om je spreadsheets te verbinden met een database in de cloud

Spreadsheets zijn waardevol voor het organiseren en beheren van klantgegevens, vooral voor kleinschalige bedrijven die werken met kleine datasets met weinig complexe relaties.

Naarmate je gegevens groeien en complexer worden, met meer gebruikers die er toegang toe moeten hebben, wordt het beheren van gegevens met spreadsheets erg inefficiënt. Ook is het observeren en bijhouden van wijzigingen in een spreadsheet ingewikkelder, wat vaak resulteert in meerdere versies van je gegevens.

Cloud-based databases maken beter gegevensbeheer mogelijk door een platform te bieden voor het benaderen, beheren en organiseren van je gegevens.

Dit artikel laat zien hoe je populaire spreadsheetprogramma’s, Microsoft Excel en Google Sheets, kunt verbinden met cloud-hosted databases MariaDB, MySQL en PostgreSQL om gegevens beter te beheren.

Vereisten

Om deze tutorial te kunnen volgen, moet je over het volgende beschikken:

• Een gevuld Google Sheet. We hebben een voorbeeldsheet meegeleverd dat je kunt gebruiken voor deze demonstratie.
• Coefficient geïnstalleerd op Google Sheet.
• Een Microsoft Excel werkmap met de Devart plugin geïnstalleerd
• pgAdmin4 en MySQL Workbench geïnstalleerd. Dit zijn grafische interfaces voor interactie met de database.

De essentie van spreadsheet- en database-integratie

Op Cloud gebaseerde databases bieden organisaties een database-as-a-service (DBaaS), waardoor ze databases kunnen hosten, implementeren en beheren zonder dat er tijd en middelen nodig zijn om hardware aan te schaffen, te configureren en te onderhouden.

Enkele van deze databases zijn:

• PostgreSQL – een robuuste open-source relationele database die bekend staat om zijn betrouwbaarheid, uitbreidbare functies en hoge prestaties. Het ondersteunt integratie met talloze tools en technologieën, waardoor je schaalbare applicaties kunt bouwen.
• MySQL – een populaire open-source relationele database die gebruikers schaalbaarheid, flexibiliteit en betrouwbaarheid biedt voor het bouwen van SQL en NoSQL toepassingen. Het biedt een goed presterende, beschikbare database voor bedrijfskritische toepassingen tegen een lage prijs.
• MariaDB-MariaDB is een andere open-source relationele database die grote of kleine hoeveelheden gegevens aankan, waardoor het een betrouwbare keuze is voor de meeste bedrijven. Hoewel het veel overeenkomsten heeft met MySQL, is het schaalbaarder en heeft het een snellere zoeksnelheid, waardoor het zeer geschikt is voor prestatie-kritische werklasten.

Cloud-gebaseerde databases zorgen voor ononderbroken bedrijfsactiviteiten door tal van functies, zoals automatische back-ups, versiebeheer en herstel na calamiteiten. Extra voordelen zijn:

• Schaalbaarheid
• Flexibiliteit
• Bedrijfsflexibiliteit
• Beveiliging
• Kostenbesparingen

Dankzij tools als Kinsta kun je PostgreSQL, MySQL en MariaDB instances in enkele minuten opzetten. Kinsta biedt toegang tot een in de cloud gehost databasesysteem, dat je kunt gebruiken om deze tutorial risicoloos en gratis te volgen.

Bereid je spreadsheetgegevens voor en organiseer ze

Verse spreadsheetgegevens kunnen fouten bevatten, zoals dubbele cijfers, ruis, outliers en andere fouten, die de gegevenskwaliteit verminderen en de integratie beïnvloeden.

1. Bereid je gegevens voor

Hier zijn enkele manieren om je gegevens te organiseren en voor te bereiden op database-integratie:

• Maak gebruik van templates – Google Sheets en Excel bevatten veel spreadsheettemplates om het opmaken en organiseren van je gegevens te versnellen. Hoewel het vinden van een template voor jouw bedrijfsdoeleinden vervelend of uitdagend kan aanvoelen, zet het gebruik ervan je op het juiste spoor.
• Formatteer je gegevens – Formatteren wijzigt je gegevens zodat je ze beter kunt visualiseren en begrijpen. Dit proces kan bestaan uit het opsplitsen van één complex sheet in meerdere sheets, het alfabetisch of numeriek sorteren van kolommen in oplopende of aflopende volgorde om de leesbaarheid te vergroten, of het veranderen van celkleuren om de belangrijkheid aan te geven.
• Schoon gegevens op – Het opschonen van gegevens verwijdert uitschieters, dubbele waarden of speciale tekens. Het kan ook gaan om het opsplitsen van een enkele tekstkolom in meerdere kolommen om parsingfouten tijdens de integratie te voorkomen of het gebruik van voorwaardelijke opmaak om foutieve gegevens te identificeren.
• Onnodige gegevens verbergen – Soms bevatten je gegevens informatie die op dit moment niet nuttig is, maar later waardevol kan zijn. Excel en Google Sheets bieden functies waarmee je deze onnodige gegevens kunt verbergen.

2. Structureer je gegevens voor integratie

Bij het voorbereiden van spreadsheets voor database-integratie zijn er enkele best practices:

• Leg metagegevens vast – Metagegevens geven essentiële details over je huidige gegevensstructuur en de herkomst ervan. Het vastleggen van je metagegevens helpt bij het nauwkeurig in kaart brengen van alle datapoints voor een succesvolle database-integratie.
• Geef null- en nulwaarden weer – Nullwaarden (een “null” in de tabel) verschillen van nulwaarden (een 0 in de tabel) en beïnvloeden de kwaliteit van je gegevens. Leg je nullwaarden nauwkeurig vast bij het voorbereiden van datasheets voor integratie, want de database kan ze interpreteren als nulwaarden, wat constraintfouten kan veroorzaken.
• Vermijd speciale tekens in veldnamen – Het introduceren van getallen, speciale tekens en andere Unicode tekens in je kolomnamen kan parsingfouten veroorzaken bij het importeren van gegevens uit spreadsheets. Best practices bij het benoemen van velden zijn het gebruik van hoofdletters (bijvoorbeeld studentName) of underscores om namen beschrijvender te maken.

Nu je gegevens gestructureerd zijn, ben je klaar om ze te integreren met een cloud database.

Integreren met MariaDB: Een stap voor stap proces

Begin eerst met het maken van je MariaDB database met Kinsta. Vervolgens gebruikt deze Coefficient gids – een no-code connector voor het importeren van spreadsheetgegevens, om je database-instance te verbinden met Google Sheets. Zorg ervoor dat je deze connector installeert.

MySQL workbench verbinden met MariaDB

Geef eerst je MariaDB database-instance externe verbindingsgegevens.

1. Open de pagina External connections en kopieer de velden External hostname, Username, Password en Database name.

   

   Maak hier verbinding met MySQL Workbench, dat een grafische gebruikersinterface biedt voor interactie met de MariaDB instance. Je verbindt MySQL Workbench met je database-instance door een nieuwe verbinding toe te voegen.

2. Op de pagina Welcome to MySQL Workbench klik je linksonder op MySQL Connection.
3. Op de pagina Setup New Connection voer je de externe verbindingsgegevens in die door je MariaDB database-instance worden verstrekt.

   

4. Klik op Test connection onderaan de pagina. Er verschijnt een verbindingswaarschuwing over een incompatibele of niet-standaard serverversie. Negeer de waarschuwing. Je hebt nu je database-instance verbonden met MySQL Workbench.
5. Maak vervolgens een tabel met de naam diabetes_table met kolommen met behulp van de volgende SQL instructie.

   CREATE TABLE `diabetes_table` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     `Pregnancies` varchar(45) NOT NULL,
     `Glucose` int(11) NOT NULL,
     `BloodPressure` int(11) NOT NULL,
     `BMI` decimal(3,1) NOT NULL,
     `DiabetesPedigreeFunction` decimal(4,3) NOT NULL,
     `Age` int(11) NOT NULL,
     `Outcome` tinyint(4) NOT NULL,
     PRIMARY KEY (`id`),
     UNIQUE KEY `id_UNIQUE` (`id`)
   ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb3

Google Sheets verbinden met MariaDB

1. Open Google Sheets. De spreadsheet bevat al een door komma’s gescheiden waarden (CSV) bestand (diabetes.csv) met zeven kolommen.

   

2. Klik op Extensions.

   

3. Ga naar Coefficient Salesforce, Hubspot Data Connector en klik op Launch.

   

   Deze stap opent de Coefficient-connector aan de rechterkant van je sheet, waarmee je gegevens kunt importeren en exporteren tussen Google Sheets en de MariaDB-database.

4. Klik in Coefficient op Export to en vervolgens op MySQL. Hoewel je verbinding maakt met een MariaDB database, klik je op MySQL omdat MariaDB een fork is van MySQL. Dit betekent dat het een MySQL database is met extra mogelijkheden.
5. Voer de verbindingsgegevens in die je van je MariaDB-instance krijgt en klik op Connect.

   

6. Selecteer in het gedeelte Source Data diabetes in de Tab lijst en Row 1 in de lijst Header row.

   

7. Selecteer in de sectie Destination Sheets-db diabetes_table in de lijst Table.
8. Selecteer Insert in de lijst Action om de spreadsheetgegevens in te voegen.

   

   In het paneel Schemas zie je de kolommen van de spreadsheet.

   

9. Koppel de spreadsheetkolommen aan de koppen van de MariaDB tabel en klik op Save.

   

10. Selecteer Specific rows on the sheetrijen op het sheet en klik op Volgende.
11. Test de mapping door rij 12 te selecteren en klik op Done selecting rows.

    

12. Bevestig je selectie door te klikken op Insert 1 row in MySQL. De spreadsheet heeft nu een Record ID kolom, een Result kolom met OK en een Timestamp kolom met de tijd van de export.

    

13. Klik op Done.
14. Selecteer nu meer rijen om te exporteren. Klik op Insert X rows in MySQL en vervolgens op Done.
15. Gebruik deze query om geïmporteerde gegevens in de MariaDB tabel te tonen.

    SELECT * FROM <your_db_name>.diabetes_table;

    

Excel sheets verbinden met MariaDB

Zorg ervoor dat je de Devart plugin hebt. Met deze plugin kun je je Excel sheet verbinden met MariaDB, gegevens importeren en bewerken in Excel en de wijzigingen bijwerken in je database. De plugin wordt geleverd met een gids om te helpen bij de installatie.

1. Open een leeg Excel sheet.
2. Klik op Devart in de bovenste navigatiebalk. Je ziet de Devart tab als je de plugin hebt geïnstalleerd.

   

3. Klik op Get Data om de wizard Import Data te openen.

   

4. Selecteer MySQL database als Data Source en voer de gegevens van je MariaDB database in om er verbinding mee te maken.

   

5. Klik op Test Connection. Er verschijnt een bericht “Successfully connected”.
6. Klik op OK en vervolgens op Next.
7. Gebruik de Visual Query Builder of een aangepaste SQL query om alle gegevens uit de diabetestabel te importeren naar de Excel sheet.

   

8. Klik op Finish. Je hebt nu een Excel sheet met gegevens uit de cloud gehoste database.

   

9. Om deze sheet en de database te bewerken en bij te werken, klik je op Edit mode.

   

   Als je ervoor kiest om het wachtwoord niet op te slaan bij het opzetten van de verbinding, wordt je gevraagd om je databasewachtwoord in te voeren.

10. Test de verbinding opnieuw om er zeker van te zijn dat je nog steeds verbonden bent na het invoeren van je wachtwoord.
11. Selecteer twee nieuwe records om aan de database toe te voegen.

    

12. Klik op Commit en vervolgens op OK om deze wijzigingen toe te passen en de wijzigingen vast te leggen in de MariaDB database.
13. Voer een query uit om de bijgewerkte database te bekijken. Je hebt nu twee nieuwe records.

    

Verbinding maken met PostgreSQL

Voordat je verbinding maakt en gegevens importeert van Google Sheets naar je PostgreSQL database, moet je een betrouwbare verbinding tot stand brengen om een naadloos importproces van gegevens te garanderen.

Maak een PostgreSQL database aan op Kinsta en gebruik de verbindingsgegevens om verbinding te maken met pgAdmin4, een grafische gebruikersinterface (GUI).

Verbind, net als in het vorige hoofdstuk, je database-instance met Google Sheets met behulp van Coefficient.

Google en Excel data verbinden en importeren in PostgreSQL

1. Geef in het dialoogvenster Register – Server je PostgreSQL verbindingsgegevens op. De details omvatten:
   • Hostnaam/adres
   • Poort
   • Onderhoudsdatabase
   • Gebruikersnaam
   • Wachtwoord

   

2. Maak een reeks voor de ID waarden van je tabel met behulp van de SQL instructie hieronder:

   CREATE SEQUENCE IF NOT EXISTS public.diabetes_table_id_seq
       INCREMENT 1
       START 1
       MINVALUE 1
       MAXVALUE 2147483647
       CACHE 1
       OWNED BY diabetes_table.id;

3. Maak nu een PostgreSQL tabel met de naam diabetes_table met kolommen waarvan het datatype en de beperkingen overeenkomen met de spreadsheet tabel.

   CREATE TABLE IF NOT EXISTS public.diabetes_table
   (
       "Pregnancies" smallint NOT NULL,
       "BloodPressure" smallint NOT NULL,
       "BMI" numeric(3,1) NOT NULL,
       "Glucose" smallint NOT NULL,
       "DiabetesPedigree" numeric(4,3) NOT NULL,
       "Age" smallint NOT NULL,
       "Outcome" boolean,
       id integer NOT NULL DEFAULT nextval('diabetes_table_id_seq'::regclass),
       CONSTRAINT diabetes_table_pkey PRIMARY KEY (id)
   )
   WITH (
       OIDS = FALSE
   )
   TABLESPACE pg_default;

4. Open diabetes.csv in Google Sheets.
5. Klik op Extensies, ga naar Coefficient: Salesforce, Hubspot Data Connector en klik op Starten.
6. Klik vervolgens op Export to om de spreadsheetdata te exporteren naar de PostgreSQL database.
7. Klik op Connect naast PostgreSQL.
8. Voer je PostgreSQL verbindingsgegevens in en klik op Connect.

   

9. Definieer hoe je je gegevens wilt exporteren door diabetes te selecteren in de lijst Tab en Row 1 in de lijst Header row.

   

10. Selecteer public.diabetes_table in de lijst Table in de sectie Destination.
11. Selecteer Insert in de lijst Action.

    

12. Koppel de kolommen van de sheet aan je PostgreSQL tabel.
13. Selecteer de tweede rij en klik op Done selecting rows.
14. Bevestig je selectie door te klikken op Insert 1 row in PostgreSQL. De spreadsheet heeft nu een Record ID kolom, een Result kolom met OK en een Timestamp kolom met het tijdstip van de export.
15. Test je integratie door meer rijen te exporteren.
16. Voer een query uit om de recent geïmporteerde gegevens te bekijken.

    SELECT * FROM diabetes_table;

    Deze query toont alle gegevens in de diabetestabel.

Postgres gegevens verbinden en exporteren naar Excel

Eerst heb je je PostgreSQL verbindingsgegevens nodig.

1. Open een leeg Excelsheet en klik op Devart.
2. Klik op Get Data om de Import Data Wizard te openen.
3. Selecteer PostgreSQL database in de lijst met gegevensbronnen en voer in de Import Data Wizard de verbindingsgegevens in om verbinding te maken met je database.

   

4. Klik op Test Connection om te controleren of de verbinding goed is.
5. Selecteer je object en bevraag je database met de visuele query. Je kunt de Visual Query Builder gebruiken of je eigen aangepaste SQL query schrijven om je database te bevragen.

   

6. Klik op Finish. Je hebt nu een Excel werksheet met gegevens. Klik op Renew om ervoor te zorgen dat je werksheet up-to-date is.

   

7. Klik op Yes om te bevestigen.
8. Klik vervolgens op Edit Mode om dit werksheet en de database te bewerken en bij te werken.
9. Voeg een nieuwe record toe aan het werksheet en klik op Commit om de wijziging vast te leggen.

   

10. Voer nu een query uit om de bijgewerkte database te bekijken. Je kunt zien dat de database een nieuw record heeft.

Samenvatting

Cloud-gebaseerde databases bieden een werkruimte voor samenwerking waarmee je dynamische relaties met gegevens kunt opslaan, openen, leggen en beheren.

Met Kinsta kun je PostgreSQL en MySQL database-instances opstarten en de meegeleverde verbindingsgegevens gebruiken om verbinding te maken met je spreadsheets. Met deze verbinding kun je je databasetabellen aanmaken, de velden van je spreadsheets koppelen aan die van je clouddatabase en beginnen met het exporteren van je gegevens.

Ga aan de slag met Kinsta om te profiteren van het betere beheer dat cloud-hosted databases bieden.

Beheer jij nog steeds grote hoeveelheden gegevens met spreadsheets? Deel in de reacties hieronder hoe jij jouw big data effectief beheert!

Jeremy Holcombe Kinsta

Content & Marketing Editor bij Kinsta, WordPress Web Developer en Content Writer. Buiten alles wat met WordPress te maken heeft, geniet ik van het strand, golf en films. En verder heb ik last van alle problemen waar andere lange mensen ook tegenaan lopen ;).

• LinkedIn