PHP script doesn't import CSV into MySQL

Question

# download the file off the internet
    $file = file_get_contents("http://localhost/sample.csv");
    $filepath = "C:/xampp/htdocs/test/file/sample.csv";
    file_put_contents($filepath, $file);
# load the data into sample table
    $pt1 = "LOAD DATA LOCAL INFILE ";
    $pt2 = "'/C:/xampp/htdocs/test/file/sample.csv' INTO TABLE sample ";
    $pt3 = "FIELDS TERMINATED BY ',' ENCLOSED BY '\"' ";
    $pt4 = "LINES TERMINATED BY '\r\n' ";
    $pt5 = "(col1,col2,col3,col4)";
    $sqlquerynew = $pt1.$pt2.$pt3.$pt4.$pt5;
    mysql_query($sqlquerynew);

This piece of code works on non-csv (well, I tested it with a text file instead).

Before this part gets run, I have to create a table. The table is now created, but no data is loaded. The file stated in the path exists.

What could be the problem?

Thanks

This is a sample csv I found online

"REVIEW_DATE","AUTHOR","ISBN","DISCOUNTED_PRICE"
"1985/01/21","Douglas Adams",0345391802,5.95
"1990/01/12","Douglas Hofstadter",0465026567,9.95
       ....... etc

Do you get an error message? have you tried removing the headers line? or add IGNORE 1 LINES after INTO TABLE sample — Yaniro
– Yaniro, Commented May 15, 2012 at 7:08
@Yaniro Thanks. I don't have any error message displayed (on browser). Yes I did, it still doesn't work. — user1012451
– user1012451, Commented May 15, 2012 at 7:10
What happens if you do this from the command line? (i mean mysql command prompt) — Yaniro
– Yaniro, Commented May 15, 2012 at 7:10
This is a question: Did you create table in advance or are you expecting to create table on the fly through LOAD DATA LOCAL INFILE? — user1260310
– user1260310, Commented May 18, 2012 at 21:57
@user1260310 There will be SQL query to create the tables before this part is ran. — user1012451
– user1012451, Commented May 20, 2012 at 9:12

CppLearner · Accepted Answer · 2012-05-15 07:25:51Z

1

Two problems

$pt2 = "'/C:/xampp/htdocs/test/file/sample.csv' INTO TABLE sample ";

Remove the / in front of C.

and secondly,

$pt5 = "(col1,col2,col3,col4)";

Make sure you have the right name for the columns. If you want to import * all columns, just remove it. It is also a good idea to remove the header in your case, because removing $pt5 you will succeed, but the header row will be added to the table.

answered May 15, 2012 at 7:25

CppLearner

17.1k34 gold badges112 silver badges171 bronze badges

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

Collectives™ on Stack Overflow

PHP script doesn't import CSV into MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related