Hi I know this question is not only for the sake of mine but for the sake of everyone who uses a mysql LOAD DATA INFILE.
I just want to know why I am receiving this ("") double quotes every time I import data using load data infile? the data from csv has been successfully added in database but in instance there's a double quotes within the value. for example.
"value1" | "value2" | "value3"
Any help? here's my mysql script.
echo $mysql = "LOAD DATA LOCAL INFILE '".$path ."'
REPLACE INTO TABLE table
TERMINATED BY ','
TERMINATED BY '\\n'
IGNORE 1 LINES
(`column1`, `column2`, `column3`, `column4`..)";
$query = mysqli_query($link, $mysql) or die(mysqli_error($link));
I am guessing from your question that your infile values are something along the lines of this,
Since in your mysql statement you have only stated that the fields are terminated by a
, it will read a value upto the mysql engine finding a
,, hence the qoutes.
The mysql syntax for declaring if fields are enclosed by something is exactly like below;
FIELDS ENCLOSED BY '"'
Your new query would look like,
"LOAD DATA LOCAL INFILE '".$path ."' REPLACE INTO TABLE table FIELDS TERMINATED BY ',', ENCLOSED BY '"' LINES TERMINATED BY '\\n' IGNORE 1 LINES (`column1`, `column2`, `column3`, `column4`..)"
This tells the mysql engine to look for the characters in between the
" and take that value as the field value.
You can read more about it on the mysql website.
Hope this helped!