[SOLVED] MySQL Load File and Apostrophe

MySQL Load File and Apostrophe

I have a text file which contain 2 field sepearated by | and record by new line

example:
L'EQUME|7A
Voley|18
L'olivier|158

i have a MySql Table with 3 column (id, name , val) //id autoincrement...

so i would like to use the mysql load file feature to insert the value into name and val but my main problem is the apostrophe while loading file ...

How to addslahes while querying via load file ?

LOAD DATA INFILE 'data.txt'
INTO TABLE table_name
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n'
(name, val);

Solution

You can use escaped by But remember escaped by and enclosed by should not be same

I am assuming that your want to enter single quotes value and your fields are enclosed by double quotes and first line should be ignore.

try something like this

LOAD DATA INFILE 'data.txt'
INTO TABLE table_name
FIELDS 
  TERMINATED BY '|'
  ENCLOSED BY '"'
  ESCAPED BY ''
LINES 
  TERMINATED BY '\r\n'
 IGNORE 1 LINES; //if you don't want to ignore first line than remove it