How to load data to db from csv using LOAD DATA INFILE in drupal

Question

I have tried the below code for laoding the inline file in drupal db

 $sql = "LOAD DATA INFILE '".db_escape_string($this->file_name).
         "' INTO TABLE `".$this->table_name.
         "` FIELDS TERMINATED BY '".db_escape_string($this->field_separate_char).
         "' OPTIONALLY ENCLOSED BY '".db_escape_string($this->field_enclose_char).
         "' ESCAPED BY '".db_escape_string($this->field_escape_char).
         "' ".
         ($this->use_csv_header ? " IGNORE 1 LINES " : "")
         ."(`".implode("`,`", $this->arr_csv_columns)."`)";
  $res = db_query($sql);
  $this->error = mysql_error();

I got the error as but the file was present in the folder

user warning: File 'http:\localhost\example\sites\default\files\example_1.csv' not found (Errcode: 22) query: LOAD DATA INFILE 'http://localhost/example/sites/default/files/example_1.csv' INTO TABLE temp_21_10_2010_06_38_00 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' ESCAPED BY '\' IGNORE 1 LINES (PROGRAMNAME,OFFLINE,ONLINE,INSTOCK) in C:\wamp\www\example\sites\all\modules\importmodule\Quick_CSV_import.php on line 65.

On line 65 the above php code $res = db_query($sql); is present.

Jon Black · Accepted Answer · 2010-10-22 08:29:28Z

3

+50

you need to specify the full/absolute or a relative path to your csv file not a URL

load data infile '/myproj/imports/example_1.csv' into table...

load data infile 'c:\\myproj\\imports\\example_1.csv' into table...

suggest you check the manual again http://dev.mysql.com/doc/refman/5.1/en/load-data.html

answered Oct 22, 2010 at 8:29

Jon Black

16.6k5 gold badges45 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ugesh Gali Over a year ago

The above same code works fine in php but i cannot get it through drupal.

Jeremy French Over a year ago

You would have a different path from drupal than you would in a stand alone php script.

Joaquín L. Robles Over a year ago

In Windows I had to use '/' slashes as path separator

MatTheCat · Accepted Answer · 2010-10-22 07:38:39Z

0

Perhaps is it due to open_files_limit ?

answered Oct 22, 2010 at 7:38

MatTheCat

18.8k6 gold badges59 silver badges70 bronze badges

Collectives™ on Stack Overflow

How to load data to db from csv using LOAD DATA INFILE in drupal

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related