load data infile is not working

Question

I am trying to batch upload by csv file. my csv file located in webroot/CSV/data.csv. Data are as follows:

name,email
santo, abc@gmail.com

my code:

$sql = "LOAD DATA INFILE '$filepath'
    INTO TABLE customers
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"' 
    LINES TERMINATED BY ',,,\\r\\n'
    IGNORE 1 LINES 
    (name, email)";
    $this->Customer->query($sql);

here

$filepath = WWW_ROOT . 'CSV' . DS.'data.csv';

Getting error:

C:\server\mysql\data\serverhtdocsdemo-home25appwebrootCSVdata.csv' not found (Errcode: 2 "No such file or directory")

I check the file it exists and file location in sql seems to okay:

SQL Query: LOAD DATA INFILE 'C:\server\htdocs\demo-home25\app\webroot\CSV\data.csv' INTO TABLE customers FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ',,,\r\n' IGNORE 1 LINES (name, email)

What is the wrong in my code?

Are you sure you have defined `DS` to something, Its not a PHP defined value you have to set it yourself. Also you need a `DS` between `WWW_ROOT . 'CSV'` i.e.`WWW_ROOT . DS . 'CSV'` Once you have actually defined it. I suggest using unix forward slashes in DS as well rather than DOS backslashes. — RiggsFolly, Dec 16 '15 at 13:52
What is exact content of the $filepath variable before $sql? — Jan Rydrych, Dec 16 '15 at 14:06
I set the path of csv file to $filepath variable as I explained, i.e: $filepath = WWW_ROOT . 'CSV' . DS.'data.csv'; @Rydrych — Abdus Sattar Bhuiyan, Dec 16 '15 at 14:10
WWW_ROOT add slash automatically as I know. I also check it. RiggsFolly — Abdus Sattar Bhuiyan, Dec 16 '15 at 14:11

score 2 · Accepted Answer · edited May 23 '17 at 12:31

2

You tried to use "LOAD DATA LOCAL INFILE"?

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

In your case:

$sql = "LOAD DATA LOCAL INFILE '$filepath'
    INTO TABLE customers
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '\"' 
    LINES TERMINATED BY ',,,\\r\\n'
    IGNORE 1 LINES 
    (name, email)";
    $this->Customer->query($sql);

"If LOCAL is specified, the file is read by the client program on the client host and sent to the server." from Documentation

Answered in https://stackoverflow.com/a/14133740/2925795

edited May 23 '17 at 12:31

Community

1
1

answered Dec 16 '15 at 13:59

user2925795

400
10
29

Thanks. Now it shows another error: PDOStatement::execute() [pdostatement.execute]: LOAD DATA LOCAL INFILE forbidden [CORE\Cake\Model\Datasource\DboSource.php, line 460] – Abdus Sattar Bhuiyan Dec 16 '15 at 14:07
Check the user's permissions in mysql. If not resolved, here there are other similar cases [link](http://stackoverflow.com/questions/7638090/load-data-local-infile-forbidden-in-php) – user2925795 Dec 17 '15 at 15:44

score 0 · Answer 2 · answered Dec 16 '15 at 13:57

I had the same issue. Sometimes it happens that your mysql server and client understand file locations differently.

Add LOCAL modifier (as LOAD DATA LOCAL INFILE) to your query

$sql = "LOAD DATA LOCAL INFILE '$filepath'
INTO TABLE customers
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"' 
LINES TERMINATED BY ',,,\\r\\n'
IGNORE 1 LINES 
(name, email)";
$this->Customer->query($sql);

load data infile is not working

2 Answers2