Erreur valeur NULL avec load data infile

Version imprimable

11/05/2016, 16h13
guen

Erreur valeur NULL avec load data infile
Bonjour

j'essaye désespérément d'importer un fichier texte dans une table par un LOAD DATA INFILE (avec un script php) et j'ai systématiquement la transformation de mes valeurs NULL en 0 pour les champs numériques alors que la valeur par défaut dans mysql est bien le NULL.
j'ai essayé des fichiers textes avec des zones vides dans les champs
```
-2127007880;10;;;-1971156970;2014;;;2014/5/10 00:00:00;2014/5/10 00:00:00;"web";
```
avec un script php

Code:

LOAD DATA INFILE '$fic_txt' INTO TABLE $nom_table_cible_temp CHARACTER SET latin1 FIELDS TERMINATED BY ';' ENCLOSED BY '\"' IGNORE 1 LINES

j'ai essayé aussi le script php

Code:

LOAD DATA INFILE '$fic_txt' INTO TABLE $nom_table_cible_temp CHARACTER SET latin1 FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '' IGNORE 1 LINES

ou encore cela :
```
-2127007880;10;\N;\N;-1971156970;2014;\N;\N;2014/5/10 00:00:00;2014/5/10 00:00:00;"web";
```
avec un script php

Code:

LOAD DATA INFILE '$fic_txt' INTO TABLE $nom_table_cible_temp CHARACTER SET latin1 FIELDS TERMINATED BY ';' ENCLOSED BY '\"' ESCAPED BY '\\\' IGNORE 1 LINES

j'ai également essayé de mettre une valeur NULL dans le fichier texte et un ESCAPED BY ''

enfin, je crois avoir essayé toutes les combinaisons...en vain...
je ne peux pas mettre un ESCAPED BY '\' ou ESCAPED BY '\\' dans mon script php car cela génère une erreur php (cela vient de là ?)
quand je fais un select into sur mon serveur, j'obtiens un fichier texte avec \N pour les valeurs null si cela peut servir à connaitre la configuration du serveur
voyez-vous une solution ?
merci par avance

Salut guen.

Essaye de faire un effort de présentation de tes messages, en mettant le code entre les balises [ code ] ... [ /code ].

Comment veux-tu que MySql sache quoi faire si tu ne précises pas, lors du chargement, ce que tu veux comme résultat ?
Voici un exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
--------------
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 (
  col_01     integer      default null,
  col_02     integer      default null,
  col_03     integer      default null,
  col_04     integer      default null,
  col_05     integer      default null,
  col_06     integer      default null,
  col_07     integer      default null,
  col_08     integer      default null,
  col_date1  datetime     default null,
  col_date2  datetime     default null,
  lib        varchar(255) default null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA LOCAL INFILE 'fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED BY ';'
     ENCLOSED BY ''
     ESCAPED BY '\\' LINES
     TERMINATED BY '\r\n'
     IGNORE 0 LINES
     (col_01, col_02, @3, @4, col_05, col_06, @7, @8, col_date1, col_date2, lib)
         SET col_03 = case @3 when '' then null else @3 end,
             col_04 = case @4 when '' then null else @4 end,
             col_07 = case @7 when '' then null else @7 end,
             col_08 = case @8 when '' then null else @8 end
--------------
 
--------------
select * from trav
--------------
 
+-------------+--------+--------+--------+-------------+--------+--------+--------+---------------------+---------------------+------+
| col_01      | col_02 | col_03 | col_04 | col_05      | col_06 | col_07 | col_08 | col_date1           | col_date2           | lib  |
+-------------+--------+--------+--------+-------------+--------+--------+--------+---------------------+---------------------+------+
| -2127007880 |     10 |   NULL |   NULL | -1971156970 |   2014 |   NULL |   NULL | 2014-05-10 00:00:00 | 2014-05-10 00:00:00 | web  |
| -2127007880 |     10 |      0 |   NULL | -1971156970 |   2014 |   NULL |      0 | 2014-05-10 00:00:00 | 2014-05-10 00:00:00 | web  |
| -2127007880 |     10 |      1 |      0 | -1971156970 |   2014 |      0 |   NULL | 2014-05-10 00:00:00 | 2014-05-10 00:00:00 | web  |
+-------------+--------+--------+--------+-------------+--------+--------+--------+---------------------+---------------------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
 
Appuyez sur une touche pour continuer...

11/05/2016, 21h23
guen

effectivement, mon message n'est pas très lisible...Désolé !
par contre, je ne comprends pas bien ton exemple. le SET ..then NULL doit être spécifié pour toutes les colonnes où l'on doit gérer l'insertion de valeurs nulles ??
si oui, c'est super pas pratique car j'ai bcp de colonnes concernées et bcp de tables différentes... cela veut dire qu'il faut que je construise un script php différent pour chaque table alors que j'avais mis un script unique (étant donné que les colonnes de mon fichier texte importé cale parfaitement avec les colonnes de la table mysql donc pas de mappage nécessaire...).
merci
guen

Salut guen.

Citation:

Envoyé par Guen

le SET ..then NULL doit être spécifié pour toutes les colonnes où l'on doit gérer l'insertion de valeurs nulles ??

Pour faire court, oui ! C'est cela que vous devez spécifier pour une colonne donnée :

Code:

SET col_03 = case @3 when '' then null else @3 end,

Votre question concernait le fait que vous vouliez mettre NULL quand vous aviez vide dans le fichier excel.
Normalement, la colonne est numérique et l'absence de valeur va se traduire par un zéro.
Comment voulez-vous que MySql sache convertir un vide en NULL si vous ne le lui précisez pas ?

Citation:

Envoyé par Guen

cela veut dire qu'il faut que je construise un script php différent pour chaque table alors que j'avais mis un script unique

Je ne voie pas où se trouve la difficulté.
D'après la formulation de votre problème, j'avais compris que l'on pouvait modifier le load data infile, mais pas le contenu du fichier Excel.

Sinon, il existe une autre solution, mais vous devez modifier tous vos fichiers Excel.
Au lieu de mettre vide dans le fichier Excel, vous devez préciser le NULL en mettant la chaîne \N.
Ainsi votre script MySql ne sera pas changé (ce que vous désirez), mais vous devrez revoir tous vos fichier Excel.

Exemple :

Code:

1
2
3
-2127007880;10;\N;\N;-1971156970;2014;\N;\N;2014/5/10 00:00:00;2014/5/10 00:00:00;web
-2127007880;10;0;\N;-1971156970;2014;\N;0;2014/5/10 00:00:00;2014/5/10 00:00:00;web
-2127007880;10;1;0;-1971156970;2014;0;\N;2014/5/10 00:00:00;2014/5/10 00:00:00;web

Et le résultat du script MySql :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
--------------
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 (
  col_01     integer      default null,
  col_02     integer      default null,
  col_03     integer      default null,
  col_04     integer      default null,
  col_05     integer      default null,
  col_06     integer      default null,
  col_07     integer      default null,
  col_08     integer      default null,
  col_date1  datetime     default null,
  col_date2  datetime     default null,
  lib        varchar(255) default null
) ENGINE=InnoDB
  DEFAULT CHARSET=`latin1` COLLATE=`latin1_general_ci`
  ROW_FORMAT=COMPRESSED
--------------
 
--------------
LOAD DATA LOCAL INFILE 'fichier.txt'
     INTO TABLE `trav`
     CHARACTER SET latin1
     FIELDS TERMINATED BY ';'
     ENCLOSED BY ''
     ESCAPED BY '\\' LINES
     TERMINATED BY '\r\n'
     IGNORE 0 LINES
--------------
 
--------------
select * from trav
--------------
 
+-------------+--------+--------+--------+-------------+--------+--------+--------+---------------------+---------------------+------+
| col_01      | col_02 | col_03 | col_04 | col_05      | col_06 | col_07 | col_08 | col_date1           | col_date2           | lib  |
+-------------+--------+--------+--------+-------------+--------+--------+--------+---------------------+---------------------+------+
| -2127007880 |     10 |   NULL |   NULL | -1971156970 |   2014 |   NULL |   NULL | 2014-05-10 00:00:00 | 2014-05-10 00:00:00 | web  |
| -2127007880 |     10 |      0 |   NULL | -1971156970 |   2014 |   NULL |      0 | 2014-05-10 00:00:00 | 2014-05-10 00:00:00 | web  |
| -2127007880 |     10 |      1 |      0 | -1971156970 |   2014 |      0 |   NULL | 2014-05-10 00:00:00 | 2014-05-10 00:00:00 | web  |
+-------------+--------+--------+--------+-------------+--------+--------+--------+---------------------+---------------------+------+
--------------
COMMIT
--------------
 
--------------
SET AUTOCOMMIT = 0
--------------
 
 
Appuyez sur une touche pour continuer...

Le NULL apparaît bien dans le select.

@+

13/05/2016, 09h22
guen

bonjour
c'est exactement ce que je veux faire dans votre exemple à savoir mettre \N dans mon fichier txt source et qu'il me récupère cela en NULL dans mysql sans avoir à préciser colonne par colonne de faire une quelconque conversion.
J'ai aucun souci pour modifier mes fichiers txt (et non excel... est-ce là la raison...?).
comme je le disais dans mon message initial, j'ai fait le test avec un fichier texte de ce type :

Code:

-2127007880;10;\N;\N;-1971156970;2014;

avec un script php du style

Code:

echo "LOAD DATA INFILE fic.txt INTO table TERMINATED BY ';'

Code:

ENCLOSED BY '\"' ESCAPED BY '\\\\'";

j'ai vérifié que mes caractères d'échappement étaient bien interprétés et que la consigne ESCAPED BY '\\' était envoyée à MySQL
et j'obtiens des 0 !
je n'y comprends rien !!!

Salut Guen.

Chez moi, ce sont des fichiers ".txt", au format de ce que sont les fichier Excel de type ".csv".

Si vous désirez récupérer les NULL :

1) mettre '\N' dans votre fichier ".txt".

2) indiquez le caractère échappement "escape" dans le "load data infile".
Code:

1 2 3 4 5 6 7 8 LOAD DATA LOCAL INFILE 'fichier.txt' INTO TABLE `trav` CHARACTER SET latin1 FIELDS TERMINATED BY ';' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 0 LINES
comme je l'ai fait dans mon exemple.

3) précisez aussi dans la table que vous désirez ce caractère NULL, comme je l'ai fait dans mon exemple.

Code:

col_01 integer default null,

Par défaut (vu que je ne l'ai pas précisé), le NULL est accepté.
Et j'ai même précisé que la valeur par défaut doit être NULL.

Si la colonne est à NOT NULL mysql force à "0", même avec un \N.

@+

merci de vous acharner sur mon cas mais... c'est exactement ce que je fais !
fichier texte :

Code:

-2127007880;10;\N;\N;-1971156970;2014;

script php :

Code:

LOAD DATA INFILE '/var/www/Data/Structures/export_exemple_1305160912/tabintervention.txt' INTO TABLE sync_tabintervention_temp CHARACTER SET latin1 FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE 1 LINES;

structure de table :
Code:

1 2 3 4 5 6 7 8 9 10 CREATE TABLE IF NOT EXISTS `sync_tabintervention_temp` ( `INTER_id` int(11) NOT NULL DEFAULT '0', `TYPINTER_id` int(11) DEFAULT NULL, `CIBLE_id` int(11) DEFAULT NULL, `MOTIV_id` int(11) DEFAULT NULL, `EXPL_id` int(11) DEFAULT NULL, `MILLESIME` int(11) DEFAULT NULL, `MAT_id` int(11) DEFAULT NULL, `LOC_id` int(11) DEFAULT NULL, `INTER_DateDebut` datetime DEFAULT NULL,
et j'ai des 0 dans mes colonnes CIBLE_id, MOTIV_id même si j'ai \N dans mon fichier texte...
cela ne peut pas venir d'une configuration du serveur MySQL ?
merci...

13/05/2016, 10h22
Artemus24

Salut Guen.

Citation:

Envoyé par Guen

merci de vous acharner sur mon cas mais... c'est exactement ce que je fais !

Pas exactement.

Code:

LOAD DATA INFILE '/var/www/Data/Structures/export_exemple_1305160912/tabintervention.txt' INTO TABLE sync_tabintervention_temp CHARACTER SET latin1 FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\' IGNORE 1 LINES;

Dans mon exemple, j'ai précisé "load data local infile", alors que vous ne mettez pas le "local" dans votre exemple.
Il ne va pas cherché votre fichier là où vous l'indiquez, mais dans le répertoire qui est précisé dans la directive "--secure-file-priv".

Votre fichier texte ne contient pas les guillemets comme vous l'indiquez dans le script ci-dessus.
Vous ne précisez pas, non plus, la fin de ligne sous Linux (mon exemple est fait sous windows).
Sous linux, mettre :

Code:

TERMINATED BY '\n'

Citation:

Envoyé par Guen

cela ne peut pas venir d'une configuration du serveur MySQL ?

Pour le fonctionnement correct du "load data local infile", Non.

Il y a deux paramètres concernant cet utilitaire, à préciser dans le fichier my.ini :

1) vous pouvez interdire de charger des fichiers qui sont locaux, en mettant :

Code:

local-infile=0

Donc soit vous mettez 1, et vous devez préciser dans la commande le paramètre "local".

2) vous pouvez préciser où vous allez récupérer vos fichiers à charger :

Code:

secure-file-priv = f:/wamp/tmp/

Si vous ne précisez pas le paramètre "local", il va chercher le fichier dans ce répertoire.

Citation:

Envoyé par Guen

et j'ai des 0 dans mes colonnes CIBLE_id, MOTIV_id même si j'ai \N dans mon fichier texte...

Combien avez-vous de lignes à charger ?
Et combien de lignes sont effectivement chargées ?
Avez-vous des messages d'anomalies ?
Quand vous avez des zéro au lieu des NULL, est-ce que les autres colonnes sont correctes ?

J'ai l'impression que votre chargement ne se fait pas correctement.

@+
13/05/2016, 10h49
guen

j'avais déjà fait des tests auparavant avec local sans succès du coup, je ne mets pas local mais il me trouve très bien mon fichier texte dans le répertoire du serveur que je lui donne car je l'ai uploadé d'abord.
vu qu'il trouve le fichier, cela ne doit pas venir de là je pense...
mon fichier texte ne contient pas de guillemet car je ne vous ai mis qu'un extrait sans valeur texte.. un extrait plus complet donne :

Code:

-2127007880;10;\N;\N;-1971156970;2014;\N;\N;2014/5/10 00:00:00;2014/5/10 00:00:00;"web";"WEEMAX DUO (20 l/ha)";"Herbicide";

avec du numérique au début (avec ou sans valeur), une date et du texte entre guillemet
effectivement pour la fin de ligne, je ne le précise pas mais comme il importe correctement chaque ligne, je ne m'en suis pas tracassé... d'ailleurs, je viens de l'ajouter et cela ne change rien
les autres valeurs non Nulles sont par contre importées tout à fait correctement.
il n'y a que ce \N qui ne passe pas en NULL...

Salut Guen.

Le seul point qui reste à vérifier, c'est l'interprétation du caractère d'échappement (escape).
Mettre une chaîne de caractères dans une colonne numérique donnera zéro comme résultat.

Faites un test dans une chaîne de caractères, en mettant "un\; deux\; trois\; quatre".
Si vous chargez correctement la chaîne de caractères, le point-virgule ne sera pas interprété comme séparateur de champs (field).

La syntaxe correcte et complète pour charger un fichier de type text dans une table MySql sous windows est :
Code:

1 2 3 4 5 6 7 8 LOAD DATA LOCAL INFILE 'fichier.txt' INTO TABLE `trav` CHARACTER SET latin1 FIELDS TERMINATED BY ';' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 0 LINES;
Le paramètre "OPTIONALLY" permet d'indiquer que les guillemets sont utilisés que sur les chaînes de caractères et pas ailleurs.
Sous linux, la valeur par défaut de "lines terminated" est "\n".
Donc il n'est pas nécessaire de le préciser.
La caractère d'échappement doit être doublé "\\".

Dernier point, êtes-vous certain de traiter vos scripts et votre fichier text dans le même charset ?

@+

13/05/2016, 12h24
guen

ah...... quand j'exécute mon script depuis la fenêtre SQL de phpmyadmin avec le fichier sur le serveur ça marche !!!
quand je le fais depuis mon script php...ça marche plus !!
un souci de charset alors ??
13/05/2016, 12h29
Artemus24

Ou des paramètres de ta connexion lorsque tu désires accéder à MySql / php.
13/05/2016, 12h58
guen
ou mais je ne sais pas sur de savoir vérifier les encodages...
sur phpmyadmin, il me dit :
- Jeu de caractères du serveur: UTF-8 Unicode (utf8)
par contre, je ne sais pas trouver cela pour les scripts php ? (pas trouvé dans phpinfo...)
si je l'ouvre sous notepad++, il apparait correctement en ansi mais pas en utf8.
je l'ai donc convertit en utf8 et remis sur le serveur
mais...rien changé...
pour la connexion PDO, j'ai vu que je pouvais mettre
Code:

1 2 3 4 $options = array( PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8' ); $db_client = new PDO ($dsn, $user, $pwd,$options);
j'ai essayé avec utf8 et latin1 pour SET NAMES mais ça ne change rien non plus
je craque !!