Un guide pas à pas pour connecter vos feuilles de calcul à une base de données dans le cloud

Les feuilles de calcul sont très utiles pour organiser et gérer les données des clients, en particulier pour les petites entreprises qui gèrent de petits ensembles de données avec peu de relations complexes.

Au fur et à mesure que vos données s’accroissent et deviennent plus complexes, et qu’un plus grand nombre d’utilisateurs doivent y accéder, la gestion des données à l’aide de feuilles de calcul devient très inefficace. En outre, l’observation et le suivi des modifications apportées à une feuille de calcul sont plus compliqués, ce qui se traduit souvent par de multiples versions de vos données.

Les bases de données hébergées dans le cloud permettent une meilleure gestion des données en offrant une plateforme pour l’accès, la gestion et l’organisation de vos données.

Cet article montre comment connecter les outils de feuilles de calcul les plus courants, Microsoft Excel et Google Sheets, aux bases de données hébergées dans le cloud MariaDB, MySQL et PostgreSQL afin d’améliorer la gestion des données.

Pré-requis

Pour suivre ce tutoriel, assurez-vous de disposer des éléments suivants :

• Une feuille Google remplie. Nous vous avons fourni une feuille d’exemple à utiliser pour cette démonstration.
• Coefficient installé sur Google Sheet.
• Un classeur Microsoft Excel avec l’extension Devart installée
• pgAdmin4 et MySQL Workbench installés. Il s’agit d’interfaces graphiques permettant d’interagir avec la base de données.

L’essentiel de l’intégration des feuilles de calcul et des bases de données

Les bases de données hébergées dans le cloud offrent aux organisations une base de données en tant que service (DBaaS), leur permettant d’héberger, de déployer et de gérer des bases de données tout en éliminant le temps et les ressources nécessaires à l’achat, à la configuration et à la maintenance du matériel.

Voici quelques-unes de ces bases de données :

• PostgreSQL – une base de données relationnelle robuste open source, connue pour sa fiabilité, ses fonctions extensibles et ses performances élevées. Elle s’intègre à de nombreux outils et technologies, ce qui vous permet de créer des applications évolutives.
• MySQL – une base de données relationnelle open source populaire, offrant aux utilisateurs évolutivité, flexibilité et fiabilité pour la création d’applications SQL et NoSQL. Il s’agit d’une base de données performante et disponible qui permet d’alimenter des applications critiques à un cout économique.
• MariaDB – MariaDB est une autre base de données relationnelle open source qui peut gérer de grandes ou de petites quantités de données, ce qui en fait un choix fiable pour la plupart des entreprises. Bien qu’elle présente de nombreuses similitudes avec MySQL, elle est plus évolutive et sa vitesse d’interrogation est plus rapide, ce qui la rend bien adaptée aux charges de travail critiques en termes de performances.

Les bases de données hébergées dans le cloud garantissent des opérations commerciales ininterrompues grâce à de nombreuses fonctionnalités, telles que les sauvegardes automatiques, le contrôle des versions et la reprise après sinistre. Les autres avantages sont les suivants :

• Évolutivité
• Flexibilité
• Agilité de l’entreprise
• Sécurité
• Réduction des couts

Grâce à des outils comme Kinsta, vous pouvez mettre en place des instances PostgreSQL, MySQL et MariaDB en quelques minutes. Kinsta offre un accès à un système de base de données hébergé dans le cloud, que vous pouvez utiliser pour suivre ce tutoriel sans risque et sans frais.

Préparez et organisez les données de votre feuille de calcul

Les données fraiches des feuilles de calcul peuvent contenir des erreurs, telles que des chiffres en double, du bruit, des valeurs aberrantes et d’autres défauts, qui diminuent la qualité des données et affectent l’intégration.

1. Préparez vos données

Voici quelques façons d’organiser et de préparer vos données pour l’intégration de bases de données :

• Utilisez des modèles – Google Sheets et Excel contiennent de nombreux modèles de feuilles de calcul permettant d’accélérer le formatage et l’organisation des données. Bien qu’il puisse sembler fastidieux ou difficile de trouver un modèle qui réponde aux besoins de votre entreprise, l’utilisation d’un modèle vous met sur la bonne voie.
• Formatez vos données – Le formatage modifie vos données pour vous aider à les visualiser et à les comprendre. Ce processus peut consister à diviser une feuille complexe en plusieurs feuilles, à trier les colonnes par ordre alphabétique ou numérique, croissant ou décroissant, pour faciliter la lecture, ou à changer la couleur des cellules pour indiquer leur importance.
• Nettoyez les données – Le nettoyage des données permet d’éliminer les valeurs aberrantes, les valeurs dupliquées ou les caractères spéciaux. Il peut également s’agir de diviser une colonne de texte unique en plusieurs colonnes afin d’éviter les erreurs d’analyse lors de l’intégration ou d’utiliser le formatage conditionnel pour identifier les données erronées.
• Masquez les données inutiles – Parfois, vos données peuvent contenir des informations qui ne sont pas utiles pour l’instant, mais qui pourraient l’être plus tard. Excel et Google Sheets proposent des fonctions qui vous aident à masquer ces données inutiles.

2. Structurez vos données pour l’intégration

Lorsque vous préparez des feuilles de calcul pour l’intégration d’une base de données, voici quelques bonnes pratiques :

• Enregistrez les métadonnées – Les métadonnées fournissent des détails essentiels sur la structure actuelle de vos données et leur origine. L’enregistrement de vos métadonnées permet d’assurer un mappage précis de tous les points de données en vue d’une intégration réussie de la base de données.
• Représentez les valeurs nulles et zéro – Les valeurs zéro diffèrent des valeurs nulles et affectent la qualité de vos données. Enregistrez avec précision vos valeurs nulles lorsque vous préparez des feuilles de données pour l’intégration, car la base de données peut les interpréter comme des valeurs nulles, ce qui peut entrainer des erreurs de contrainte.
• Évitez les caractères spéciaux dans les noms de champs – L’introduction de chiffres, de caractères spéciaux et d’autres caractères Unicode dans les noms de colonnes peut entrainer des erreurs d’analyse lors de l’importation de données à partir de feuilles de calcul. Les meilleures pratiques pour nommer les rubriques consistent à utiliser des majuscules (par exemple, studentName) ou des traits de soulignement pour rendre les noms plus descriptifs.

Vos données étant structurées, vous êtes prêt à les intégrer à une base de données dans le cloud.

Comment intégrer MariaDB : une procédure étape par étape

Commencez par créer votre base de données MariaDB avec Kinsta. Ensuite, ce guide utilise Coefficient – un connecteur sans code pour l’importation de données de feuilles de calcul, pour connecter votre instance de base de données à Google Sheets. Veillez à installer ce connecteur.

Connectez MySQL Workbench à MariaDB

Tout d’abord, fournissez à votre instance de base de données MariaDB les détails de la connexion externe.

1. Ouvrez la page Connexions externes et copiez les champs Nom d’ hôte externe, Nom d’utilisateur, Mot de passe et Nom de la base de données.

   

   Ici, connectez MySQL Workbench, qui fournit une interface utilisateur graphique pour interagir avec l’instance MariaDB. Vous connectez MySQL Workbench à votre instance de base de données en ajoutant une nouvelle connexion.

2. Sur la page Welcome to MySQL Workbench, cliquez sur MySQL Connection dans le coin inférieur gauche.
3. Sur la page Setup New Connection, saisissez les détails de la connexion externe fournis par votre instance de base de données MariaDB.

   

4. Cliquez sur Test Connection en bas de la page. Un avertissement de connexion concernant une version de serveur incompatible ou non standard apparait. Ignorez l’avertissement. Vous avez maintenant connecté votre instance de base de données à MySQL Workbench.
5. Ensuite, créez une table nommée diabetes_table avec des colonnes en utilisant l’instruction SQL suivante.

   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

Connectez Google Sheets à MariaDB

1. Ouvrez Google Sheets. La feuille de calcul contient déjà un fichier CSV de valeurs séparées par des virgules (diabetes.csv) avec sept colonnes.

   

2. Cliquez sur Extensions.

   

3. Accédez à Coefficient Salesforce, Hubspot Data Connector, puis cliquez sur Lancer.

   

   Cette étape ouvre le connecteur Coefficient sur le côté droit de votre feuille, qui vous permet d’importer et d’exporter des données entre Google Sheets et la base de données MariaDB.

4. Cliquez sur Export toer vers dans Coefficient, puis sur MySQL. Bien que vous vous connectiez à une base de données MariaDB, vous cliquez sur MySQL car MariaDB est un dérivé de MySQL. Il s’agit donc d’une base de données MySQL dotée de fonctionnalités supplémentaires.
5. Saisissez les détails de connexion fournis par votre instance MariaDB et cliquez sur Connect.

   

6. Dans la section Source Data, sélectionnez diabetes dans la liste Tab et Row 1 dans la liste Header row.

   

7. Dans la section Destination, sélectionnez Sheets-db diabetes_table dans la liste Table.
8. Sélectionnez Insert dans la liste Action pour insérer les données de la feuille de calcul.

   

   Dans le panneau Schemas, vous verrez les colonnes de la feuille de calcul.

   

9. Faites correspondre les colonnes de la feuille de calcul aux titres de la table MariaDB et cliquez sur Save.

   

10. Sélectionnez Specific rows on sheet et cliquez sur Next.
11. Testez le mappage en sélectionnant la ligne 12 et cliquez sur Done selecting rows.

    

12. Confirmez votre sélection en cliquant sur Insert 1 row in MySQL. La feuille de calcul comporte désormais une colonne Record ID, une colonne Result indiquant OK et une colonne Timestamp indiquant l’heure de l’exportation.

    

13. Cliquez sur Done.
14. Sélectionnez maintenant d’autres lignes à exporter. Cliquez sur Insert X rows in MySQL , puis sur Done.
15. Utilisez cette requête pour afficher les données importées dans la table MariaDB.

    SELECT * FROM <your_db_name>.diabetes_table;

    

Connecter les feuilles Excel à MariaDB

Assurez-vous d’avoir l’extension Devart. Cette extension vous permet de connecter votre feuille Excel à MariaDB, d’importer et de modifier les données sur Excel, et de mettre à jour les changements dans votre base de données. L’extension est accompagnée d’un guide d’installation.

1. Ouvrez une feuille Excel vierge.
2. Cliquez sur Devart dans la barre de navigation supérieure. L’onglet Devart s’affiche si vous avez installé l’extension.

   

3. Cliquez sur Obtenir des données pour ouvrir l’assistant d’importation de données.

   

4. Sélectionnez la base de données MySQL comme source de données et entrez les détails de votre base de données MariaDB pour vous y connecter.

   

5. Cliquez sur Tester la connexion. Un message Connexion réussie s’affiche.
6. Cliquez sur OK, puis sur Suivant.
7. Utilisez le Visual Query Builder ou une requête SQL personnalisée pour importer toutes les données de la table diabetes vers la feuille Excel.

   

8. Cliquez sur Terminer. Vous disposez à présent d’une feuille Excel contenant les données de la base de données hébergée dans le cloud.

   

9. Pour modifier et mettre à jour cette feuille et la base de données, cliquez sur Mode édition.

   

   Si vous avez choisi de ne pas enregistrer le mot de passe lors de la configuration de la connexion, vous êtes invité à saisir le mot de passe de votre base de données.

10. Testez à nouveau la connexion pour vous assurer que vous êtes toujours connecté après avoir saisi votre mot de passe.
11. Sélectionnez deux nouveaux enregistrements à ajouter à la base de données.

    

12. Cliquez sur Valider, puis sur OK pour appliquer ces modifications et valider les changements dans la base de données MariaDB.
13. Effectuez une requête pour voir la base de données mise à jour. Vous avez maintenant deux nouveaux enregistrements.

    

Établir une connexion avec PostgreSQL

Avant de vous connecter et d’importer des données de Google Sheets vers votre base de données PostgreSQL, vous devez établir une connexion fiable pour garantir un processus d’importation de données transparent.

Créez une base de données PostgreSQL sur Kinsta et utilisez les détails de connexion pour connecter pgAdmin4, une interface utilisateur graphique (GUI).

Comme dans la section précédente, connectez votre instance de base de données à Google Sheets en utilisant Coefficient.

Connecter et importer les données Google et Excel vers PostgreSQL

1. Dans la boîte de dialogue Enregistrer – Serveur, fournissez les détails de votre connexion PostgreSQL. Les détails incluent :
   • Nom d’hôte/adresse
   • Le port
   • Base de données de maintenance
   • Nom d’utilisateur
   • Mot de passe

   

2. Créez une séquence pour les valeurs d’ID de votre table en utilisant l’instruction SQL ci-dessous :

   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. Créez maintenant une table PostgreSQL nommée diabetes_table avec des colonnes dont le type de données et les restrictions correspondent à la table de la feuille de calcul.

   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. Ouvrez diabetes.csv dans Google Sheets.
5. Cliquez sur Extensions, puis sur Coefficient : Salesforce, Hubspot Data Connector, puis cliquez sur Lancer.
6. Ensuite, pour exporter les données de la feuille de calcul dans la base de données PostgreSQL, cliquez sur Exporter vers.
7. Cliquez sur Connecter à côté de PostgreSQL.
8. Saisissez les détails de votre connexion PostgreSQL et cliquez sur Connecter.

   

9. Définissez la manière dont vous souhaitez exporter vos données en sélectionnant diabète dans la liste Onglet et Ligne 1 dans la liste Ligne d’en-tête .

   

10. Sélectionnez public.diabetes_table dans la liste Table de la section Destination.
11. Sélectionnez Insérer dans la liste Action.

    

12. Faites correspondre les colonnes de la feuille à votre table PostgreSQL.
13. Sélectionnez la deuxième ligne et cliquez sur Terminer la sélection des lignes.
14. Confirmez votre sélection en cliquant sur Insérer 1 ligne dans PostgreSQL. La feuille de calcul comporte désormais une colonne ID d’enregistrement, une colonne Résultat indiquant OK et une colonne Horodatage indiquant l’heure de l’exportation.
15. Testez votre intégration en exportant d’autres lignes.
16. Effectuez une requête pour afficher les données récemment importées.

    SELECT * FROM diabetes_table;

    Cette requête affiche toutes les données de la table diabète.

Connexion et exportation des données Postgres vers Excel

Tout d’abord, vous avez besoin des détails de votre connexion PostgreSQL.

1. Ouvrez une feuille Excel vierge et cliquez sur Devart.
2. Cliquez sur Obtenir des données pour ouvrir l’assistant d’importation de données.
3. Sélectionnez la base de données PostgreSQL dans la liste des sources de données et, dans l’ Assistant d’importation de données, entrez les détails de connexion à votre base de données.

   

4. Cliquez sur Tester la connexion pour vérifier que la connexion a réussi.
5. Sélectionnez votre objet et interrogez votre base de données à l’aide de la requête visuelle. Vous pouvez utiliser le Constructeur de requêtes visuelles ou écrire votre propre requête SQL personnalisée pour interroger votre base de données.

   

6. Cliquez sur Terminer. Vous disposez à présent d’une feuille Excel contenant des données. Cliquez sur Rafraichir pour vous assurer que votre feuille de calcul est à jour.

   

7. Cliquez sur Oui pour confirmer.
8. Cliquez ensuite sur Mode édition pour modifier et mettre à jour cette feuille et la base de données.
9. Ajoutez un nouvel enregistrement à la feuille de calcul et cliquez sur Valider pour valider la modification.

   

10. Effectuez maintenant une requête pour voir la base de données mise à jour. Vous pouvez voir que la base de données contient un nouvel enregistrement.

Résumé

Les bases de données hébergées dans le cloud offrent un espace de travail collaboratif qui vous permet de stocker, d’accéder, d’établir et de gérer des relations dynamiques avec les données.

Avec Kinsta, vous pouvez créer des instances de bases de données PostgreSQL et MySQL et utiliser les détails de connexion fournis pour vous connecter à vos feuilles de calcul. Grâce à cette connexion, vous pouvez créer vos tables de base de données, faire correspondre les champs de votre feuille de calcul à ceux de votre base de données dans le cloud et commencer à exporter vos données.

Commencez avec Kinsta pour profiter de la meilleure gestion offerte par les bases de données hébergées dans le cloud.

Vous gérez encore de grandes quantités de données à l’aide de feuilles de calcul ? Faites-nous part de votre méthode de gestion efficace des données volumineuses dans les commentaires ci-dessous !

Jeremy Holcombe Kinsta

Rédacteur en chef du contenu et du marketing chez Kinsta, développeur web WordPress et rédacteur de contenu. En dehors de WordPress, j'aime la plage, le golf et le cinéma. J'ai aussi des problèmes avec les personnes de grande taille ;).

• LinkedIn