Importer des données externes dans une base MySql

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Importer dans des bases de données MySql

Dans cette formation, nous laissons temporairement le code Php de côté. Nous abordons les méthodes d'importation de données dans les bases MySql. Il s'agit dans un premier temps de maîtriser les techniques manuelles. Nous étudierons les méthodes robotisées, par le code PHP, dans un prochain volet.

Un site Web en production a régulièrement besoin de récolter et consolider des données externes. Dès lors qu'elles sont intégrées, elles enrichissent les possibilités offertes par une application serveur. Le format CSV est universel. Il peut être interprété par n'importe quel gestionnaire de base de données. En outre, il permet à ces différents applicatifs de communiquer entre eux.

Sources et présentation de la problématique
Nous proposons de récupérer des fichiers d'exportation disponibles au téléchargement.

• Télécharger le fichier donnees-exportees-externes.rar en cliquant sur son lien,
• Puis, le décompresser dans le dossier de votre choix,

La décompression fournit trois petits fichiers nommés respectivement selection-id-debut, selection-id-fin et test. Le premier est un fichier texte, créé par une exportation réalisée depuis le gestionnaire de base de données Access.

• Double cliquer sur le fichier selection-id-debut.txt pour l'ouvrir dans un bloc note,

Il s'agit d'une exportation de quelques idées de sorties, référencées à l'origine dans une table Access. Une ligne représente un enregistrement. Pour chacun, les informations de champs sont explicitement séparées par un caractère spécial. Ici, il s'agit du point-virgule. Il sera utilisé comme délimiteur lors de l'importation, afin de reconstruire les colonnes. Chaque information de champ est encadrée de doubles côtes. On parle de fichier au format CSV. Ces remarques sont importantes. Nous le constaterons bientôt. Vous remarquez que les entêtes de colonnes brillent par leur absence. En d'autres termes, les noms des champs n'ont pas été exportés.



Ces données sont relativement denses. L'enjeu consiste à préserver leur intégrité durant la phase d'importation. Ainsi, nous pourrons les exploiter en production.

• Fermer ce fichier texte,
• A la racine du dossier de décompression, cliquer droit sur le fichier selection-id-fin.sql,
• Dans le menu contextuel, choisir de l'ouvrir avec un éditeur comme le Notepad,

Comme vous le remarquez, bien qu'il s'agisse du même type de données, l'agencement du contenu est radicalement différent. Nous y trouvons notamment deux syntaxes SQL. La première consiste à créer la table. La seconde consiste à y insérer les informations, dans les champs désignés par la première.



Ce type d'exportation a très bien pu être produit grâce à PhpMyAdmin depuis une base MySql. L'objectif est de pouvoir consolider ces deux sources, de natures différentes, dans une même table de base de données.

Importation CSV
Nous devons commencer par récolter les informations du fichier texte au format Csv. Et pour mieux comprendre les embûches, nous proposons de réaliser ces manipulations avec le fichier test.txt. Il s'agit d'un fichier csv, semblable au premier. Cependant, il est doté de beaucoup moins d'enregistrements. Pour cela, l'interface de PhpMyAdmin doit être ouverte.

• Sur l'interface d'EasyPhp, cliquer sur le bouton Start du Database Server,

Fort logiquement, pour manipuler des bases de données, le serveur de base de données doit être démarré.

• Puis, cliquer sur le bouton Start du Http Server,

Il s'agit du serveur virtuel qui émule les applications serveur. Mais il émule aussi PhpMyAdmin. Il est donc nécessaire.

• Cliquer alors sur le bouton Open du module MySql Administration,

Nous accédons ainsi à l'interface d'administration PhpMyAdmin. Dans le volet de gauche, nous y retrouvons la petite base de données bd_gestion que nous avions créée à l'occasion d'une formation. Elle ne sera pas utile ici, mais elle n'est pas gênante. Nous pouvons gérer autant de bases de données que souhaité.

Et précisément, pour importer ces données, encore faut-il que la base existe. C'est l'occasion de mettre en pratique des connaissances acquises au cours de la Formation pour créer sa première base de données MySql.

• Dans le volet de gauche, cliquer sur le lien Nouvelle base de données,
• Dans la première zone de saisie, la nommer : ideeSorties, par exemple,
• Avec la liste déroulante à sa droite, choisir l'interclassement utf8_general_ci,

Il s'agit d'un système d'encodage classique que nous avions abordé et expliqué au cours de cette formation.

• Enfin, cliquer sur le bouton Créer pour finaliser le processus de création,

La nouvelle base de données apparaît désormais listée dans le volet de gauche. Avant de réaliser une importation, il faut toujours commencer par désigner la base de données de réception.

• Dans le volet de gauche, cliquer sur le lien de la base de données ideesorties,
• Puis, en haut de l'interface PhpMyAdmin, cliquer sur l'onglet Importer,
• Cliquer alors sur le bouton Parcourir,
• Dans le dossier de décompression, double cliquer sur le fichier test.txt,
• Plus bas dans l'interface, choisir le format CSV à l'aide de la liste déroulante,
• Et, juste en dessous, dans la section des options spécifiques, remplacer la virgule par un point-virgule (;) dans la zone Colonnes séparées par,

Nous l'avons en effet constaté en consultant le fichier Csv. Le séparateur de liste est le point-virgule. C'est lui qui délimite les informations de champs. Les doubles côtes sont bien utilisées pour les encadrer en revanche. Nous pouvons donc conserver les autres paramétrages par défaut.

• Cliquer enfin sur le bouton Exécuter, situé en bas de l'interface,

Comme vous le constatez, le processus semble long malgré le peu d'enregistrements contenus dans ce fichier Csv. Une importation longue malgré le peu d'enregistrements à créer révèle des symptômes qu'il faudra sans doute corriger.

Et nous allons le constater.



Une fois le processus achevé, PhpMyAdmin indique qu'une table a été créée. Cela paraît logique et c'est une bonne nouvelle. Il s'agit d'une réponse à une question que nous aurions pu nous poser. Est-il nécessaire de créer la table avant d'importer les données. Pourtant et nous le constaterons, c'est préférable.

Au travers de la syntaxe retournée :

CREATE TABLE IF NOT EXISTS 'ideesorties'.'TABLE 1' ('COL 1' varchar(35), 'COL 2' varchar(14), 'COL 3' varchar(8), 'COL 4' varchar(25)) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Nous remarquons que quatre champs ont été créés et typés en Varchar. Ils sont donc prévus pour recevoir des chaînes de caractères. Leurs dimensions diffèrent. Elles paraissent d'ailleurs fort limitées. Elles risquent de poser souci si d'aventure nous souhaitions insérer de nouveaux enregistrements plus riches à la suite. De plus, aucune clé primaire n'est prévue. Or comme nous l'avons appris avec Access, toute table de base de données doit proposer une clé primaire pour l'unicité des enregistrements et l'intégrité référentielle. Souvenez-vous, l'intégrité référentielle est une sécurité mettant en jeu les relations entre les tables.

• Bref, cliquer néanmoins sur le lien de la table, nommée table1, depuis le volet gauche,

Des soucis d'encodage sautent instantanément aux yeux. Les accents ne sont pas interprétés. C'est la raison pour laquelle le processus d'importation a mis du temps. Dans les paramétrages d'importation, nous devons explicitement indiquer que la source propose des caractères latins.



Ces manipulations intermédiaires sont volontaires. Elles permettent de mieux comprendre les embûches à éviter à l'avenir. Ces péripéties sont aussi l'occasion d'apprendre à manipuler les objets de bases de données, comme les tables. Etant donné que l'importation précédente n'est pas satisfaisante, nous proposons de supprimer la table créée.

• En haut de l'interface de PhpMyAdmin, cliquer sur l'onglet Opérations,
• Dans la section Supprimer les données ou la table, cliquer sur Supprimer la table (DROP),

DROP est la commande SQL pour une telle opération.



La table disparaît en effet, comme en atteste l'arborescence de la base, dans le volet de gauche.

• Cliquer de nouveau sur l'onglet Importer en haut de l'interface de PhpMyAdmin,
• Cliquer sur le bouton Parcourir,
• Double cliquer cette fois sur le fichier Csv plus volumineux : selection-id-debut.txt,
• Dérouler la liste déroulante : Jeu de caractères du fichier, située juste en dessous,
• Choisir l'encodage iso-8859-1,

Ce système d'encodage permet d'interpréter tous les caractères latins notamment.

Bien sûr, nous ne devons pas oublier de spécifier le format et pour ce dernier de définir le séparateur de liste.

• Dans la section Format, choisir le modèle CSV comme précédemment,
• Dans les options spécifiques, remplacer la virgule par un point-virgule dans la zone Colonnes séparées par,
• Enfin, cliquer sur le bouton Exécuter,

Bien que les enregistrements soient beaucoup plus nombreux, le processus est cette fois plus rapide. Cette précision sur le système d'encodage est salvatrice. Et nous allons le constater en visualisant les données.

• Cliquer sur le lien de la table dans le volet de gauche,

Les enregistrements apparaissent et cette fois, les caractères latins sont parfaitement interprétés. Comme l'indique la mention en haut de la table, 1121 enregistrements ont été importés. Il s'agit donc d'une méthode productive pour récupérer des informations riches afin de les passer en production.



Importer des données au format Sql
Comme nous l'avons remarqué précédemment, le fichier sql permet l'importation de données par l'exécution de requêtes SQL. La première d'entre elles consiste à créer la table de réception. La seconde permet d'y insérer les enregistrements. Mais comme nous avions pu le constater, les champs sont dimensionnés au plus juste et la clé primaire est absente. Sachez néanmoins que toute exportation de base de données MySql depuis PhpMyAdmin, peut proposer des fichiers prêts à l'emploi, structure comprise, dans la mesure où la table a été créée en conséquence.

• Cliquer sur l'onglet Importer en haut de la fenêtre PhpMyAdmin,
• Cliquer sur le bouton Parcourir et désigner le fichier selection-id-fin.sql,

Tous les autres réglages peuvent être conservés puisque le format proposé par défaut est le Sql.

• Cliquer sur le bouton Exécuter,

Le processus est relativement court avant que PhpMyAdmin ne rende son verdict. L'importation s'est déroulée avec succès.

• Cliquer sur le lien idsorties de la nouvelle table créée par la requête Sql,

Comme vous le remarquez, 319 enregistrements ont été importés. De plus, les caractères latins sont parfaitement interprétés. Il s'agit d'une restitution d'une base de données MySql à l'origine.

Créer une table pour importer les données
La précédente technique, celle de l'importation SQL est donc fort intéressante. Si la table est bien configurée en amont, restituant des requêtes SQL créant et dimensionnant ses champs, nulle autre manipulation n'est nécessaire. Mais dans le cas de l'importation de données au format CSV, le problème est tout autre. Le système d'encodage doit être défini. Les champs créés automatiquement sont mal dimensionnés. De plus, aucune clé primaire n'est créée. Ce dernier point n'est pas acceptable. C'est pourquoi, nous proposons de créer une table en cohérence avec les informations à recevoir. Dans cette table, nous devons y importer les données issues des deux fichiers, bien que leur nature soit différente.

Préalablement, nous devons purger la base de données de ces deux tables d'essai, créées à la volée.

• Dans le volet de gauche, cliquer sur le lien table1,
• Cliquer ensuite sur l'onglet Opérations en haut de la fenêtre PhpMyAdmin,
• Cliquer alors sur le lien Supprimer la table (Drop),
• Puis, valider par Ok,
• Cliquer ensuite sur le lien de la table idsorties dans le volet de gauche pour la désigner,
• Cliquer de nouveau sur l'onglet Opérations,
• Puis, cliquer sur le lien Supprimer la table (Drop) et valider,

Les deux tables ont disparu. La base de données est désormais purgée et prête à accueillir la nouvelle table parfaitement configurée. Il s'agit de créer quatre champs capables de recevoir des informations textuelles. Nous souhaitons prévoir suffisamment de possibilités pour des importations futures. Nous devons adapter les tailles, donc les dimensions de ces champs.

Ils concernent dans l'ordre la désignation (ou raison sociale), l'activité, le département et la ville. Nous pouvons prévoir des tailles de 150 caractères pour le premier et de 50 caractères pour les autres.

• Dans le volet sur la gauche, cliquer sur la base ideesorties pour la désigner,
• Au centre de l'écran, saisir un nom pour la nouvelle table : liste_id, par exemple,
• Régler le nombre de colonnes sur 4,
• Puis, cliquer sur le bouton Exécuter,
• Dans l'écran qui intervient, créer les quatre champs comme suit :

liste_nom, Type : Varchar, Taille : 150, Interclassement : utf8_general_ci,
liste_activite, Type : Varchar, Taille : 50, Interclassement : utf8_general_ci,
liste_dep, Type : Varchar, Taille : 50, Interclassement : utf8_general_ci,
liste_ville, Type : Varchar, Taille : 50, Interclassement : utf8_general_ci,



Comme nous l'avions évoqué lors de la formation dédiée, il est important de préfixer les noms des champs du nom de la table. Ils ne doivent proposer aucun caractère latin et aucun espace.

• Définir ensuite l'interclassement de la table sur utf8_general_ci,
• Conserver le moteur de stockage sur MyIsam,

Nous l'avions évoqué, il s'agit du moteur le plus performant pour les requêtes classiques.

• Enfin, cliquer sur le bouton Exécuter,

L'affichage bascule automatiquement sur la structure de la table que nous venons de créer. Nous n'avons pas configuré de clé primaire à ce stade, pour une bonne et simple raison. Le fichier CSV propose quatre champs qui ne sont pas définis explicitement. Pour éviter toute incohérence, nous devons procéder à l'importation de ces données avant de définir la clé primaire.

• Cliquer sur l'onglet Importer en haut de la fenêtre PhpMyAdmin,
• Avec le bouton Parcourir, sélectionner le fichier selection-id-debut.txt,
• Définir le jeu de caractères sur : iso-8859-1,
• Choisir le format CSV à la place du modèle SQL,
• Remplacer la virgule par le point-virgule dans la zone Colonnes séparées par,
• Puis, cliquer sur le bouton Exécuter,

Un message de confirmation apparaît indiquant que l'importation des données s'est déroulée avec succès.

Mais à ce stade un problème persiste donc. Aucune clé primaire n'a été définie. Nous allons pallier ce problème car rien n'est irréversible avec PhpMyAdmin. Il demeure cependant important que les tables et la base de données soient bien conçues à l'origine.

• Cliquer sur le lien de la table liste_id,
• Ensuite, cliquer sur l'onglet Structure en haut de la fenêtre de PhpMyAdmin,

En bas de l'aperçu de la structure, PhpMyAdmin propose d'ajouter de nouveaux champs à cette table. Par défaut, la valeur est réglée sur une seule colonne. C'est ce que nous souhaitons pour créer le champ de la clé primaire.

• Avec la liste déroulante, choisir de l'insérer en début de table,
• Puis, cliquer sur le bouton Exécuter,

• Dans l'éditeur qui suit, le nommer liste_num,
• Conserver son type sur Int car il doit s'agir d'un numéro auto-incrémenté,
• Ne pas définir sa taille,

L'interclassement n'est pas utile lorsqu'il s'agit de données numériques fort logiquement.

• Cocher la case de la colonne A.I.,

Cette abréviation signifie littéralement : auto-incrémenté.

• Dans la boîte de dialogue qui apparaît, conserver le choix de l'index sur Primary,
• Puis valider par Ok,

Nous définissons le champ de la clé primaire naturellement associé à un numéro auto-incrémenté.

• Cliquer sur le bouton Enregistrer pour valider la création de la clé primaire,
• Cliquer ensuite sur le lien de la table liste_id dans le volet de gauche,
• Puis, cliquer sur l'onglet Structure en haut de l'interface de PhpMyAdmin,

Ainsi, nous affichons la table en conception.



Vous remarquez le symbole d'une clé en regard du champ que nous venons de créer. Il indique qu'il s'agit effectivement de la clé primaire essentielle dans toute table de base de données. Nous savons qu'un champ auto-incrémenté remplit parfaitement son rôle au fur et à mesure de l'insertion de nouvelles données. La question est de savoir s'il va réagir correctement a posteriori, soit durant une phase d'importation de données externes.

• Cliquer sur l'onglet Parcourir en haut de la fenêtre pour afficher le contenu de la table,

Comme vous le remarquez, même a posteriori, le champ auto-incrémenté de la clé primaire a parfaitement réagi. Chaque enregistrement est dès lors repéré explicitement comme unique. Après importation, notre table propose 1121 enregistrements.



Adapter l'importation des fichiers SQL
Désormais, il s'agit de poursuivre cette importation avec les données issues du fichier Sql. La clé primaire n'est pas un souci bien que le fichier Sql ne considère que quatre champs. Il suffit d'adapter la syntaxe, depuis le fichier source, afin de désigner explicitement les champs de réception. Il en va de même quant au nom de la table. Si ces opérations ne sont pas entreprises, l'importation se déroulera dans une nouvelle table et ses champs, comme l'impose l'actuelle syntaxe.

• A la racine du dossier de décompression, cliquer droit sur le fichier selection-id-fin.sql,
• Dans le menu contextuel, choisir de l'ouvrir avec un éditeur comme le Notepad,
• Supprimer les quatre lignes de la syntaxe Sql consistant à créer la table (Ci-dessous) :

CREATE TABLE 'idsorties' (
'COL 1' varchar(36) DEFAULT NULL,
'COL 2' varchar(17) DEFAULT NULL,
'COL 3' varchar(10) DEFAULT NULL,
'COL 4' varchar(25) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

• Dans la syntaxe de la requête Insert Into, remplacer les quatre noms de champs (Col 1, Col 2...) par les noms des quatre nouveaux champs,
• Adapter de même le nom de la table, comme suit :

...
INSERT INTO 'liste_id' ('liste_nom', 'liste_activite', 'liste_dep', 'liste_ville') VALUES
('FOLIES DU LAC', 'Spectacle', '26-Drome', 'Chateauneuf sur isere'),
('LA FERME AUX CROCODILES', 'Parc animalier', '26-Drome', 'Pierrelatte'),
...

Comme vous le constatez, nous ignorons le champ liste_num. Il est défini pour s'auto-incrémenter à chaque nouvel ajout d'enregistrement.

• Enregistrer les modifications et fermer le fichier sql,
• Revenir sur l'interface de PhpMyAdmin,
• Cliquer sur le lien ideesorties dans le volet de gauche pour désigner la base de données,
• Cliquer sur l'onglet Importer en haut de la fenêtre de PhpMyAdmin,
• Cliquer sur le bouton Parcourir puis double cliquer sur le fichier selection-id-fin.sql,
• Conserver tous les autres réglages par défaut et cliquer sur le bouton Exécuter,

Un message de succès confirme le bon déroulement des opérations.

• Cliquer sur le lien de la table liste_id dans le volet de gauche,
• Puis, cliquer sur l'onglet Parcourir en haut de la fenêtre PhpMyAdmin,

Comme vous le constatez, le nombre d'enregistrements a effectivement progressé. Il est passé de 1121 à 1440.

• Cliquer sur la double flèche au-dessus de la table pour accéder aux derniers enregistrements,

Nous confirmons la bonne réception des informations externes. Les données Sql ont été insérées après les données CSV. Et comme vous le constatez, le champ liste_num de la clé primaire, s'est incrémenté automatiquement. Il repère chaque enregistrement comme unique. Cette clé est aussi un moyen efficace pour accéder aux données depuis le code Php par clause Where dans une requête SQL.



Déverrouiller les capacités d'importation
Il s'agit d'une limitation que vous avez sans doute remarquée dans l'écran d'importation de PhpMyAdmin. La taille du fichier externe ne peut pas excéder un certain poids. Or, dans le cas de sites professionnels en production, les importations dans les bases de données ne sont pas rares. Et ces contraintes deviennent très vite problématiques.

• Cliquer sur l'onglet Importer en haut de la fenêtre PhpMyAdmin,

Une mention, à droite du bouton Parcourir, indique que la taille du fichier ne peut pas excéder 2 Mo. Même compressées, les bases sur les sites en production, dépassent rapidement et allègrement ce chiffre. La solution consiste à modifier ce paramétrage dans le fichier de configuration Php.ini.

Dans la formation Php pour transférer des courriers par formulaire de contact, nous avions appris à y accéder depuis le disque dur. Mais il est aussi possible de l'atteindre depuis l'interface d'EasyPhp.

• Revenir sur l'interface d'EasyPhp,
• En haut de la page, cliquer sur le lien Applications,
• Dans la section de gauche, cliquer sur la rubrique PHP,
• Puis, cliquer sur le lien de la version en cours,
• Au centre de l'écran, cliquer sur le bouton Configuration File,
• Puis, cliquer sur l'icône du crayon située en haut à droite de l'aperçu, pour éditer le fichier,

• Dans l'éditeur qui apparaît, réaliser le raccourci CTRL + F,

Nous affichons ainsi la boîte de dialogue de recherche.

• Taper le mot clé : max_file,

Nous atteignons la ligne permettant de paramétrer la taille maximale autorisée pour les upload.

• Remplacer le chiffre 2 par le chiffre 8 : upload_max_filesize = 8M,
• Enregistrer les modifications (CTRL + S) et fermer l'éditeur,
• Dans EasyPhp, cliquer sur le lien Home (Symbole maison) pour revenir sur la page d'accueil,
• Cliquer sur le bouton Restart du serveur Http,

Pour que les modifications de configuration soient prises en compte, le serveur doit effectivement être redémarré. A chaque lancement, EasyPhp consulte les consignes et paramètres du fichier Php.ini notamment.

• Puis, cliquer sur le bouton Open du module MySql,
• Dans l'interface de PhpMyAdmin, cliquer sur l'onglet Importer,

Comme vous le constatez, des bases de données plus volumineuses peuvent désormais être importées.

Dans une prochaine formation, nous verrons comment robotiser des importations de données dans des bases MySql.