Instruction LOAD DATA LOCAL INFILE

Bonjour,

J'ai trouvé sur le forum l'instruction LOAD DATA LOCAL INFILE pour importer un gros fichier csv dans une base mysql.

mais je rencontre une difficulté qui est à priori liée à ma version de MariaDB de ce que je comprends.

Voici mon instruction :

Code:

---

1 2 3 4 5 6 7 8 9

-> LOAD DATA LOCAL INFILE "import.csv" -> INTO TABLE import -> FIELDS -> TERMINATED BY ';' -> OPTIONAlLY ENCLOSED BY '"' -> ESCAPED BY '\\' -> LINES -> TERMINATED BY '\r\n' -> IGNORE 1 LINES;

---

et voici le message d'erreur que j'obtiens :

Citation:

---

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'mysql
LOAD DATA LOCAL INFILE "import.csv"
INTO TABLE import
FIELDS
TERMIN' at line 1

---

Une idée sur la syntaxe a utiliser ?
Merci de votre aide.
Mut

Salut Mutig.

Voici le fichier "fichier.csv" à charger dans la table de travail :

Code:

---

1 2 3

\N;"10/5/2014 15:33:17";"web" 125;"15/5/2014 23:53:59";"alpha\; beta\; gamma" -247;\N;"zéro"

---

Et 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

-------------- 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_num integer null default null, col_date datetime null default null, col_char varchar(255) null 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 ';' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' IGNORE 0 LINES (@F1, @F2, @F3) set col_num = trim(@F1), col_date = str_to_date(trim(@F2), '%d/%c/%Y %H:%i:%s'), col_char = trim(@F3) --------------   -------------- select * from trav --------------   +---------+---------------------+--------------------+ | col_num | col_date | col_char | +---------+---------------------+--------------------+ | NULL | 2014-05-10 15:33:17 | web | | 125 | 2014-05-15 23:53:59 | alpha; beta; gamma | | -247 | NULL | zéro | +---------+---------------------+--------------------+ -------------- COMMIT --------------   -------------- SET AUTOCOMMIT = 0 --------------   Appuyez sur une touche pour continuer...

---

@+