LOAD DATA LOCAL INFILE MySQL/PHP issue

Question

Here is my MySQL table structure

id    |   tracking_number   |   order_id

Here is the structure of the CSV file: (Sometimes the order_id is missing, and this seems to be causing issues)

"1R2689Y603406","33097"
"1R2689Y603404","33096"
"1R2689Y603414",
"1R2689Y603429","33093"
"1R2689Y603452",

Here is my current SQL Query which isn't working: (The file is being uploaded, and is being read correctly, it's the query itself which is causing issues)

        $sql = 'LOAD DATA LOCAL INFILE "'.$_FILES['my_file']['tmp_name'].'" 
        INTO TABLE table_tracking
        FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY "\""
        LINES TERMINATED BY "\n" 
        (tracking_number,order_id)';

        mysql_query($sql) or die(myqsl_error());

What is wrong with my query? Thanks for any help!

Edit: Changed CSV structure to represent missing data that sometimes occurs. Changed query to match the one I am now using

What's the actual error you're having? Or the actual unexpected behavior? — Romain
– Romain, Commented Oct 10, 2011 at 16:00
try mysql_query(...) or die (mysql_error()); and tell us what you see. — voldomazta
– voldomazta, Commented Oct 10, 2011 at 16:05
PHP is going to convert the \n to an actual linebreak before MySQL ever sees this query, you'd need \\n in this case. The default for LINES TERMINATED BY is a linebreak anyway, so you don't actually have to specify it. — John Flatness
– John Flatness, Commented Oct 10, 2011 at 16:13
Note that MySQL uses forward slashes / for filenames, even on windows. — Johan
– Johan, Commented Oct 10, 2011 at 16:21

Laurent' · Accepted Answer · 2011-10-10 16:06:26Z

2

Just trying here (not 100% confident) but did you try adding the destination columns like this:

mysql_query("LOAD DATA LOCAL INFILE '".$_FILES['my_file']['tmp_name']."' 
     INTO TABLE table_tracking FIELDS TERMINATED BY ',' 
     OPTIONALLY ENCLOSED BY '\"' 
     LINES TERMINATED BY '\n'
     (tracking_number,order_id)");

You have three columns in your table and are only providing two data.

answered Oct 10, 2011 at 16:06

Laurent'

2,6311 gold badge16 silver badges23 bronze badges

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

2 Comments

Johan Over a year ago

You still need to make sure that $_FILES contains a valid filepath with forward slashes as required by MySQL.

Laurent' Over a year ago

@Johan yes. That was the part I was not confident in. kylex: nice :)

voldomazta · Accepted Answer · 2011-10-10 17:25:06Z

1

This worked for me:

$sql = 'load data local infile "c:/users/ramon/desktop/1.csv"
        into table test fields terminated by ","
        optionally enclosed by "\""
        lines terminated by "\n"';

mysql_query($sql) or die(myqsl_error());

You also probably need to make sure that your third column has a default value.

edited Oct 10, 2011 at 17:25

answered Oct 10, 2011 at 16:07

voldomazta

1,3401 gold badge10 silver badges22 bronze badges

4 Comments

voldomazta Over a year ago

i have a reason to belive it's an escaping issue.

kylex Over a year ago

That seems to work except I'm running into an issue. Some of the data is incomplete (missing the order number) and that seems to throw things off.

voldomazta Over a year ago

You probably just need to put a default value to the field that's missing the data so that its not a prerequisite.

kylex Over a year ago

Actually it appears that I need \r\n as the line terminator.

tobias_k · Accepted Answer · 2013-12-20 11:57:50Z

0

Instead of $_FILES['my_file']['tmp_name'], try

dirname(__FILE__)."/".$_FILES['my_file']['tmp_name']

dirname(__FILE__) will give the full directory path like 'C:/path/to/directory'.

edited Dec 20, 2013 at 11:57

tobias_k

83.1k12 gold badges130 silver badges186 bronze badges

answered Dec 20, 2013 at 11:39

Bitopan Das

1

Collectives™ on Stack Overflow

LOAD DATA LOCAL INFILE MySQL/PHP issue

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related