Import d'un fichier csv dans ma base de données

**steph44470** · 23/11/2020, 18h50

Bonjour, je récupere un fichier csv comme suit :

A;donnée1;données2;données3;2019;1.2
B;donnée1;données2;données3;2020;3.4
C;donnée1;données2;données3;2021;5.6
D;donnée1;données2;données3;2022;7.8
E;donnée1;données2;données3;2023;9.10

je lance l'instruction suivante :

Code :

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

1
2
 
LOAD DATA INFILE  fichier.cvs INTO TABLE test FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r'

Je souhaite que cela soit importé comme suit :

Code :

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

1
2
3
4
5
6
7
8
 
id | lettre | données                            | date |
----------------------------------------------------------
1  |A       |donnée1;données2;données3;2019;1.2  | now () |
2  |B       |donnée1;données2;données3;2019;3.4  | now () |
3  |C       |donnée1;données2;données3;2019;5.6  | now () |
4  |D       |donnée1;données2;données3;2019;7.8  | now () |
5  |E       |donnée1;données2;données3;2019;9.10  | now () |

mais je n'y arrive pas .

pourriez-vous m'aider

Merci

**escartefigue** · 23/11/2020, 19h00

Faut il comprendre que 3 valeurs du fichier CSV séparées par un ";" doivent alimenter une seule colonne de la table cible ?
Si c'est le cas, il faut modifier le fichier pour que ces 3 valeurs ne soient plus séparées par un point virgule mais pas un autre délimiteur.

Et surtout, c'est inquiétant quant au contenu de la colonne cible.

Pouvez-vous expliquer la nature de cette colonne fournir quelques exemples de contenu ?

**steph44470** · 23/11/2020, 21h08

Le système sert de passe-place entre 2 applis. C est un vieux devoir qui fonctionne très bien avec des petits fichiers mais maintenant il faudrait utiliser des fichiers beaucoup plus gros. Fichier csv de quelques millions de lignes donc impossible d'intégrer en php

**escartefigue** · 23/11/2020, 21h13

Ca ne répond pas à la question : quelle est la vocation fonctionnelle de cette colonne (autrement dit, qu'est-ce que les utilisateurs font avec son contenu ?), pouvez-vous communiquer un extrait (une vingtaine de lignes suffit) du contenu de cette table et en particulier de la colonne sensée recueillir les valeurs "donnée1;données2;données3;"

**steph44470** · 24/11/2020, 09h24

Bonjour,
Pour l'utilisation des données, je n'en sais rien.
L'application récupère les données à partir d'un fichier csv
exemple :

A;donnée1;données2;données3;2019;1.2
B;donnée1;données2;données3;2020;3.4
C;donnée1;données2;données3;2021;5.6
D;donnée1;données2;données3;2022;7.8
E;donnée1;données2;données3;2023;9.10

Effectue quelques contrôles : ligne vide, données non pertinentes, etc...
Puis insère les données dans une table Sql .
exemple :

 
id | lettre | données                            | date |
----------------------------------------------------------
1  |A       |donnée1;données2;données3;2019;1.2  | 2020-11-24 08:00:00 |
2  |B       |donnée1;données2;données3;2019;3.4  | 2020-11-24 08:00:00 |
3  |C       |donnée1;données2;données3;2019;5.6  | 2020-11-24 08:00:00|
4  |D       |donnée1;données2;données3;2019;7.8  | 2020-11-24 08:00:00|
5  |E       |donnée1;données2;données3;2019;9.10  | 2020-11-24 08:00:00|

Ensuite un batch exécuté via un crontab crée un fichier qui est récupéré par une tierce application.

le fichier généré est comme suit :

A;donnée1;données2;données3;2019;1.2
B;donnée1;données2;données3;2019;3.4
C;donnée1;données2;données3;2019;5.6
D;donnée1;données2;données3;2019;7.8
E;donnée1;données2;données3;2019;9.10

Comme je l'expliquais avant, le système fonctionne très bien mais la durée du traitement pour l'importation des données est beaucoup trop longue si je passe par le PHP, je préfèrerais utiliser un Shell avec le load data infile qui lui va beaucoup plus vite.

**Artemus24** · 24/11/2020, 15h27

Salut à tous.

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
--------------
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)     not null,
  `col2`  varchar(255)     not null,
  `date`  date             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA INFILE 'E:/30.MySql/01.Exercices/Ex_21/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1, @F2, @F3, @F4, @F5, @F6)
     set col1 = trim(@F1),
         col2 = concat(@F2,@F3,@F4,@F5,@F6),
         date = current_date
--------------
 
--------------
select * from trav
--------------
 
+----+------+---------------------------------+------------+
| id | col1 | col2                            | date       |
+----+------+---------------------------------+------------+
|  1 | A    | donnée1données2données320191.2  | 2020-11-24 |
|  2 | B    | donnée1données2données320203.4  | 2020-11-24 |
|  3 | C    | donnée1données2données320215.6  | 2020-11-24 |
|  4 | D    | donnée1données2données320227.8  | 2020-11-24 |
|  5 | E    | donnée1données2données320239.10 | 2020-11-24 |
+----+------+---------------------------------+------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

@+

**steph44470** · 24/11/2020, 16h13

Bonjour,
c'est tout à fait cela que je voulais.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
LOAD DATA INFILE 'E:/30.MySql/01.Exercices/Ex_21/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1, @F2, @F3, @F4, @F5, @F6)
     set col1 = trim(@F1),
         col2 = concat(@F2,@F3,@F4,@F5,@F6),
         date = current_date

Merci beaucoup, je teste et je résolu mais cela me parait me convenir tout à fait .

**escartefigue** · 24/11/2020, 16h15

Dans les questions qui précèdent, il est demandé de conserver les ";" dans la colonne "données", ce n'est plus le cas

**steph44470** · 24/11/2020, 16h22

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
 
LOAD DATA INFILE 'E:/30.MySql/01.Exercices/Ex_21/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1, @F2, @F3, @F4, @F5, @F6)
     set col1 = trim(@F1),
         col2 = concat(@F2,";",@F3,";",@F4,";",@F5,";",@F6),
         date = current_date

et c'est corrigé mais pas testé

**Artemus24** · 24/11/2020, 16h27

Salut à tous.

Envoyé par Escartefigue

Dans les questions qui précèdent, il est demandé de conserver les ";" dans la colonne "données", ce n'est plus le cas

Bien vu !

Pour résoudre le problème, il suffit de rajouter le point-virgule dans la fonction concar().

Je corrige le problème :

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
--------------
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)     not null,
  `col2`  varchar(255)     not null,
  `date`  date             not null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA INFILE 'E:/30.MySql/01.Exercices/Ex_21/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1, @F2, @F3, @F4, @F5, @F6)
     set col1 = trim(@F1),
         col2 = concat(@F2,';',@F3,';',@F4,';',@F5,';',@F6),
         date = current_date
--------------
 
--------------
select * from trav
--------------
 
+----+------+-------------------------------------+------------+
| id | col1 | col2                                | date       |
+----+------+-------------------------------------+------------+
|  1 | A    | donnée1;données2;données3;2019;1.2  | 2020-11-24 |
|  2 | B    | donnée1;données2;données3;2020;3.4  | 2020-11-24 |
|  3 | C    | donnée1;données2;données3;2021;5.6  | 2020-11-24 |
|  4 | D    | donnée1;données2;données3;2022;7.8  | 2020-11-24 |
|  5 | E    | donnée1;données2;données3;2023;9.10 | 2020-11-24 |
+----+------+-------------------------------------+------------+
--------------
COMMIT
--------------
 
Appuyez sur une touche pour continuer...

Est-ce bon maintenant ?

@+

**steph44470** · 24/11/2020, 16h31

bonjour, c'est important les ' et non les "

**Artemus24** · 24/11/2020, 16h35

Les guillemets servent à interpréter le contenu, tandis que l'apostrophe ne le fait pas.

Pour revenir au point-virgule, dans le "load infile", il sert comme séparateur de champs.
Si vous désirez délimiter correctement vos champs, vous devez utiliser autre chose que le point-virgule, par exemple la virgule en tant que séparateur de la première et seconde colonne.

Code :

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

1
2
3
4
5
A,donnée1;données2;données3;2019;1.2
B,donnée1;données2;données3;2020;3.4
C,donnée1;données2;données3;2021;5.6
D,donnée1;données2;données3;2022;7.8
E,donnée1;données2;données3;2023;9.10

@+

**steph44470** · 03/12/2020, 13h53

Bonjour,

le traitement fonctionne parfaitement par contre, comment faire si un champ est vide, car cela recherche 6 champs alors que pour une ligne , je n'ai que 5 champs.

merci

**Artemus24** · 04/12/2020, 06h30

Salut steph44470.

La solution dépend du caractère séparateur de champs du fichier ".csv".
Si c'est le point-virgule, il y a six champs à mettre dans deux colonnes.
Si c'est la virgule alors il y deux champs à mettre dans deux colonnes.

Que signifie exactement vide ?
Dois-je comprendre que le vide doit être remplacé par une chaine de caratères, disons par défaut.

Voici le jeu d'essai :

Code :

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

1
2
3
4
5
A;donnée1;données2;données3;2019;1.2
B;donnée1;données2;données3;2020;3.4
;donnée1;données2;données3;2021;5.6
D;donnée1;données2;données3;2022;7.8
E;donnée1;;données3;2023;9.10

Et voici le résultat :

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
--------------
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)     NOT NULL,
  `col2`  varchar(255)         NULL,
  `date`  date             NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA INFILE 'E:/30.MySql/01.Exercices/Ex_21/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1,@F2,@F3,@F4,@F5,@F6)
     set col1 = trim(if(@F1='','--------',@F1)),
         col2 = concat(if(@F2='','--------',@F2),';',if(@F3='','--------',@F3),';',if(@F='','--------',@F4),';',if(@F5='','--------',@F5),';',if(@F6='','--------',@F6)),
         date = current_date
--------------
 
--------------
select * from trav
--------------
 
+----+----------+------------------------------------+------------+
| id | col1     | col2                               | date       |
+----+----------+------------------------------------+------------+
|  1 | A        | donnée1;données2;données3;2019;1.2 | 2020-12-03 |
|  2 | B        | donnée1;données2;données3;2020;3.4 | 2020-12-03 |
|  3 | -------- | donnée1;données2;données3;2021;5.6 | 2020-12-03 |
|  4 | D        | donnée1;données2;données3;2022;7.8 | 2020-12-03 |
|  5 | E        | donnée1;--------;donnée3;2023;9.10 | 2020-12-03 |
+----+----------+------------------------------------+------------+
--------------
COMMIT
--------------
 
 
Appuyez sur une touche pour continuer...

@+

**steph44470** · 04/12/2020, 10h08

Bonjour Artemus,

En fait, selon le code du 1er champ, on a x données séparé par un point virgule.
Le 1er champ est obligatoire
Donc il faudrait mettre

Code :

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

1
2
3
4
 
set col1 = concat(trim(@F1),';'),
         col2 = concat(if(@F2='','',concat(@F2,';')),if(@F3='','',concat(@F3,';')),if(@F4='','',concat(@F4,';')),if(@F5='','',concat(@F5,';')),if(@F6='','',concat(@F6,';'))),
         date = current_date

merci beaucoup ,

**Artemus24** · 05/12/2020, 04h20

Salut steph44470.

Il existe plusieurs solutions pour répartir les champs selon le séparateur utilisé.

Vous pouvez utiliser la virgule comme séparateur et considérer que vous avez que deux champs :

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
LOAD DATA INFILE 'E:/30.MySql/01.Exercices/Ex_21/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ','
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1,@F2)
     set col1 = trim(@F1),
         col2 = trim(@F2),
         date = current_date
--------------
 
--------------
select * from trav
--------------
 
+----+------+------------------------------------+------------+
| id | col1 | col2                               | date       |
+----+------+------------------------------------+------------+
|  1 | A    | donnée1;données2;données3;2019;1.2 | 2020-12-05 |
|  2 | B    | donnée1;données2;données3;2020;3.4 | 2020-12-05 |
|  3 | C    | donnée1;données2;données3;2021;5.6 | 2020-12-05 |
|  4 | D    | donnée1;données2;données3;2022;7.8 | 2020-12-05 |
|  5 | E    | donnée1;données2;donnée3;2023;9.10 | 2020-12-05 |
+----+------+------------------------------------+------------+

Ou bien utiliser le point-virgule comme séparateur et considérer que vous avez six champs.

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
LOAD DATA INFILE 'E:/30.MySql/01.Exercices/Ex_21/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ';'
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1,@F2,@F3,@F4,@F5,@F6)
     set col1 = trim(@F1),
         col2 = trim(concat(@F2,';',@F3,';',@F4,';',@F5,';',@F6)),
         date = current_date
--------------
 
--------------
select * from trav
--------------
 
+----+------+------------------------------------+------------+
| id | col1 | col2                               | date       |
+----+------+------------------------------------+------------+
|  1 | A    | donnée1;données2;données3;2019;1.2 | 2020-12-05 |
|  2 | B    | donnée1;données2;données3;2020;3.4 | 2020-12-05 |
|  3 | C    | donnée1;données2;données3;2021;5.6 | 2020-12-05 |
|  4 | D    | donnée1;données2;données3;2022;7.8 | 2020-12-05 |
|  5 | E    | donnée1;données2;donnée3;2023;9.10 | 2020-12-05 |
+----+------+------------------------------------+------------+

C'est la solution que je vous ai proposée.

A cela, je me pose la question sur la façon dont vous répartissez vos champs dans vos colonnes ?
A cela, vous répondez :

Envoyé par steph44470

Pour l'utilisation des données, je n'en sais rien.

Ce qui revient à dire que la colonne "col1" sert comme identifiant et la colonne "col2" comme stockage des données.
Mais vous n'en savez pas plus.

Que ce passe-t-il si vous avez des données en quantités variables ?
Peut-être que le cas ne se présente pas car l'exemple donné est formaté pour cinq champs.

Par contre, vous avez soulevé la question de l'absence de données dans la colonne "col2".
Je vous ai indiqué un exemple, genre valeur par défaut en cas d'absence entre deux points virgules.
Pour cela, il faut tester la valeur reçue par :

Code :

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

if(@F2='','--------',@F2)

Vous nous indiqués que le point-virgule est bien le séparateur de vos champs dans le fichier ".csv", mais qu'en fait, vous avez un nombre de champs variable et qui dépend de la colonne "col1".
En fait, c'est le premier point-virgule qui va servir de séparateur mais pas les autres.
Voici un exemple de fichier ".csv" :

Code :

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

1
2
3
4
5
A;donnée1
B;donnée1;données2
C;donnée1;données2;données3
D;donnée1;données2;données3;données4
E;donnée1;données2;donnéee3;données4;données5

Ce qui donne :

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
LOAD DATA INFILE 'E:/30.MySql/01.Exercices/Ex_21/fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED            BY ''
            OPTIONALLY ENCLOSED   BY '\"'
            ESCAPED               BY '\\'
     LINES  TERMINATED            BY '\r\n'
     IGNORE 0 LINES
     (@F1)
     set col1 = left(trim(@F1),instr(trim(@F1),';')),
         col2 = right(trim(@F1),length(trim(@F1)) - instr(trim(@F1),';')),
         date = current_date
--------------
 
--------------
select * from trav
--------------
 
+----+------+---------------------------------------------+------------+
| id | col1 | col2                                        | date       |
+----+------+---------------------------------------------+------------+
|  1 | A;   | donnée1                                     | 2020-12-05 |
|  2 | B;   | donnée1;données2                            | 2020-12-05 |
|  3 | C;   | donnée1;données2;données3                   | 2020-12-05 |
|  4 | D;   | donnée1;données2;données3;données4          | 2020-12-05 |
|  5 | E;   | donnée1;données2;donnéee3;données4;données5 | 2020-12-05 |
+----+------+---------------------------------------------+------------+

Dans cet exemple, il n'y a pas de point-virgule somme séparateur (FIELDS TERMINATED BY).
C'est dans le remplissage de vos deux colonnes que le travail se fait.

La fonction instr() détermine la position du premier point-virgule.
Si vous ne désirez pas ce point-virgule dans la colonne "col1", il suffit de faire - 1.

@+

**steph44470** · 08/12/2020, 09h18

Bonjour Artemus24,

C'est tout à fait le dernier cas.
Je ne peux que m'incliner devant cette solution qui me sauve .

En plus, avec les explications , cela est super.

merci beaucoup et encore toutes mes félicitations.

Import d'un fichier csv dans ma base de données

Requêtes MySQL

Discussions similaires

Partager

Partager