Comment importer un fichier Excel sur unt table MySQL ayant des clés étrangères?

**geeka** · 18/08/2015, 14h46

Bonjour,
Je souhaiterais transférer mes données contenues dans un fichier Excel vers ma BDD MySQL.
Voici le contenu de ma table :

Table structure

id --------------------- PK
nom_contact--------------
nom_structure------------
id_typologie ----------- FK
id_pays --------------- FK

Mon fichier Excel contient les informations suivantes :
le nom du contact, le nom de la structure, la typologie et le pays.

Le problème ce sont les clés étrangères (FK), dans ma table j'ai des ID faisant référence aux données d'une autre table, alors que dans mon fichier, j'ai toutes les données brutes.
Comment puis-je importer mon fichier Excel sachant que j'ai des clés étrangères que je devrais mentionner avant ? Comment faire dans ce cas, est-ce possible ?

Merci

**fsmrel** · 18/08/2015, 14h59

Bonjour geeka,

Envoyé par geeka

dans mon fichier, j'ai toutes les données brutes.

Logiquement, les clés étrangères font référence à des clés primaires déjà existantes de la table de référence.

Qu’en est-il ?

**fsmrel** · 18/08/2015, 16h17

Les colonnes id_typologie id_pays référencées appartiennent-elles à la même table « parente » ?

**fsmrel** · 18/08/2015, 16h58

En attente de plus d’informations de votre part, je me limite pour le moment au scénario suivant :

La base de données contient une table PAYS, dont la structure est issue du script suivant :


CREATE TABLE PAYS
(
        Id_Pays          INTEGER          NOT NULL
      , Nom_Pays         VARCHAR(16)      NOT NULL      	
    , CONSTRAINT PAYS_PK PRIMARY KEY (Id_Pays)
) ;

INSERT INTO PAYS (Id_Pays, Nom_Pays)  VALUES (1, 'France'), (2, 'Belgique'), (3, 'Canada') ;

Le fichier Excel a été converti au format CSV (les valeurs de la colonne id_pays sont des valeurs existant dans la table PAYS) :


id    nom_contact    nom_structure    id_typologie    id_pays
1     Fernand        Ets Naudin       3               2
2     Raoul          La péniche       1               1
3     Paul           La péniche       1               1

Une structure pour héberger ce fichier dans la base de données :


CREATE TABLE imageExcel
(
        id               INTEGER          NOT NULL
      , nom_contact      VARCHAR(16)      NOT NULL 
      , nom_structure    VARCHAR(16)      NOT NULL       
      , id_typologie     INTEGER          NOT NULL 
      , id_pays          INTEGER          NOT NULL
    , CONSTRAINT imageExcel_PK PRIMARY KEY (id)
     , CONSTRAINT imageExcel_Pays_FK FOREIGN KEY (id_pays) 
           REFERENCES PAYS (id_pays)
 ) ;

Importation du fichier CSV (nommé ici « geeka.csv ») :


LOAD DATA INFILE 'C:\Developpez/$MySQL/geeka.csv' INTO TABLE imageExcel
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n' 
IGNORE 1 ROWS ;

Pour voir :


SELECT * FROM imageExcel ;

=>


id    nom_contact    nom_structure    id_typologie    id_pays
1     Fernand        Ets Naudin       3               2
2     Raoul          La péniche       1               1
3     Paul           La péniche       1               1

**geeka** · 18/08/2015, 17h03

Merci pour votre réponse.
Oui les clés étrangères font référence à des clés primaires d'autres tables.
L'id_typologie fait référence à la table typologie
Table typologie
id_typologie------PK
typologie---------

Et l'id_pays fait référence à une autre table, la table pays
Table pays
id_pays-----PK
code_pays------
pays------------

**geeka** · 18/08/2015, 17h17

Merci pour votre exemple bien expliqué.

Je dois juste préciser que dans mon fichier Excel, je n'ai pas d'id_typologie et d'id_pays mais plutôt nom_typologie et nom_pays.

**fsmrel** · 18/08/2015, 17h20

Addendum : je n'ai pas vu votre dernier message, donc j'ai conservé les » id ».

Le scénario CSV devient le suivant (présence de la table TYPOLOGIE et ajout dans la table imageExcel de la clé étrangère qui va bien) :


CREATE TABLE PAYS
(
        Id_Pays          INTEGER          NOT NULL
      , Nom_Pays         VARCHAR(16)      NOT NULL      	
    , CONSTRAINT PAYS_PK PRIMARY KEY (Id_Pays)
) ;

INSERT INTO PAYS (Id_Pays, Nom_Pays)  VALUES (1, 'France'), (2, 'Belgique'), (3, 'Canada') ;

CREATE TABLE TYPOLOGIE
(
        Id_Typo          INTEGER          NOT NULL
      , Nom_Typo         VARCHAR(16)      NOT NULL      	
    , CONSTRAINT PAYS_PK PRIMARY KEY (Id_Typo)
) ;

INSERT INTO TYPOLOGIE (Id_Typo, Nom_Typo)  VALUES (1, 'Typo A'), (2, 'Typo B'), (3, 'Typo C') ;

CREATE TABLE imageExcel
(
        id               INTEGER          NOT NULL
      , nom_contact      VARCHAR(16)      NOT NULL 
      , nom_structure    VARCHAR(16)      NOT NULL       
      , id_typologie     INTEGER          NOT NULL 
      , id_pays          INTEGER          NOT NULL
    , CONSTRAINT imageExcel_PK PRIMARY KEY (id)
     , CONSTRAINT imageExcel_Pays_FK FOREIGN KEY (id_pays) 
           REFERENCES PAYS (id_pays)
      , CONSTRAINT imageExcel_Typologie_FK FOREIGN KEY (id_typologie) 
           REFERENCES TYPOLOGIE (Id_Typo)
) ;

LOAD DATA INFILE 'D:\Developpez/$MySQL/geeka.csv' INTO TABLE imageExcel
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n' 
IGNORE 1 ROWS ;

SELECT * FROM imageExcel ;

=>


id    nom_contact    nom_structure    id_typologie    id_pays
1     Fernand        Ets Naudin       3               2
2     Raoul          La péniche       1               1
3     Paul           La péniche       1               1

**fsmrel** · 18/08/2015, 17h28

Envoyé par geeka

Je dois juste préciser que dans mon fichier Excel, je n'ai pas d'id_typologie et d'id_pays mais plutôt nom_typologie et nom_pays.

Précision non négligeable ! On va s’occuper de ça, à coups de jointures avec les colonnes nom_typologie et nom_pays.

**geeka** · 18/08/2015, 17h32

Je vous remercie

Donc c'est possible de tout importer alors.

**fsmrel** · 18/08/2015, 18h16

C'est parti !

Le fichier Excel au format CSV a maintenant la structure suivante :


id    nom_contact    nom_structure    nom_typologie    nom_pays
1     Fernand        Ets Naudin       Typo C           Belgique
2     Raoul          La péniche       Typo A           France
3     Paul           La péniche       Typo A           France

Structure de la table imageExcel correspondante :


CREATE TABLE imageExcel
(
        id               INTEGER          NOT NULL
      , nom_contact      VARCHAR(16)      NOT NULL 
      , nom_structure    VARCHAR(16)      NOT NULL 
      , Nom_Typologie    VARCHAR(16)      NOT NULL 
      , Nom_Pays         VARCHAR(16)      NOT NULL
    , CONSTRAINT imageExcel_PK PRIMARY KEY (id)
 ) ;

On met en œuvre une table supplémentaire, destinée à héberger les valeurs des clés primaires correspondant aux noms des typologies et pays :


CREATE TABLE imageExcelComplet
(
        id               INTEGER          NOT NULL
      , nom_contact      VARCHAR(16)      NOT NULL 
      , nom_structure    VARCHAR(16)      NOT NULL               
      , Id_Typologie     INTEGER          NOT NULL 
      , id_pays          INTEGER          NOT NULL
    , CONSTRAINT imageExcel_PK PRIMARY KEY (id)
     , CONSTRAINT imageExcel_Pays_FK FOREIGN KEY (id_pays) 
           REFERENCES PAYS (id_pays)
      , CONSTRAINT imageExcel_Typologie_FK FOREIGN KEY (Id_Typologie) 
           REFERENCES TYPOLOGIE (Id_Typologie)
) ;

D’où le script SQL :


CREATE TABLE PAYS
(
        Id_Pays          INTEGER          NOT NULL
      , Nom_Pays         VARCHAR(16)      NOT NULL      	
    , CONSTRAINT PAYS_PK PRIMARY KEY (Id_Pays)
) ;

INSERT INTO PAYS (Id_Pays, Nom_Pays)  VALUES (1, 'France'), (2, 'Belgique'), (3, 'Canada') ;

CREATE TABLE TYPOLOGIE
(
        Id_Typologie     INTEGER          NOT NULL
      , Nom_Typologie    VARCHAR(16)      NOT NULL      	
    , CONSTRAINT PAYS_PK PRIMARY KEY (Id_Typologie)
) ;

INSERT INTO TYPOLOGIE (Id_Typologie, Nom_Typologie)  VALUES (1, 'Typo A'), (2, 'Typo B'), (3, 'Typo C') ;

CREATE TABLE imageExcel
(
        id               INTEGER          NOT NULL
      , nom_contact      VARCHAR(16)      NOT NULL 
      , nom_structure    VARCHAR(16)      NOT NULL       
      , Nom_Typologie    VARCHAR(16)      NOT NULL 
      , Nom_Pays         VARCHAR(16)      NOT NULL
    , CONSTRAINT imageExcel_PK PRIMARY KEY (id)
) ;

LOAD DATA INFILE 'D:\Developpez/$MySQL/geeka.csv' INTO TABLE imageExcel
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\r\n' 
IGNORE 1 ROWS ;

SELECT * FROM imageExcel ;

CREATE TABLE imageExcelComplet
(
        id               INTEGER          NOT NULL
      , nom_contact      VARCHAR(16)      NOT NULL 
      , nom_structure    VARCHAR(16)      NOT NULL
      , Id_Typologie     INTEGER          NOT NULL 
      , id_pays          INTEGER          NOT NULL
    , CONSTRAINT imageExcel_PK PRIMARY KEY (id)
     , CONSTRAINT imageExcel_Pays_FK FOREIGN KEY (id_pays) 
           REFERENCES PAYS (id_pays)
      , CONSTRAINT imageExcel_Typologie_FK FOREIGN KEY (Id_Typologie) 
           REFERENCES TYPOLOGIE (Id_Typologie)
) ;

INSERT INTO imageExcelComplet (id, nom_contact, nom_structure, Id_Typologie, id_pays)  
    select x.id, x.nom_contact, x.nom_structure, y.Id_Typologie, z.id_pays
    from   imageExcel as x,  TYPOLOGIE as y, PAYS as z
    where  x.Nom_Typologie = y.Nom_Typologie AND x.Nom_Pays = z.Nom_Pays
;

SELECT * FROM imageExcelComplet ;

=>


id    nom_contact    nom_structure    Id_Typologie    id_pays
1     Fernand        Ets Naudin       3               2
2     Raoul          La péniche       1               1
3     Paul           La péniche       1               1

En espérant que que vous n'aurez pas d'erreurs d'intégrité référentielle, mais là, je n'y peux rien, sauf à rajouter un contrôle ad-hoc lor de l'INSERT...

**fsmrel** · 18/08/2015, 18h27

Pour éviter des problèmes de doublons, si ça n'est pas déjà fait, il serait bien que {Nom_Pays} soit clé alternative de la table PAYS et {Nom_Typologie} clé alternative de TYPOLOGIE :


CREATE TABLE PAYS
(
        Id_Pays          INTEGER          NOT NULL
      , Nom_Pays         VARCHAR(16)      NOT NULL      	
    , CONSTRAINT PAYS_PK PRIMARY KEY (Id_Pays)
    , CONSTRAINT PAYS_AK UNIQUE (Nom_Pays)
) ;

CREATE TABLE TYPOLOGIE
(
        Id_Typologie     INTEGER          NOT NULL
      , Nom_Typologie    VARCHAR(16)      NOT NULL      	
    , CONSTRAINT TYPOLOGIE_PK PRIMARY KEY (Id_Typologie)
    , CONSTRAINT TYPOLOGIE_AK UNIQUE (Nom_Typologie)    
) ;

**geeka** · 19/08/2015, 11h07

Merci beaucoup !!

J'ai juste une question, je n'ai pas compris d'où viennent les x, y, y z dans votre code :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
INSERT INTO imageExcelComplet (id, nom_contact, nom_structure, Id_Typologie, id_pays)  
    select x.id, x.nom_contact, x.nom_structure, y.Id_Typologie, z.id_pays
    from   imageExcel as x,  TYPOLOGIE as y, PAYS as z
    where  x.Nom_Typologie = y.Nom_Typologie AND x.Nom_Pays = z.Nom_Pays
;

**fsmrel** · 19/08/2015, 16h13

Bonjour geeka,

Envoyé par geeka

je n'ai pas compris d'où viennent les x, y, y z dans votre code

Ce sont des synonymes, des alias, des « token », des surnoms, des noms de corrélation, que sais-je : il s’agit de surnommer ponctuellement, de façon brève, des tables dont le nom peut mesurer des kilomètres...

FROM imageExcel AS x, TYPOLOGIE AS y, PAYS AS z

Il s’agit d’alléger l’écriture du code. Mais ça n’est pas strictement nécessaire. J’aurais très bien pu écrire :


INSERT INTO imageExcelComplet (id, nom_contact, nom_structure, Id_Typologie, id_pays)  
    select imageExcel.id, imageExcel.nom_contact, imageExcel.nom_structure, TYPOLOGIE.Id_Typologie, PAYS.id_pays
    from   imageExcel, TYPOLOGIE, PAYS
    where  imageExcel.Nom_Typologie = TYPOLOGIE.Nom_Typologie AND imageExcel.Nom_Pays = PAYS.Nom_Pays
;

Bref, disons que l’objet est de simplifier la vie des flemmards...

**geeka** · 20/08/2015, 14h38

Merci pour l'explication.

**geeka** · 20/08/2015, 18h10

Re bonsoir,
Encore un petit problème, je n'ai pas obtenu de bons résultats pour mes données

Il n'y a qu'une seule ligne qui est insérée (la ligne 5) de ma liste de données.
Les données ont été bien insérées dans la table imageExcel. Mais dans la table imageExcelComplet, seule une seule ligne a été insérée.
Pourquoi y a-t-il un problème avec les autres données ?

EDIT: j'ai essayé votre code, il marche très bien, tout a été inséré. Je vais revoir mes tables et mes données .

Merci encore

Comment importer un fichier Excel sur unt table MySQL ayant des clés étrangères?

MySQL

Discussions similaires

Partager

Partager