LOAD DATA INFILE

[steelmanronald06]

Using codeigniter and having an issue:

 

$sql = "LOAD DATA LOCAL INFILE '".$_SERVER['DOCUMENT_ROOT']."/junk/2010-07-19_ddna_current_APJ.csv' INTO TABLE `pulled_openx_clicks` FIELDS TERMINATED BY '\\t' ENCLOSED BY '\"' ESCAPED BY '\\' LINES TERMINATED BY '\\n' IGNORE 1 LINES";

$data['MyError'] = 	$this->db->query($sql);

 

  Quote

Error Number: 1064

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\n' IGNORE 1 LINES' at line 1

 

LOAD DATA LOCAL INFILE '/var/www/vhosts/sapkdm.com/subdomains/openx/httpdocs/junk/2010-07-19_ddna_current_APJ.csv' INTO TABLE `pulled_openx_clicks` FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\n' IGNORE 1 LINES

 

Any insight on this would be great.  Also note that where i'm doing terminated by for fields and lines, it is using \\t and \\n.  I did have this set to \t and \n, but then the error read like this:

 

  Quote

Error Number: 1064

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' IGNORE 1 LINES' at line 1

 

LOAD DATA LOCAL INFILE '/var/www/vhosts/sapkdm.com/subdomains/openx/httpdocs/junk/2010-07-19_ddna_current_APJ.csv' INTO TABLE `pulled_openx_clicks` FIELDS TERMINATED BY '' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '' IGNORE 1 LINES

[steelmanronald06]

*bump*

[Maq]

You were closer the first time except you were missing a value for the TERMINATED BY.  Try this:

 

$sql = "LOAD DATA LOCAL INFILE '".$_SERVER['DOCUMENT_ROOT']."/junk/2010-07-19_ddna_current_APJ.csv' INTO TABLE `pulled_openx_clicks` 
  FIELDS TERMINATED BY '\t' ENCLOSED BY '\"' ESCAPED BY '\\' 
  LINES TERMINATED BY '\n' 
  IGNORE 1 LINES";

[steelmanronald06]

No dice.  I get this error:

 

  Quote

 

A Database Error Occurred

 

Error Number: 1064

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' IGNORE 1 LINES' at line 3

 

LOAD DATA LOCAL INFILE '/var/www/vhosts/sapkdm.com/subdomains/openx/httpdocs/junk/2010-07-19_ddna_current_APJ.csv' INTO TABLE `pulled_openx_clicks` FIELDS TERMINATED BY ' ' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY ' ' IGNORE 1 LINES

[steelmanronald06]

*bump*

[brianlange]

Try removing the "escaped by" statement. I removed this and it worked. Not sure why the syntax looks okay.

[steelmanronald06]

Okay, so that made the SQL statement valid.  Here is what I'm dealing with.  If I put the sql code directly into the console method in my controller (you can see this because I left the code but commented it out), it works fine and inserts the data into the database.  However if I move the SQL over to a method within the model class, create a method in the controller class to call that model method and then call that controller method within the console method, it only outputs a blank page.  Here is a pastebin of both of my classes.  Controller is listed first then the model.  The main method I'm using is the console method within the controller.

 

http://pastebin.com/R1YPxKDJ

 

 

Any advice/direction would be great.  I feel like it is something small, but I'm just not seeing it.  No errors are outputting and no errors are being written to my error log.