PHP MySQL LOAD DATA INFILE from CSV File Upload

Question

I am trying to learn how to upload new records to MySQL table but seem to be failing so far. My attempt:

$uploaded = $_FILES["fileupload"]["name"];
`$try = mysqli_query($connect,"LOAD DATA INFILE IGNORE $uploaded INTO TABLE allrecords");`

I get an error. It's expected though as the code is a mess. I tried searching in Google but all seem to offer only inserting each records one by one to the database. I used a tag to select the Excel CSV file (exported from Excel) then on the receiving PHP file I use this query.

I use LOAD DATA INFILE IGNORE because I want to upload new records to the database but not include records who's primary key already exist in the database. Also I'm not sure how to do LOAD DATA INFILE and match each fields. Do I need to rename each column names/headers in Excel CSV to match the field names in database?

I think what he wants to do @TimBiegeleisen is to get those surrounding single quotes around the '/full/path/to/filename', and to ironically not ignore 1 lines, and tweak line 1 to be perfect — Drew
– Drew, Commented Aug 28, 2015 at 5:55
@Drew Post this as an answer. And the OP should have included error output. Then I would not have to take wild guesses. — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 28, 2015 at 5:57
yeah guys I just confirmed it over there on that Answer. If I don't do the single quotes I get error 1064. Put em back it, I get my export. OUTFILE and LOAD DATA INFILE are connected by the hip with syntax finickiness and share almost everything in common — Drew
– Drew, Commented Aug 28, 2015 at 6:03

Tim Biegeleisen · Accepted Answer · 2015-08-28 07:10:26Z

1

As @drew wisely mentioned in his comments above, you are missing single quotes around the file name in your LOAD DATA statement, which can mess things up. You may also be missing a few other things which would prevent it from working.

Try using this PHP code instead:

$uploaded = $_FILES["fileupload"]["name"];
`$try = mysqli_query($connect,"LOAD DATA INFILE IGNORE '$uploaded' INTO TABLE allrecords FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY ',,,\r\n'");`

Here is that LOAD DATA command as separate (readable) query:

LOAD DATA INFILE IGNORE '$uploaded'
INTO TABLE allrecords
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY ',,,\r\n'

Note carefully that the double quote in OPTIONALLY ENCLOSED BY has to be escaped so PHP doesn't choke.

And as another aside comment, LOAD DATA is fairly primitive with regard to what it can do handling data. It is intended as a workhorse to bring in raw data to MySQL. For complex filtering and manipulations of your raw data, you are probably better off doing the work in MySQL proper.

edited Aug 28, 2015 at 7:10

answered Aug 28, 2015 at 5:48

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

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

9 Comments

Jin Pards Over a year ago

Thanks Tim and Drew, I will try this now and get back to you :)

Jin Pards Over a year ago

what did you mean better off doing the work in MySQL proper? Did you mean INSERT each rows one by one in a loop? I was going to do that first but I thought it would make the process run very slow?

Tim Biegeleisen Over a year ago

I mean that if you have complex rules for selectively retaining/excluding certain records, you are better off just reading in everything in one shot and then cleaning up the table inside MySQL where you have a wealth of tools to help you do that.

Jin Pards Over a year ago

Using:

$try = mysqli_query($connect,"LOAD DATA INFILE IGNORE '$uploaded' INTO TABLE allrecords FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ',,,\r\n'");

I get error:

Parse error: syntax error, unexpected '' LINES TERMINATED BY '' (T_CONSTANT_ENCAPSED_STRING) in /home/hidden/public_html/subfolder/loader.php on line 41

Tim Biegeleisen Over a year ago

I forgot to escape the double quotes. Check my updated answer. Sorry for the confusion.

|

Collectives™ on Stack Overflow

PHP MySQL LOAD DATA INFILE from CSV File Upload

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related