[ad_1]
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:
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.
- Open de pagina External connections en kopieer de velden External hostname, Username, Password en Database name.
De pagina External connections toont de velden die nodig zijn om verbinding te maken met een externe host. 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.
- Op de pagina Welcome to MySQL Workbench klik je linksonder op MySQL Connection.
- Op de pagina Setup New Connection voer je de externe verbindingsgegevens in die door je MariaDB database-instance worden verstrekt.
De pagina Setup New Connection toont de details van de externe verbinding. - 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.
- 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
- Open Google Sheets. De spreadsheet bevat al een door komma’s gescheiden waarden (CSV) bestand (diabetes.csv) met zeven kolommen.
Google Sheets toont het diabetes.csv bestand. - Klik op Extensions.
De menubalk van Google Sheets. - Ga naar Coefficient Salesforce, Hubspot Data Connector en klik op Launch.
Het menu Extensies. 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.
- 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.
- Voer de verbindingsgegevens in die je van je MariaDB-instance krijgt en klik op Connect.
Coefficient toont de gegevens die nodig zijn om verbinding te maken met MariaDB. - Selecteer in het gedeelte Source Data diabetes in de Tab lijst en Row 1 in de lijst Header row.
De sectie Source Data toont de velden Tab en Header. - Selecteer in de sectie Destination Sheets-db diabetes_table in de lijst Table.
- Selecteer Insert in de lijst Action om de spreadsheetgegevens in te voegen.
De sectie Destination toont de lijsten Table en Action. In het paneel Schemas zie je de kolommen van de spreadsheet.
Het Schemas paneel met de spreadsheetkolommen. - Koppel de spreadsheetkolommen aan de koppen van de MariaDB tabel en klik op Save.
Het paneel Field Mappings met de kolommen die zijn toegewezen aan de koppen van de MariaDB tabel. - Selecteer Specific rows on the sheetrijen op het sheet en klik op Volgende.
- Test de mapping door rij 12 te selecteren en klik op Done selecting rows.
De Google Sheets tabel toont de selectie van rij 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.
De geselecteerde rij is succesvol geëxporteerd met wat tijdstempelinformatie. - Klik op Done.
- Selecteer nu meer rijen om te exporteren. Klik op Insert X rows in MySQL en vervolgens op Done.
- Gebruik deze query om geïmporteerde gegevens in de MariaDB tabel te tonen.
SELECT * FROM <your_db_name>.diabetes_table;
MariaDB toont de geïmporteerde gegevens.
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.
- Open een leeg Excel sheet.
- Klik op Devart in de bovenste navigatiebalk. Je ziet de Devart tab als je de plugin hebt geïnstalleerd.
Excelsheet met het tabsheet Devart. - Klik op Get Data om de wizard Import Data te openen.
Tabsheet Devart met links de knop Get Data. - Selecteer MySQL database als Data Source en voer de gegevens van je MariaDB database in om er verbinding mee te maken.
Wizard Gegevens importeren toont de velden die nodig zijn om verbinding te maken met MariaDB. - Klik op Test Connection. Er verschijnt een bericht “Successfully connected”.
- Klik op OK en vervolgens op Next.
- Gebruik de Visual Query Builder of een aangepaste SQL query om alle gegevens uit de diabetestabel te importeren naar de Excel sheet.
Import Data Wizard toont een aangepaste SQL query om gegevens in de Excel sheet te importeren. - Klik op Finish. Je hebt nu een Excel sheet met gegevens uit de cloud gehoste database.
Excelsheet met gegevens uit de cloud-database. - Om deze sheet en de database te bewerken en bij te werken, klik je op Edit mode.
Excel sheet shows the Edit Mode button in the Edit Session group on the Devart tab 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.
- Test de verbinding opnieuw om er zeker van te zijn dat je nog steeds verbonden bent na het invoeren van je wachtwoord.
- Selecteer twee nieuwe records om aan de database toe te voegen.
Excel sheet shows two new records highlighted in yellow - Klik op Commit en vervolgens op OK om deze wijzigingen toe te passen en de wijzigingen vast te leggen in de MariaDB database.
- Voer een query uit om de bijgewerkte database te bekijken. Je hebt nu twee nieuwe records.
MariaDB toont 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
- Geef in het dialoogvenster Register – Server je PostgreSQL verbindingsgegevens op. De details omvatten:
- Hostnaam/adres
- Poort
- Onderhoudsdatabase
- Gebruikersnaam
- Wachtwoord
PostgreSQL verbindingsgegevens. - 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; - 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; - Open diabetes.csv in Google Sheets.
- Klik op Extensies, ga naar Coefficient: Salesforce, Hubspot Data Connector en klik op Starten.
- Klik vervolgens op Export to om de spreadsheetdata te exporteren naar de PostgreSQL database.
- Klik op Connect naast PostgreSQL.
- Voer je PostgreSQL verbindingsgegevens in en klik op Connect.
Coefficient toont de velden die nodig zijn om verbinding te maken met PostgreSQL. - Definieer hoe je je gegevens wilt exporteren door diabetes te selecteren in de lijst Tab en Row 1 in de lijst Header row.
Het gedeelte Source Data toont de Tab en Header row. - Selecteer public.diabetes_table in de lijst Table in de sectie Destination.
- Selecteer Insert in de lijst Action.
De sectie Destination toont de lijsten Table en Action. - Koppel de kolommen van de sheet aan je PostgreSQL tabel.
- Selecteer de tweede rij en klik op Done selecting rows.
- 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.
- Test je integratie door meer rijen te exporteren.
- 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.
- Open een leeg Excelsheet en klik op Devart.
- Klik op Get Data om de Import Data Wizard te openen.
- 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.
De wizard Import Data toont de velden die nodig zijn om verbinding te maken met MariaDB. - Klik op Test Connection om te controleren of de verbinding goed is.
- 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.
Visual Query Builder toont de lijsten met Objects en Filters. - 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.
Klik op de knop Refresh in de groep Import op het tabsheet Devart. - Klik op Yes om te bevestigen.
- Klik vervolgens op Edit Mode om dit werksheet en de database te bewerken en bij te werken.
- Voeg een nieuwe record toe aan het werksheet en klik op Commit om de wijziging vast te leggen.
De knoppen Edit Mode en Commit in de groep Edit Session op het tabsheet Devart. - 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!
Kinsta
Content & Marketing Editor at Kinsta, WordPress Web Developer, and Content Writer. Outside of all things WordPress, I enjoy the beach, golf, and movies. I also have tall people problems ;).
[ad_2]
https://kinsta.com/nl/blog/cloud-spreadsheet-integratie/
