Importation d'un fichier Excel dans MySQL

Version imprimable

28/03/2016, 18h54
malkimohamed

1 pièce(s) jointe(s)

Importation d'un fichier Excel dans MySQL

Importation d'un fichier Excel dans MySQL

Bonjour à tous,
j'ai besoins de votre aides SVP:
j'ai un tableau excel avec les champs suivants :
Pièce jointe 205227
mais je veux liés un nombre de ces champs à des tableaux diffèrentes ex:
DesignationArticle => tabale MYSQL Article
Quantité => tabale MYSQL Article
poids => tabale MYSQL Article
CatégorieClient => tabale MYSQL CategorieClient
Unité => tabale MYSQL Unité

Comment je peux faire ça, merci d'avance :(
29/03/2016, 00h27
Artemus24
Salut malkimohamed.

Dans un premier temps, vous créez une table MySql contenant la même structure que votre fichier excel.
Vous chargez votre fichier excel dans la table MySql, en faisant un "load data local infile".

Citation:

Envoyé par malkimohamed

je veux lier un nombre de ces champs à des tableaux différents

Je ne comprends pas ce que vous entendez par lier ?

S'il s'agit de rediriger le contenu d'une colonne dans une table MySql, par exemple la colonne "Unité" dans la table MySql "Unité", il suffit de faire ceci :
Code:

1 2 insert into unite (`unite`) select unite from votre_table;
--> http://dev.mysql.com/doc/refman/5.7/en/insert.html

Pour mieux comprendre votre problème, donnez-nous un exemple avec un jeu d'essai et le descriptif de vos tables.

@+
29/03/2016, 01h02
malkimohamed

1 pièce(s) jointe(s)

Merci bien Artemus24 pour votre aide
mon tableau excel est un rapport des données, donc il contient des champs des certains tables en relation par exemple (Numero de CMD ) de la table Commande et en relation avec (categorieClient ) de la table Client et (Désignation ) de la table Article;
voila la structure de mon base de données MySQL
Pièce jointe 205244

mais est ce que la création d'une table MySql contenant la même structure que mon fichier excel est obligatoire pour faire l'importation ? il n'y a pas d'autre solution ?
merci infiniment.
29/03/2016, 13h19
escartefigue

Citation:

Envoyé par malkimohamed

mais est ce que la création d'une table MySql contenant la même structure que mon fichier excel est obligatoire pour faire l'importation ? il n'y a pas d'autre solution ?
merci infiniment.

Il y a une autre solution, si vous utilisez peu de tables pour charger votre tableau excel, vous n'en mentionnez que 2 mais ce n'est peut être qu'un exemple, créez plutôt une vue qui fera la jointure entre les tables dont vous avez besoin pour charger votre tableau. Ca évitera de créer une redondance d'information avec une table supplémentaire, et ca vous épargnera le temps de chargement de cette table.
Attention toutefois, la création d'une table vous permet de figer les données, prendre une photo à un instant "t", alors qu'une vue est un accès en temps réel aux données, ce n'est donc pas exactement la même chose.
Si vous voulez figer les données, il faut dupliquer le support (pas forcément sur une table, un fichier suffit), sinon, la vue est une bien meilleure solution
Ensuite, vous accédez à la vue via des liens ODBC.

Salut à tous.

Citation:

Envoyé par malkimohamed

mon tableau excel est un rapport des données ...

Dois-je comprendre qu'à partir de votre fichier excel, vous devez remplir toutes vos tables, qui au préalable sont toutes vides ?

Citation:

Envoyé par malkimohamed

est ce que la création d'une table MySql contenant la même structure que mon fichier excel est obligatoire pour faire l'importation ?

La création d'une table de travail pour l'importation n'est pas nécessaire.
Mais c'est quand même plus souple à l'usage pour répartir toutes vos données dans les bonnes tables.

Normalement, le "load data local infile" sert à charger un fichier excel dans une table. D'où la table de travail ou e chargement.
On peut procéder autrement, mais cela va compliquer grandement votre requête de chargement dans le "load data local infile".

Si vous utilisez des contraintes comme par exemple les clefs étrangères, il faut obligatoirement charger certaines tables avant les autres.

Et si vous rencontrez des problèmes de volumétrie, il faut segmenter le chargement en plusieurs phases.

Si vous ne savez pas comment utiliser un "load data local infile", voici un petit exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS Cities
--------------
 
--------------
CREATE TABLE Cities (
    Country     CHAR(2),
    City        CHAR(22),
    AccentCity  CHAR(3),
    Region      CHAR(2),
    Population  INT UNSIGNED NULL DEFAULT NULL,
    Latitude    FLOAT,
    Longitude   FLOAT,
        Date        DATETIME
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
TRUNCATE Cities
--------------
 
--------------
set unique_checks      = 0
--------------
 
--------------
set foreign_key_checks = 0
--------------
 
--------------
set sql_log_bin        = 0
--------------
 
--------------
alter table Cities DISABLE KEYS
--------------
 
--------------
commit
--------------
 
--------------
LOAD DATA LOCAL INFILE 'fichier.txt'
     INTO TABLE `Cities`
     CHARACTER SET latin1
     FIELDS TERMINATED BY ','
     ENCLOSED BY '"'
     ESCAPED BY '\\' LINES
     TERMINATED BY '\r\n'
     IGNORE 0 LINES
     (@F1, @F2, @F3, @F4, Population, Latitude, Longitude, @F8)
     set Country = trim(@F1),
            City = trim(@F2),
      AccentCity = trim(@F3),
          Region = trim(@F4),
            Date = outils.chgdate(@F8)
--------------
 
--------------
commit
--------------
 
--------------
alter table Cities ENABLE KEYS
--------------
 
--------------
set unique_checks      = 1
--------------
 
--------------
set foreign_key_checks = 1
--------------
 
--------------
set sql_log_bin        = 1
--------------
 
--------------
commit
--------------
 
--------------
select  concat('>', Country,   '<') as 'Country',
        concat('>', City,      '<') as 'City',
        concat('>', AccentCity,'<') as 'AccentCity',
        concat('>', Region,    '<') as 'Region',
        concat('>', Population,'<') as 'Population',
        concat('>', Latitude,  '<') as 'Latitude',
        concat('>', Longitude, '<') as 'Longitude',
        concat('>', date,      '<') as 'Date'
from `Cities`
--------------
 
+---------+-----------------------+------------+--------+------------+----------+-----------+-----------------------+
| Country | City                  | AccentCity | Region | Population | Latitude | Longitude | Date                  |
+---------+-----------------------+------------+--------+------------+----------+-----------+-----------------------+
| >fr<    | >dijon bourgogne<     | >---<      | >bo<   | >500000<   | >200<    | >150<     | >2015-12-10 15:20:00< |
| >fr<    | >paris ile de france< | >---<      | >il<   | >2000000<  | >100<    | >50<      | >2015-06-15 07:15:00< |
| >fr<    | >nice paca<           | >---<      | >pa<   | >100000<   | >250<    | >75<      | >2015-07-25 19:58:00< |
| >fr<    | >marseille paca<      | >---<      | >pa<   | >3000<     | >750<    | >15<      | >2015-03-12 19:58:00< |
+---------+-----------------------+------------+--------+------------+----------+-----------+-----------------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
Appuyez sur une touche pour continuer...

Citation:

Envoyé par Escartefigue

Il y a une autre solution, si vous utilisez peu de tables pour charger votre tableau excel

Malkimohamed demande comment charger un fichier excel dans plusieurs tables MySql et non l'inverse.

@+

29/03/2016, 13h54
escartefigue

Citation:

Envoyé par Artemus24

Malkimohamed demande comment charger un fichier excel dans plusieurs tables MySql et non l'inverse.

Oups en effet, j'ai lu trop vite :oops:

Donc en effet, il faut savoir
- quel mode de chargement adopter (annule, ignore, ajout)
- respecter l'ordre de chargement en fonction des contraintes FK (parents avant enfants)
- que que faire, si mode ajout, en cas d'égalité sur la clef (rejet, mise à jour)

Quelques règles à mettre en place donc dont le script dépendra, par exemple si c'est de l'ajout qu'il faut faire, alors ce ne sera pas du load mais de l'insert
29/03/2016, 14h26
Artemus24

Salut Escartefigue.

Le fichier excel, si j'ai bien compris, contient la totalité de ce qui doit être chargé, non pas dans une table, mais dans plusieurs tables d'une base de données.
J'ai supposé, que toutes les tables sont vides car c'est le plus simple à faire.

Si ce n'est pas le cas, alors il faut traiter la différence entre l'existent et le fichier excel.
--> présent en table mais absent du fichier ==> suppression
--> absent en table mais présent dans le fichier ==> création
--> présent en table et présent dans le fichier ==> si identique alors ne rien faire, sinon mettre à jour.

Son problème est de savoir comment dispatcher les données dans toutes ces tables.
Je pense que la solution la plus simple consiste à créer une table de travail contenant l'importation.
Ensuite, la manipulation de la recopie d'une table à une autre est assez simple à faire.

Deux cas :
--> soit la table est vide, alors on fait un insert ... select, comme l'exemple que j'ai donné.
--> soit la table contient déjà un existent, alors on fait un insert ... on duplicate update : https://dev.mysql.com/doc/refman/5.7...duplicate.html

Maintenant, s'il ne veut pas faire une table de travail, il faudra complexifier l'exemple du "load data local infile".
Il devra tenir compte de chaque chargement.
Il faudra aussi gérer les points de reprises en cas de plantage.

En admettant que le fichier excel ne possède aucune erreur, on peut désactiver certains contrôles pour gagner du temps.

@+
29/03/2016, 16h04
escartefigue

Plusieurs solutions sont possibles en effet

J'avais lu à tort qu'il s'agissait d'importer les données depuis la base MySQL vers excel, car c'est ce qui se fait le plus souvent.
C'est assez dangereux de faire le chemin inverse, mais bon si on prototype bien la solution pourquoi pas

Vous pourrez avoir les écarts suivants :
-1- les doublons présents dans le classeur d'origine et qui ont forcément été soit rejetés, soit mis à jour (dans les 2 cas on a moins de rows dans les tables que prévu)
-2- les rejets enfant + parent : données requises pour la création d'un enregistrement enfant présentes dans le classeur d'origine, mais données parentes absentes ou non conformes dans ce même classeur d'où rejet du parent + des enfants correspondants. Exemple : contrainte check du parent non satisfaite
-3- les rejets d'enfants seuls : cas de contrainte check non satisfaite pour l'enfant, mais le parent est bien créé

La difficulté consiste donc à garantir l'intégrité fonctionnelle du résultat, le rejet du parent ne pose pas de souci puisque tous les enfants sont naturellement rejetés grâce aux contraintes.
Par contre, les enfants manquants ne rejettent pas les parents, à vous de voir si c'est satisfaisant fonctionnellement !

Cas d'espèce : la facture dont il manquerait une ligne de facture est absolument illégale, à corriger obligatoirement donc dans ce genre de cas

il faut donc que le doc excel d'origine soit de qualité, ce qui est très difficile à garantir
29/03/2016, 16h49
malkimohamed

Salut à tous.

Citation:

Envoyé par Artemus24

Dois-je comprendre qu'à partir de votre fichier excel, vous devez remplir toutes vos tables, qui au préalable sont toutes vides ?

oui c'est bien ça Artemus24. mon base de données contient que des tables vide, et avec le fichier excel qui va s'alimenter

Citation:

Envoyé par Artemus24

Si vous utilisez des contraintes comme par exemple les clefs étrangères, il faut obligatoirement charger certaines tables avant les autres.

c'est ça le problème qui mon fait la tête :ptdr:. Dans ce cas la seul chose et de charger table par table. ok dans je panse de je dois créer un script php pour jouer avec l'insertion

merci très bien Artemus24.

Salut malkimohamed.

Voici un script mysql qui va récupérer un fichier de type ".csv".
Pour ce faire, j'utilise une table de travail de nom "trav".
Je dois remplir trois tables de noms "pere", "mere" et "fils".
J'ai bien sûr des contraintes comme deux clefs étrangères, et unicité dans le couple (pere_id ; mere_id).
Cela signifie que j'ai obligatoirement un seul fils.
Autre cas, j'ai un doublon ! J'ai fais le choix de récupérer le dernier tuple.

A vous de regarder comment j'ai fait, et si vous avez des questions à me poser, alors ouvrez le feu !

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
 
--------------
SET AUTOCOMMIT = 0
--------------
 
--------------
START TRANSACTION
--------------
 
--------------
DROP DATABASE IF EXISTS `base`
--------------
 
--------------
CREATE DATABASE IF NOT EXISTS `base`
        DEFAULT CHARACTER SET `latin1`
        DEFAULT COLLATE       `latin1_general_ci`
--------------
 
--------------
DROP TABLE IF EXISTS trav
--------------
 
--------------
CREATE TABLE trav (
  `id`    integer unsigned not null auto_increment primary key,
  `col1`  char(10)         not null,
  `col2`  char(10)         not null,
  `col3`  char(10)         not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS pere
--------------
 
--------------
CREATE TABLE pere (
  `id`   integer unsigned not null auto_increment primary key,
  `nom`  char(10)         not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS mere
--------------
 
--------------
CREATE TABLE mere (
  `id`    integer unsigned not null auto_increment primary key,
  `lang`  char(10)         not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
DROP TABLE IF EXISTS fils
--------------
 
--------------
CREATE TABLE fils (
  `id`       integer unsigned not null auto_increment primary key,
  `trad`     char(10)         not null,
  `pere_id`  integer unsigned not null,
  `mere_id`  integer unsigned not null,
  unique index `idx` (`pere_id`,`mere_id`),
  CONSTRAINT `FK_01` FOREIGN KEY (`pere_id`) REFERENCES `pere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_02` FOREIGN KEY (`mere_id`) REFERENCES `mere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA LOCAL INFILE 'fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED BY ','
     ENCLOSED BY '"'
     ESCAPED BY '\\' LINES
     TERMINATED BY '\r\n'
     IGNORE 0 LINES
     (`col1`,`col2`,`col3`)
--------------
 
--------------
COMMIT
--------------
 
--------------
select * from trav
--------------
 
+----+-------+----------+-------+
| id | col1  | col2     | col3  |
+----+-------+----------+-------+
|  1 | un    | anglais  | one   |
|  2 | un    | italien  | uno   |
|  3 | un    | allemand | eins  |
|  4 | deux  | anglais  | two   |
|  5 | deux  | italien  | due   |
|  6 | deux  | allemand | zwei  |
|  7 | trois | anglais  | tree  |
|  8 | trois | italien  | tre   |
|  9 | trois | allemand | drei  |
| 10 | trois | anglais  | three |
+----+-------+----------+-------+
--------------
insert into `pere` (`nom`)
  select distinct col1 from `trav`
--------------
 
--------------
COMMIT
--------------
 
--------------
select * from pere
--------------
 
+----+-------+
| id | nom   |
+----+-------+
|  1 | un    |
|  2 | deux  |
|  3 | trois |
+----+-------+
--------------
insert into `mere` (`lang`)
  select distinct col2 from `trav`
--------------
 
--------------
COMMIT
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | lang     |
+----+----------+
|  1 | anglais  |
|  2 | italien  |
|  3 | allemand |
+----+----------+
--------------
insert into `fils` (`pere_id`, `mere_id`, `trad`)
  select p.id, m.id, t.col3
    from       `trav` as t
    inner join `pere` as p
    on p.nom = t.col1
 
    inner join `mere` as m
    on m.lang = t.col2
 
  on duplicate key update trad = values(trad)
--------------
 
--------------
COMMIT
--------------
 
--------------
select * from fils order by mere_id, pere_id
--------------
 
+----+-------+---------+---------+
| id | trad  | pere_id | mere_id |
+----+-------+---------+---------+
|  1 | one   |       1 |       1 |
|  2 | two   |       2 |       1 |
|  3 | three |       3 |       1 |
|  4 | uno   |       1 |       2 |
|  5 | due   |       2 |       2 |
|  6 | tre   |       3 |       2 |
|  7 | eins  |       1 |       3 |
|  8 | zwei  |       2 |       3 |
|  9 | drei  |       3 |       3 |
+----+-------+---------+---------+
--------------
drop table `trav`
--------------
 
--------------
select m.lang, p.nom, f.trad
from       `fils` as f
 
inner join `pere` as p
on p.id = f.pere_id
 
inner join `mere` as m
on m.id = f.mere_id
 
order by m.lang, p.id
--------------
 
+----------+-------+-------+
| lang     | nom   | trad  |
+----------+-------+-------+
| allemand | un    | eins  |
| allemand | deux  | zwei  |
| allemand | trois | drei  |
| anglais  | un    | one   |
| anglais  | deux  | two   |
| anglais  | trois | three |
| italien  | un    | uno   |
| italien  | deux  | due   |
| italien  | trois | tre   |
+----------+-------+-------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
 
Appuyez sur une touche pour continuer...

Et voici mon fichier.txt :

Code:

1
2
3
4
5
6
7
8
9
10
"un","anglais","one"
"un","italien","uno"
"un","allemand","eins"
"deux","anglais","two"
"deux","italien","due"
"deux","allemand","zwei"
"trois","anglais","tree"
"trois","italien","tre"
"trois","allemand","drei"
"trois","anglais","three"

Le doublon, c'est la dernière ligne de ce jeu d'essai. J'ai fait exprès de faire une faute au mot "three".

@+

29/03/2016, 17h50
malkimohamed

merci très très bien Monsieur Artemus24
j'arrivé à faire tout ça grâce a vous et escartefigue, vos information ma vraiment aidée merci encore :king:
19/04/2016, 13h25
Coeur de Pirat

Import Excel vers MySql Workbench

Bonjour la communauté !
Je suis débutante et j'utilise MySql workbench!
Ma question : dois-je avoir les mêmes colonnes (même nom de colonnes) dans ma table sur Mysql workbench avec le fichier Excel pour que l'import se fasse correctement ?
Merci pour votre réponse
20/04/2016, 17h07
Artemus24

Salut Coeur de Pirat.

Non, ce n'est pas nécessaire.

L'import, tu le fais comment ?

@+