phphopzter phphopzter - 6 months ago 22
PHP Question

mysql load data infile inserting double quotes

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
FIELDS
TERMINATED BY ','
LINES
TERMINATED BY '\\n'
IGNORE 1 LINES
(`column1`, `column2`, `column3`, `column4`..)";

$query = mysqli_query($link, $mysql) or die(mysqli_error($link));


Thanks

Answer

I am guessing from your question that your infile values are something along the lines of this,

"column1","column2","column3","column4"
"column1a","column2a","column3a","column4a"

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.

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Hope this helped!