Base de données csv volumineuse

**IMS64** · 10/10/2018, 03h13

Bonjour j'essaye depuis quelques jour d'importer une base de donnée de 8 Go en csv sur wamp. J'ai paramétré wamp pour accepter les fichiers volumineux.
Par contre, petit souci, seule la première ligne de l'en-tête est prise en compte.
Si vous pouviez m'aiguillez pour trouver des informations.
Merci d'avance pour toute information.

**CinePhil** · 10/10/2018, 16h48

Avec les gros fichiers, il vaut mieux passer en console que via phpMyAdmin parce qu'on atteint vite le timeout de php ou d'Apache.

**Artemus24** · 11/10/2018, 12h39

Salut IMS64.

Envoyé par IMS64

j'essaye depuis quelques jour d'importer une base de donnée de 8 Go en csv sur wamp.

Voulez-vous dire que vous chargez vos tables, une par une, en utilisant "load data local infile" ?
Parce que on ne peut pas charger une base de données directement à partir d'un fichier au format csv.

Pour importer une base de données, vous devez en premier lieu effectuer un export depuis un autre SGBD MySql.
Dans PhpMyAdmin, vous devez utiliser l'onglet "import", en sélectionnant aupréalable la base de données à charger.

Envoyé par IMS64

J'ai paramétré wamp pour accepter les fichiers volumineux.

Qu'avez-vous fait ?

Si c'est un problème de timeout, voici le paramétrage à faire :

Code :

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

1
2
3
4
5
6
7
# ------------------------------#
#     Timeout Configuration     #
# ------------------------------#
 
connect-timeout     = 600
interactive-timeout = 600
wait-timeout        = 600

Ici 600 correspond à 60 secondes * 10 soit 10 minutes.

Envoyé par IMS64

Par contre, petit souci, seule la première ligne de l'en-tête est prise en compte.

Raisonner dans le vide ne pas servir à grand chose.

Comment avez-vous procéder pour charger votre base de données ?

@+

**IMS64** · 13/10/2018, 01h26

Bonjour et merci pour vos retours vous etes vraiment sympa.
Je suis passé par phpmyadmin puis j ai créer une base de donné et j ai importé le fichier en selectionnant le type de fichier (.csv).
Dans ce fichier (.csv) la premiere ligne est l’entete de la bdd. Ce que j ai trouvé etonnant c’est que seule cette premiere ligne a été enregistré.
Je me retrouve pour le coup avec une bdd dont l entete est bien remplis mais sans aucune donnée.
Je vais suivre vos conseil et tester par la console ainsi que regarder le time out.
👍👌

**IMS64** · 04/11/2018, 10h25

Salut a tous je reviens vers vous après avoir régler le problème du au paramétrage de phpmyadmin.
Toujours le même problème via l'interface phpmyadmin.
Du coup je me penche sur la méthode LOAD DATA INFILE.

Petit soucis.
si vous avez le temps de jeter un coup d'oeil....

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Code :

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

1
2
3
4
5
6
LOAD DATA LOCAL INFILE 'C:\Users\pc\Downloads\sirene_201804_L_M\sirc-17804_9075_61173_201804_L_M_20180501_023750698.csv'
INTO TABLE table1
OPTIONALLY ENCLOSED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(SIREN,NIC,L1_NORMALISEE,L2_NORMALISEE,L3_NORMALISEE,L4_NORMALISEE,L5_NORMALISEE,L6_NORMALISEE,L7_NORMALISEE,L1_DECLAREE,L2_DECLAREE,L3_DECLAREE,L4_DECLAREE,L5_DECLAREE,L6_DECLAREE,L7_DECLAREE,NUMVOIE,INDREP,TYPVOIE,LIBVOIE,CODPOS,CEDEX,RPET,LIBREG,DEPET,ARRONET,CTONET,COMET,LIBCOM,DU,TU,UU,EPCI,TCD,ZEMET,SIEGE,ENSEIGNE,IND_PUBLIPO,DIFFCOM,AMINTRET,NATETAB,LIBNATETAB,APET700,LIBAPET,DAPET,TEFET,LIBTEFET,EFETCENT,DEFET,ORIGINE,DCRET,DDEBACT,ACTIVNAT,LIEUACT,ACTISURF,SAISONAT,MODET,PRODET,PRODPART,AUXILT,NOMEN_LONG,SIGLE,NOM,PRENOM,CIVILITE,RNA,NICSIEGE,RPEN,DEPCOMEN,ADR_MAIL,NJ,LIBNJ,APEN700,LIBAPEN,DAPEN,APRM,ESS,DATEESS,TEFEN,LIBTEFEN,EFENCENT,DEFEN,CATEGORIE,DCREN,AMINTREN,MONOACT,MODEN,PRODEN,ESAANN,TCA,ESAAPEN,ESASEC1N,ESASEC2N,ESASEC3N,ESASEC4N,VMAJ,VMAJ1,VMAJ2,VMAJ3,DATEMAJ,)

Erreur

Analyse statique :
1 erreurs trouvées lors de l'analyse.

Type d'énoncé non reconnu. (near "ENCLOSED BY" at position 141)

Requête SQL :

Code :

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

LOAD DATA LOCAL INFILE 'C:\Users\pc\Downloads\sirene_201804_L_M\sirc-17804_9075_61173_201804_L_M_20180501_023750698.csv' INTO TABLE table1 ENCLOSED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES (SIREN,NIC,L1_NORMALISEE,L2_NORMALISEE,L3_NORMALISEE,L4_NORMALISEE,L5_NORMALISEE,L6_NORMALISEE,L7_NORMALISEE,L1_DECLAREE,L2_DECLAREE,L3_DECLAREE,L4_DECLAREE,L5_DECLAREE,L6_DECLAREE,L7_DECLAREE,NUMVOIE,INDREP,TYPVOIE,LIBVOIE,CODPOS,CEDEX,RPET,LIBREG,DEPET,ARRONET,CTONET,COMET,LIBCOM,DU,TU,UU,EPCI,TCD,ZEMET,SIEGE,ENSEIGNE,IND_PUBLIPO,DIFFCOM,AMINTRET,NATETAB,LIBNATETAB,APET700,LIBAPET,DAPET,TEFET,LIBTEFET,EFETCENT,DEFET,ORIGINE,DCRET,DDEBACT,ACTIVNAT,LIEUACT,ACTISURF,SAISONAT,MODET,PRODET,PRODPART,AUXILT,NOMEN_LONG,SIGLE,NOM,PRENOM,CIVILITE,RNA,NICSIEGE,RPEN,DEPCOMEN,ADR_MAIL,NJ,LIBNJ,APEN700,LIBAPEN,DAPEN,APRM,ESS,DATEESS,TEFEN,LIBTEFEN,EFENCENT,DEFEN,CATEGORIE,DCREN,AMINTREN,MONOACT,MODEN,PRODEN,ESAANN,TCA,ESAAPEN,ESASEC1N,ESASEC2N,ESASEC3N,ESASEC4N,VMAJ,VMAJ1,VMAJ2,VMAJ3,DATEMAJ,)

MySQL a répondu :

#1064 - Erreur de syntaxe près de 'ENCLOSED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(SIREN,NIC,L1_NORMALI' à la ligne 3

**CosmoKnacki** · 04/11/2018, 11h33

Il ne faut pas te laisser coincer par une simple erreur de syntaxe. Dés que ça t'arrive, tu dois avoir le réflexe de vérifier ta syntaxe dans la documentation officielle (ce qui généralement règle le problème en moins de temps qu'il ne faut pour le dire):LOAD DATA INFILE

**Artemus24** · 04/11/2018, 20h33

Salut IMS64.

Je suis sous mysql 8.0.13.

1) Pour utiliser "load data local infile", vous devez paramétrer votre fichier "my.ini".

Code :

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

1
2
3
4
5
6
7
8
9
# ------------------------------ #
#     Load data local infile     #
# ------------------------------ #
 
local-infile = 1
 
secure-file-priv  = E:/30.MySql
 
loose-local-infile = on

Après la modification du fichir "my.ini", ne pas oublier de redémarrer votre serveur MySql.

2) ne plus utiliser le terme "local".
Autrement dit, vous devez écrire "load data infile".

3) vous devez créer une table de travail pour chaque fichier ".csv" que vous désirer charger.
Ensuite, vous allez répartir vos données dans les différentes tables de votre base de données définitive.

Je rappelle que vous devez définir les liens définissants vos relations dans votre base de données, à partir de vos fichiers plats ".csv".

4) voici un exemple :

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
--------------
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`  varchar(255)         null,
  `col2`  varchar(255)         null,
  `col3`  varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA INFILE 'E:/30.MySql/40.Load/Ex_04/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ','
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (`col1`,`col2`,`col3`)
--------------
 
--------------
select * from trav
--------------
 
+----+--------+----------+--------------+
| id | col1   | col2     | col3         |
+----+--------+----------+--------------+
|  1 | quatre | Anglais  | four         |
|  2 | quatre | Allemand | vier         |
|  3 | quatre | Italien  | quattro      |
|  4 | trois  | anglais  | three        |
|  5 | cinq   | Allemand | fünf         |
|  6 | cinq   | Italien  | cinque       |
|  7 | cinq   | Anglais  | five         |
|  8 | NULL   | NULL     | vingt-quatre |
|  9 | six    | Anglais  | six          |
| 10 | six    | Allemand | sechs        |
| 11 | six    | Italien  | sei          |
| 12 | un     | Espagnol | uno          |
| 13 | deux   | Espagnol | dos          |
| 14 | trois  | Espagnol | tres         |
| 15 | quatre | Espagnol | cuatro       |
| 16 | cinq   | Espagnol | cinquo       |
| 17 | six    | Espagnol | seis         |
+----+--------+----------+--------------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS pere
--------------
 
--------------
CREATE TABLE pere
( `id`      integer unsigned not null auto_increment primary key,
  `nombre`  varchar(255)     not null,
  index `idx` (`nombre`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `pere` (`nombre`) value ('un'),('deux'),('trois')
--------------
 
--------------
select * from pere
--------------
 
+----+--------+
| id | nombre |
+----+--------+
|  2 | deux   |
|  3 | trois  |
|  1 | un     |
+----+--------+
--------------
insert into `pere` (`nombre`)
  select  distinct t.col1
    from  `trav` as t
   where  not exists (select 1 from `pere` as p where p.nombre = t.col1)
     and  t.col1 is not null
--------------
 
--------------
select * from pere
--------------
 
+----+--------+
| id | nombre |
+----+--------+
|  5 | cinq   |
|  2 | deux   |
|  4 | quatre |
|  6 | six    |
|  3 | trois  |
|  1 | un     |
+----+--------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS mere
--------------
 
--------------
CREATE TABLE mere
( `id`      integer unsigned not null auto_increment primary key,
  `langue`  varchar(255)     not null,
  index `idx` (`langue`)
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `mere` (`langue`) value ('Allemand'), ('Italien'), ('Anglais')
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | langue   |
+----+----------+
|  1 | Allemand |
|  3 | Anglais  |
|  2 | Italien  |
+----+----------+
--------------
insert into `mere`  (`langue`)
   select distinct  t.col2
              from  `trav` as t
  where not exists  (select 1 from `mere` as m  where m.langue = t.col2)
               and  t.col2 is not null
--------------
 
--------------
select * from mere
--------------
 
+----+----------+
| id | langue   |
+----+----------+
|  1 | Allemand |
|  3 | Anglais  |
|  4 | Espagnol |
|  2 | Italien  |
+----+----------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS fille
--------------
 
--------------
CREATE TABLE fille
( `id`          integer unsigned not null auto_increment primary key,
  `pere_id`     integer unsigned not null,
  `mere_id`     integer unsigned not null,
  `traduction`  varchar(255)     not null,
  unique index `idx` (`pere_id`,`mere_id`),
  CONSTRAINT `FK_PERE` FOREIGN KEY (`pere_id`) REFERENCES `pere` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_MERE` 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
--------------
 
--------------
insert into `fille` (`pere_id`,`mere_id`,`traduction`) value
  (1, 1, 'eins'), (2, 1, 'zwei'), (3, 1, 'drei'),
  (1, 2, 'uno'),  (2, 2, 'due'),  (3, 2, 'tre'),
  (1, 3, 'one'),  (2, 3, 'two'),  (3, 3, 'tree')
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       1 |       2 | uno        |
|  5 |       2 |       2 | due        |
|  6 |       3 |       2 | tre        |
|  7 |       1 |       3 | one        |
|  8 |       2 |       3 | two        |
|  9 |       3 |       3 | tree       |
+----+---------+---------+------------+
--------------
insert into `fille` (`pere_id`,`mere_id`,`traduction`)
    select  p.id,
            m.id,
            t.col3
      from  `trav` as t
 
inner join  `pere` as p
        on  p.nombre = t.col1
 
inner join  `mere` as m
        on  m.langue = t.col2
 
     where  t.col3 is not null
 
        on  duplicate key update traduction = values(traduction)
--------------
 
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       1 |       2 | uno        |
|  5 |       2 |       2 | due        |
|  6 |       3 |       2 | tre        |
|  7 |       1 |       3 | one        |
|  8 |       2 |       3 | two        |
|  9 |       3 |       3 | three      |
| 10 |       4 |       3 | four       |
| 11 |       4 |       1 | vier       |
| 12 |       4 |       2 | quattro    |
| 13 |       5 |       1 | fünf       |
| 14 |       5 |       2 | cinque     |
| 15 |       5 |       3 | five       |
| 16 |       6 |       3 | six        |
| 17 |       6 |       1 | sechs      |
| 18 |       6 |       2 | sei        |
| 19 |       1 |       4 | uno        |
| 20 |       2 |       4 | dos        |
| 21 |       3 |       4 | tres       |
| 22 |       4 |       4 | cuatro     |
| 23 |       5 |       4 | cinquo     |
| 24 |       6 |       4 | seis       |
+----+---------+---------+------------+
--------------
alter table `fille` drop   column `id`
--------------
 
--------------
alter table `fille` add    column `id` integer first
--------------
 
--------------
set @id:=0
--------------
 
--------------
update `fille` set id=(@id:=@id+1) order by mere_id, pere_id
--------------
 
--------------
alter table `fille` change column `id` `id` integer unsigned auto_increment primary key first
--------------
 
--------------
describe fille
--------------
 
+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| id         | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| pere_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| mere_id    | int(10) unsigned | NO   | MUL | NULL    |                |
| traduction | varchar(255)     | NO   |     | NULL    |                |
+------------+------------------+------+-----+---------+----------------+
--------------
select * from fille
--------------
 
+----+---------+---------+------------+
| id | pere_id | mere_id | traduction |
+----+---------+---------+------------+
|  1 |       1 |       1 | eins       |
|  2 |       2 |       1 | zwei       |
|  3 |       3 |       1 | drei       |
|  4 |       4 |       1 | vier       |
|  5 |       5 |       1 | fünf       |
|  6 |       6 |       1 | sechs      |
|  7 |       1 |       2 | uno        |
|  8 |       2 |       2 | due        |
|  9 |       3 |       2 | tre        |
| 10 |       4 |       2 | quattro    |
| 11 |       5 |       2 | cinque     |
| 12 |       6 |       2 | sei        |
| 13 |       1 |       3 | one        |
| 14 |       2 |       3 | two        |
| 15 |       3 |       3 | three      |
| 16 |       4 |       3 | four       |
| 17 |       5 |       3 | five       |
| 18 |       6 |       3 | six        |
| 19 |       1 |       4 | uno        |
| 20 |       2 |       4 | dos        |
| 21 |       3 |       4 | tres       |
| 22 |       4 |       4 | cuatro     |
| 23 |       5 |       4 | cinquo     |
| 24 |       6 |       4 | seis       |
+----+---------+---------+------------+
--------------
COMMIT
--------------
 
--------------
DROP TABLE IF EXISTS poubelle
--------------
 
--------------
CREATE TABLE poubelle
( `id`    integer unsigned not null auto_increment primary key,
  `col1`  varchar(255)         null,
  `col2`  varchar(255)         null,
  `col3`  varchar(255)         null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
insert into `poubelle` (`col1`,`col2`,`col3`)
    select  col1,
            col2,
            col3
      from  `trav`
 
     where  col1 is null
        or  col2 is null
--------------
 
--------------
select * from poubelle
--------------
 
+----+------+------+--------------+
| id | col1 | col2 | col3         |
+----+------+------+--------------+
|  1 | NULL | NULL | vingt-quatre |
+----+------+------+--------------+
--------------
COMMIT
--------------
 
--------------
drop table `trav`
--------------
 
--------------
select      m.langue,
            p.nombre,
            f.traduction
      from  `fille` as f
 
inner join  `pere` as p
        on  p.id = f.pere_id
 
inner join  `mere` as m
        on  m.id = f.mere_id
--------------
 
+----------+--------+------------+
| langue   | nombre | traduction |
+----------+--------+------------+
| Allemand | un     | eins       |
| Allemand | deux   | zwei       |
| Allemand | trois  | drei       |
| Allemand | quatre | vier       |
| Allemand | cinq   | fünf       |
| Allemand | six    | sechs      |
| Italien  | un     | uno        |
| Italien  | deux   | due        |
| Italien  | trois  | tre        |
| Italien  | quatre | quattro    |
| Italien  | cinq   | cinque     |
| Italien  | six    | sei        |
| Anglais  | un     | one        |
| Anglais  | deux   | two        |
| Anglais  | trois  | three      |
| Anglais  | quatre | four       |
| Anglais  | cinq   | five       |
| Anglais  | six    | six        |
| Espagnol | un     | uno        |
| Espagnol | deux   | dos        |
| Espagnol | trois  | tres       |
| Espagnol | quatre | cuatro     |
| Espagnol | cinq   | cinquo     |
| Espagnol | six    | seis       |
+----------+--------+------------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
Appuyez sur une touche pour continuer...

@+

**IMS64** · 04/11/2018, 22h38

je file de ce pas sur le liens essayé de voir mon erreur de syntaxe...
merci pour vos retours...

Base de données csv volumineuse

MySQL

Discussions similaires

Partager

Partager