1

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);
Saty
  • 22,443
  • 7
  • 33
  • 51
Krasic
  • 137
  • 2
  • 14

1 Answers1

2

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
Ram Sharma
  • 8,676
  • 7
  • 43
  • 56
  • Actually i was looking to addslashes so after i can stripslashes when displaying the value (only the name) LOAD DATA INFILE 'data.txt' INTO TABLE table_name FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@name, val) SET name = QUOTE(@name) – Krasic Jul 04 '15 at 13:04
  • 'code' LOAD DATA INFILE 'data.txt' INTO TABLE table_name FIELDS TERMINATED BY '|' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (@name, val) SET name = QUOTE(@name) – Krasic Jul 04 '15 at 13:07
  • You do not included field enclosed and escaped as per my answer – Ram Sharma Jul 04 '15 at 13:09
  • Ok i used your query data ok , however is normal when i browser table result i see L'EQUME instead of L\'EQUME or is normal ? thank you – Krasic Jul 04 '15 at 13:22