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

Version imprimable

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

Citation:

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:

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:

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

23/11/2020, 19h00
escartefigue

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 ?
23/11/2020, 21h08
steph44470

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
23/11/2020, 21h13
escartefigue

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;"

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.

Salut à tous.

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
--------------
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...

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

Code:

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 .

24/11/2020, 16h15
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 :weird:

Code:

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é :D

Salut à tous.

Citation:

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:

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 ?

@+

24/11/2020, 16h31
steph44470

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

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:

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
@+

03/12/2020, 13h53
steph44470

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

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:

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:

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...

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:

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 ,

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:

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:

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 :

Citation:

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:

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:

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:

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.

@+

08/12/2020, 09h18
steph44470

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.