Importer des données depuis des fichiers csv dans MySQL

**nrpfc** · 04/10/2012, 14h08

Bonjours,

j'ai bien suivis vos conseil et avec l'aide du forum sur les MCD et les schéma, j'ai reorganisé ma base de donnée.

Donc je me retrouve avec une belle base bien pensée (image 1), j'ai remplis toute les tables en dehors de "relever_mesure".

JE vous explique vite fait mon probleme, novice sur MySQL j'ai pour projet de stage de monter une base de donnée a partir de fichier *.csv (extrait d'un de ces fichiers joint "janvier_2012.zip").

Il faut que je recupere les infos pour les reinjecter dans la ma table "relever_mesure", pour le moment j'arrive a isoler une partie de la table, j'ai un premier soucis quand je veux la reinjecter dans la table "relever_mesure" c'est que je n'ai pas d'id_calendrier dans mes fichiers csv mais une datetime

le second soucis et que j'ai un fichier *.csv qui arrive tout les mois avec toute ces données et il faudrait donc que je crée une procedure pour faciliter la remonté des données car apres mon stage ce sera surement aux chercheurs de remonter eux meme les données.

Mes fichiers csv ont 84 colonnes : station, date_heure_locale et de 0 a 82 ( les 83 capteurs).

Ces informations doivent remontés dans une table « relever_mesure » qui se compose de 4 colonnes : id_calendrier_calendrier, id_station_station, id_capteur_capteur et valeur.

id_station_station correspond à la colonne station
id_capteur_capteur correspond au numéro de colonne capteur (de 0 a 82)
valeur correspond à la colonne capteur
id_calendrier_calendrier correspond à date_heure_locale (mais il va falloir une jonction ou quelques choses dans le genre)

Les fichiers csv que je dois remonter arrivent une fois par mois et font donc 84 colonnes sur 90 000 lignes maximum (en moyenne 84 000).

merci de m'aider a prendre la bonne voix pour écrire ma requête

Si quelqu'un pouvait m'orienter dans la bonne direction ce serait parfait

Merci d'avance

**nrpfc** · 05/10/2012, 11h02

alors voila le début de mon raisonnement:

avec cette formule j’extrais les valeurs, la date_heure et la station pour le capteur 0

Code :

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

SELECT `id_station`,`date_heure`,`0` FROM `import`;

après si je veux l’insérer dans ma table « relever_mesure »

Code :

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

1
2
3
INSERT INTO `relever_mesure`(`valeur`, `id_calendrier_calendrier`, `id_station_Stations`, `id_capteur_capteur`) 
SELECT `0`,`id_station`,`date_heure` 
FROM `import`

bon la j'ai un probleme deja les colonnes n'ont pas le meme nom.
la colonne "0" deviens la colonne "valeur",
la colonne "id_station" deviens la colonne "id_station_Stations",
la colonne "id_capteur_capteur" n'existe pas mais dans cette exemple doit avoir pour valeur 0,
et enfin la colonne "date_heure" deviens la colonne "id_calendrier_calendrier" qui n'est pas sur le meme format

donc j'en ai deduit ça a l'air de marcher

Code :

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

1
2
3
INSERT INTO `relever_mesure`(`valeur`, `id_calendrier_calendrier`, `id_station_Stations`,`id_capteur_capteur`) 
select `import`.`0` as `valeur`, `calendrier`.`id_calendrier`, `import`.`code_station`, '0' from `import`  
JOIN `calendrier` on `import`.`date_heure` = `calendrier`.`date_heure_locale`

donc nouveau probleme je ne veux pas que les valeurs (null) soit copier dans ma nouvelle table puisque si j'ai bien compris ce n'est pas utile et ça va la surcharger ...

la je bloque ...

je veux bien qu'on m'éclaire un peu si possible

**CinePhil** · 05/10/2012, 13h52

Choses à vérifier :
1) La colonne `calendrier`.`date_heure_locale` est bien de type DATETIME ?
2) La colonne `import`.`date_heure` est-elle aussi de type DATETIME ?
3) Cette dernière contient bien les bonnes valeurs que l'on retrouve dans calendrier ?
4) Y a t-il bien un id_capteur ayant la valeur 0 dans la table des capteurs ?

Tu pourrais poster la structure exacte des tables de la BDD modélisée (résultat de SHOW CREATE TABLE nom_de_la_table avec le texte complet) STP ?

**nrpfc** · 05/10/2012, 14h11

je suis bien contant de te retrouver ici

Choses à vérifier :
1) La colonne `calendrier`.`date_heure_locale` est bien de type DATETIME ?
2) La colonne `import`.`date_heure` est-elle aussi de type DATETIME ?
3) Cette dernière contient bien les bonnes valeurs que l'on retrouve dans calendrier ?
4) Y a t-il bien un id_capteur ayant la valeur 0 dans la table des capteurs ?

je repond oui a tes 4 questions et je te joins la structure des tables comme demandé

**CinePhil** · 05/10/2012, 14h15

Au lieu de joindre des PDF, tu pouvais te contenter de copier/coller le résultat de la requête dans des balises CODE !

Ajoute un index UNIQUE sur la colonne calendrier.date_heure_locale.
Ajoute un index simple sur la colonne import.id_station et un autre sur la colonne import.date_heure.

**nrpfc** · 05/10/2012, 14h23

desolé vieille habitude a moi "pourquoi faire simple quand on peut faire compliqué" que j'essaye de changer

Code :

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

1
2
3
4
5
CREATE TABLE `calendrier` (
 `id_calendrier` int(11) NOT NULL AUTO_INCREMENT,
 `date_heure_locale` datetime DEFAULT NULL,
 PRIMARY KEY (`id_calendrier`)
) ENGINE=InnoDB AUTO_INCREMENT=908063 DEFAULT CHARSET=utf8

Code :

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

CREATE TABLE `capteur` (  `id_capteur` int(11) NOT NULL,  `libelle` varchar(19) DEFAULT NULL,  `unite` varchar(6) DEFAULT NULL,  `courbe_ou_etat` int(11) DEFAULT NULL,  `chaine_protocole` varchar(3) DEFAULT NULL,  `chaine_protocole_ftp` varchar(3) DEFAULT NULL,  `nb_carac_protocole` int(11) DEFAULT NULL,  `gain_defaut` decimal(2,1) DEFAULT NULL,  `offset_defaut` int(11) DEFAULT NULL,  `valeur_proto_hs` int(11) DEFAULT NULL,  `valeur_base_hs` decimal(5,1) DEFAULT NULL,  `valeur_com_hs` int(11) DEFAULT NULL,  `ordre_etat_reseau` int(11) DEFAULT NULL,  `unite_wav` varchar(12) DEFAULT NULL,  `fichier_wav` varchar(12) DEFAULT NULL,  `couleur` int(11) DEFAULT NULL,  PRIMARY KEY (`id_capteur`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

Code :

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

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
CREATE TABLE `import` (
 `id_station` int(2) NOT NULL,
 `date_heure` datetime NOT NULL,
 `0` varchar(6) DEFAULT NULL,
 `1` varchar(6) DEFAULT NULL,
 `2` varchar(6) DEFAULT NULL,
 `3` varchar(6) DEFAULT NULL,
 `4` varchar(6) DEFAULT NULL,
 `5` varchar(6) DEFAULT NULL,
 `6` varchar(6) DEFAULT NULL,
 `7` varchar(6) DEFAULT NULL,
 `8` varchar(6) DEFAULT NULL,
 `9` varchar(6) DEFAULT NULL,
 `10` varchar(6) DEFAULT NULL,
 `11` varchar(6) DEFAULT NULL,
 `12` varchar(6) DEFAULT NULL,
 `13` varchar(6) DEFAULT NULL,
 `14` varchar(6) DEFAULT NULL,
 `15` varchar(6) DEFAULT NULL,
 `16` varchar(6) DEFAULT NULL,
 `17` varchar(6) DEFAULT NULL,
 `18` varchar(6) DEFAULT NULL,
 `19` varchar(6) DEFAULT NULL,
 `20` varchar(6) DEFAULT NULL,
 `21` varchar(6) DEFAULT NULL,
 `22` varchar(6) DEFAULT NULL,
 `23` varchar(6) DEFAULT NULL,
 `24` varchar(6) DEFAULT NULL,
 `25` varchar(6) DEFAULT NULL,
 `26` varchar(6) DEFAULT NULL,
 `27` varchar(6) DEFAULT NULL,
 `28` varchar(6) DEFAULT NULL,
 `29` varchar(6) DEFAULT NULL,
 `30` varchar(6) DEFAULT NULL,
 `31` varchar(6) DEFAULT NULL,
 `32` varchar(6) DEFAULT NULL,
 `33` varchar(6) DEFAULT NULL,
 `34` varchar(6) DEFAULT NULL,
 `35` varchar(6) DEFAULT NULL,
 `36` varchar(6) DEFAULT NULL,
 `37` varchar(6) DEFAULT NULL,
 `38` varchar(6) DEFAULT NULL,
 `39` varchar(6) DEFAULT NULL,
 `40` varchar(6) DEFAULT NULL,
 `41` varchar(6) DEFAULT NULL,
 `42` varchar(6) DEFAULT NULL,
 `43` varchar(6) DEFAULT NULL,
 `44` varchar(6) DEFAULT NULL,
 `45` varchar(6) DEFAULT NULL,
 `46` varchar(6) DEFAULT NULL,
 `47` varchar(6) DEFAULT NULL,
 `48` varchar(6) DEFAULT NULL,
 `49` varchar(6) DEFAULT NULL,
 `50` varchar(6) DEFAULT NULL,
 `51` varchar(6) DEFAULT NULL,
 `52` varchar(6) DEFAULT NULL,
 `53` varchar(6) DEFAULT NULL,
 `54` varchar(6) DEFAULT NULL,
 `55` varchar(6) DEFAULT NULL,
 `56` varchar(6) DEFAULT NULL,
 `57` varchar(6) DEFAULT NULL,
 `58` varchar(6) DEFAULT NULL,
 `59` varchar(6) DEFAULT NULL,
 `60` varchar(6) DEFAULT NULL,
 `61` varchar(6) DEFAULT NULL,
 `62` varchar(6) DEFAULT NULL,
 `63` varchar(6) DEFAULT NULL,
 `64` varchar(6) DEFAULT NULL,
 `65` varchar(6) DEFAULT NULL,
 `66` varchar(6) DEFAULT NULL,
 `67` varchar(6) DEFAULT NULL,
 `68` varchar(6) DEFAULT NULL,
 `69` varchar(6) DEFAULT NULL,
 `70` varchar(6) DEFAULT NULL,
 `71` varchar(6) DEFAULT NULL,
 `72` varchar(6) DEFAULT NULL,
 `73` varchar(6) DEFAULT NULL,
 `74` varchar(6) DEFAULT NULL,
 `75` varchar(6) DEFAULT NULL,
 `76` varchar(6) DEFAULT NULL,
 `77` varchar(6) DEFAULT NULL,
 `78` varchar(6) DEFAULT NULL,
 `79` varchar(6) DEFAULT NULL,
 `80` varchar(6) DEFAULT NULL,
 `81` varchar(6) DEFAULT NULL,
 `82` varchar(6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Code :

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

1
2
3
4
5
6
CREATE TABLE `stations` (
 `id_station` int(11) NOT NULL,
 `departement` varchar(10) NOT NULL,
 `localisation` varchar(30) NOT NULL,
 PRIMARY KEY (`id_station`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Code :

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

CREATE TABLE `relever_mesure` (  `valeur` varchar(25) DEFAULT NULL,  `id_calendrier_calendrier` int(11) NOT NULL,  `id_station_Stations` int(11) NOT NULL,  `id_capteur_capteur` int(11) NOT NULL,  PRIMARY KEY (`id_calendrier_calendrier`,`id_station_Stations`,`id_capteur_capteur`),  KEY `FK_relever_mesure_id_station_Stations` (`id_station_Stations`),  KEY `FK_relever_mesure_id_capteur_capteur` (`id_capteur_capteur`),  CONSTRAINT `FK_relever_mesure_id_capteur_capteur` FOREIGN KEY (`id_capteur_capteur`) REFERENCES `capteur` (`id_capteur`),  CONSTRAINT `FK_relever_mesure_id_calendrier_calendrier` FOREIGN KEY (`id_calendrier_calendrier`) REFERENCES `calendrier` (`id_calendrier`),  CONSTRAINT `FK_relever_mesure_id_station_Stations` FOREIGN KEY (`id_station_Stations`) REFERENCES `stations` (`id_station`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

**CinePhil** · 05/10/2012, 14h44

donc nouveau probleme je ne veux pas que les valeurs (null) soit copier dans ma nouvelle table puisque si j'ai bien compris ce n'est pas utile et ça va la surcharger ...

Et bien il suffit d'ajouter une restriction à la requête pour éliminer les NULL.
Au passage, je récris la requête pour la rendre plus agréable à lire car :
- les apostrophes inversées sont la plupart du temps inutiles ;
- l'utilisation d'alias pour les tables est fortement recommandé dès qu'il y a plus d'une table dans la requête. Cela facilite l'écriture et la lecture de celle-ci. Il faut ensuite utiliser systématiquement ces alias devant chaque colonne nommée pour savoir facilement de quelle table elle vient.

Code :

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

1
2
3
4
5
6
7
8
9
10
INSERT INTO relever_mesure(valeur, id_calendrier_calendrier, id_station_Stations, id_capteur_capteur)
SELECT i.`0` AS valeur,
	c.id_calendrier,
	i.code_station,
	0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`0` IS NOT NULL
-- ou bien
-- WHERE i.`0` <> '(null)'

Selon que les " (null) " du fichier CSV ont été interprété par MySQL comme le marqueur NULL ou comme une chaîne de caractères, tu utiliseras l'une ou l'autre version du WHERE.

Vide la table avant d'exécuter celle nouvelle requête.

**nrpfc** · 05/10/2012, 14h59

ok je vais tester ça, ça prend un temps fou a faire le tri, est ce normal ?

Si ça marche, il faudra que je repete la commande pour chaque capteur (du 0 au 82) et si j'ai bien compris je peux creer une procedure a partir de toute ces requetes.

je suis entrain d'essayer de l'ecrire je te la joins des que je pense etre juste

**nrpfc** · 05/10/2012, 15h18

bon voyons voir cette procedure
si j'ai bien compris ca devrait me donner un truc comme ça

Code :

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

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
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
DELIMITER |
 
CREATE PROCEDURE import_donnees
 
BEGIN
INSERT INTO relever_mesure(valeur, id_calendrier_calendrier, id_station_Stations, id_capteur_capteur)
SELECT i.`0` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`0` <> '(null)';
 
SELECT i.`1` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`1` <> '(null)';
 
SELECT i.`2` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`2` <> '(null)';
 
SELECT i.`3` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`3` <> '(null)';
 
SELECT i.`4` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`4` <> '(null)';
 
SELECT i.`5` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`5` <> '(null)';
 
SELECT i.`6` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`6` <> '(null)';
 
SELECT i.`7` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`7` <> '(null)';
 
SELECT i.`8` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`8` <> '(null)';
 
SELECT i.`9` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`9` <> '(null)';
 
SELECT i.`10` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`10` <> '(null)';
 
SELECT i.`11` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`11` <> '(null)';
 
SELECT i.`12` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`12` <> '(null)';
 
SELECT i.`13` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`13` <> '(null)';
 
SELECT i.`14` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`14` <> '(null)';
 
SELECT i.`15` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`15` <> '(null)';
 
SELECT i.`16` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`16` <> '(null)';
 
SELECT i.`17` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`17` <> '(null)';
 
SELECT i.`18` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`18` <> '(null)';
 
SELECT i.`19` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`19` <> '(null)';
 
SELECT i.`20` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`20` <> '(null)';
 
SELECT i.`21` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`21` <> '(null)';
 
SELECT i.`22` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`22` <> '(null)';
 
SELECT i.`23` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`23` <> '(null)';
 
SELECT i.`24` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`24` <> '(null)';
 
SELECT i.`25` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`25` <> '(null)';
 
SELECT i.`26` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`26` <> '(null)';
 
SELECT i.`27` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`27` <> '(null)';
 
SELECT i.`28` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`28` <> '(null)';
 
SELECT i.`29` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`29` <> '(null)';
 
SELECT i.`30` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`30` <> '(null)';
 
SELECT i.`31` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`31` <> '(null)';
 
SELECT i.`32` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`32` <> '(null)';
 
SELECT i.`33` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`33` <> '(null)';
 
SELECT i.`34` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`34` <> '(null)';
 
SELECT i.`35` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`35` <> '(null)';
 
SELECT i.`36` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`36` <> '(null)';
 
SELECT i.`37` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`37` <> '(null)';
 
SELECT i.`38` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`38` <> '(null)';
 
SELECT i.`39` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`39` <> '(null)';
 
SELECT i.`40` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`40` <> '(null)';
 
SELECT i.`41` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`41` <> '(null)';
 
SELECT i.`42` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`42` <> '(null)';
 
SELECT i.`43` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`43` <> '(null)';
 
SELECT i.`44` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`44` <> '(null)';
 
SELECT i.`45` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`45` <> '(null)';
 
SELECT i.`46` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`46` <> '(null)';
 
SELECT i.`47` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`47` <> '(null)';
 
SELECT i.`48` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`48` <> '(null)';
 
SELECT i.`49` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`49` <> '(null)';
 
SELECT i.`50` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`50` <> '(null)';
 
SELECT i.`51` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`51` <> '(null)';
 
SELECT i.`52` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`52` <> '(null)';
 
SELECT i.`53` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`53` <> '(null)';
 
SELECT i.`54` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`54` <> '(null)';
 
SELECT i.`55` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`55` <> '(null)';
 
SELECT i.`56` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`56` <> '(null)';
 
SELECT i.`57` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`57` <> '(null)';
 
SELECT i.`58` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`58` <> '(null)';
 
SELECT i.`59` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`59` <> '(null)';
 
SELECT i.`60` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`60` <> '(null)';
 
SELECT i.`61` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`61` <> '(null)';
 
SELECT i.`62` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`62` <> '(null)';
 
SELECT i.`63` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`63` <> '(null)';
 
SELECT i.`64` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`64` <> '(null)';
 
SELECT i.`65` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`65` <> '(null)';
 
SELECT i.`66` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`66` <> '(null)';
 
SELECT i.`67` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`67` <> '(null)';
 
SELECT i.`68` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`68` <> '(null)';
 
SELECT i.`69` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`69` <> '(null)';
 
SELECT i.`70` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`70` <> '(null)';
 
SELECT i.`71` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`71` <> '(null)';
 
SELECT i.`72` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`72` <> '(null)';
 
SELECT i.`73` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`73` <> '(null)';
 
SELECT i.`74` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`74` <> '(null)';
 
SELECT i.`75` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`75` <> '(null)';
 
SELECT i.`76` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`76` <> '(null)';
 
SELECT i.`77` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`77` <> '(null)';
 
SELECT i.`78` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`78` <> '(null)';
 
SELECT i.`79` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`79` <> '(null)';
 
SELECT i.`80` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`80` <> '(null)';
 
SELECT i.`81` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`81` <> '(null)';
 
SELECT i.`82` AS valeur,
        c.id_calendrier,
        i.code_station,
        0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`82` <> '(null)';
 
END |

ça fait quand meme une procedure vachement longue, quand je vois le temps que MySQL met a ne traiter que le tri du '0' qu'on a vu tout a l'heure ça me fait peur

**CinePhil** · 05/10/2012, 15h26

ok je vais tester ça, ça prend un temps fou a faire le tri, est ce normal ?

As-tu ajouté les index que j'ai mentionnés dans le message de 14h15 ?

ça fait quand meme une procedure vachement longue, quand je vois le temps que MySQL met a ne traiter que le tri du '0' qu'on a vu tout a l'heure ça me fait peur

Tu as compris le principe mais la procédure peut être simplifiée en utilisant une boucle de 0 à 82 et en préparant le texte de la requête avec en paramètre la variable sur laquelle on boucle.

Comme je ne suis pas spécialiste des procédures, je ne voudrais pas t'induire en erreur en t'indiquant un code faux. Il y a des plus chevronnés que moi dans cet exercice qui pourront t'aider.

Ils pourront aussi t'aider à écrire la procédure complète d'importation qui doit :
1) vider la table d'importation ;
2) importer le fichier CSV passé en paramètre de la procédure dans la table temporaire ;
3) transférer les mesures dans la table relever_mesure.

Tu n'auras plus qu'à tester tout ça et créer une interface graphique pour l'importation des données dans la BDD.

Au fait, pour donner du code dans les forums, ce n'est pas la balise QUOTE mais la balise CODE qu'il faut utiliser (bouton # de l'éditeur) !

**nrpfc** · 05/10/2012, 15h35

As-tu ajouté les index que j'ai mentionnés dans le message de 14h15 ?

oui oui ça a bien ete fait, si ce n'est que je n'ai pas pu mettre un index unique sur date_heure_locale du calendrier car il y a des doublons du au changement d'heure du coup j'ai mis un index simple.

merci pour tout, je rend honneur a ta patience

**CinePhil** · 05/10/2012, 15h48

Ben supprime les doublons sinon tu vas avoir plusieurs lignes pour une mesure par l'opération de jointure.

Si c'est vraiment lent, tu peux tester en ajoutant un index sur la colonne 0 (ou 1 ou 2...) mais ne le fait pas tout de suite pour toutes les colonnes car ça va alourdir beaucoup la BDD.

Autre solution : commence par une requête UPDATE pour transformer la chaîne '(null)' en vrai marqueur NULL. Avec le IS NOT NULL ça pourrait être plus rapide.

Si ça marche, il faudra penser à intégrer ça dans la procédure ou transformer le fichier CSV pour que MySQL interprète bien la chaîne comme un NULL lors de l'importation.

**nrpfc** · 05/10/2012, 16h09

avec l'index normal et les (null) changer en NULL ça n'a plus rien a voir, la remonté ce fait en quelques minutes a peine c'est cool

merci

En revanche j'ai regardé les histoires de boucle et j'ai un de peu de mal a saisir, si quelqu'un peut m'aider je suis preneur

**CinePhil** · 05/10/2012, 16h11

Quelques minutes pour un seul capteur ou pour tous ?

**nrpfc** · 05/10/2012, 16h26

pour le capteur 0 (celui qui a le plus de lignes) ca me fait moins de 3 minutes, j'ai un soucis dans ma procedure ça plante apres le capteur '0' du coup je cherche le pourquoi du comment parceque je me vois mal faire les 83 capteurs a la main ^^
d'ailleur meme probleme avec le update, je suis entrain de me documenter sur les boucles mais je comprend pas tout pour le moment ...

**CinePhil** · 05/10/2012, 16h32

Les fichiers csv que je dois remonter arrivent une fois par mois et font donc 84 colonnes sur 90 000 lignes maximum (en moyenne 84 000).

3 minutes pour 90 000 lignes, ça me semble énorme !
C'est un escargot ton serveur ?

**nrpfc** · 05/10/2012, 16h40

sur un petit plus que ça mon fichier import contient les 3 premiers mois de l'année 2012 soit plus de 250 000 lignes.
Mon serveur n'est pas tout jeune (DELL PowerEdge 2900) mais bon j'ai travaillé sur pire que ça a l'AFPA

**CinePhil** · 05/10/2012, 16h45

Tu pourrais fournir le résultat de ceci ?

Code :

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

1
2
3
4
5
6
7
EXPLAIN SELECT i.`0` AS valeur,
	c.id_calendrier,
	i.code_station,
	0
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`0` IS NOT NULL

Combien y a t-il de stations ?
Tu peux fournir le contenu des tables station et capteur ?

**nrpfc** · 05/10/2012, 16h53

je te fait ça ce soir des que je suis rentré, de toute façon je vais continuer a bosser sur latable ce week end histoire de mieux comprendre certaines fonction de MySQL.

Je te souhaite un bon week-end au cas ou on ne se croiserais pas sur le forum

**Fred_34** · 05/10/2012, 17h42

Ta procédure pourrait ressembler à ceci :

Code :

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

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
DELIMITER |
 
DROP PROCEDURE IF EXISTS import_donnees|
CREATE PROCEDURE import_donnees( nbCapteur INTEGER)
 
BEGIN
 
DECLARE iter INTEGER DEFAULT 0; 
 
iterwhile: WHILE iter <= nbCapteur DO 
 
  SET @myInsert:= CONCAT('
INSERT INTO relever_mesure(valeur, id_calendrier_calendrier, id_station_Stations, id_capteur_capteur)
SELECT i.`',iter,'`,
       c.id_calendrier,
       i.id_station,
       ',iter,' 
FROM import i
INNER JOIN calendrier c ON i.date_heure = c.date_heure_locale
WHERE i.`',iter,'` IS NOT NULL'); 
 
 
  prepare stmnt FROM @myInsert; 
  EXECUTE stmnt; 
  DEALLOCATE PREPARE stmnt;
 
 
   SET iter = iter + 1; 
END WHILE iterwhile;
END |
DELIMITER ;
CALL import_donnees(82);

Importer des données depuis des fichiers csv dans MySQL

SQL Procédural MySQL

Discussions similaires

Partager

Partager