MySQL import data from a csv file via LOAD DATA INFILE does not work

Question

I tried so hard to get this command work. Nothing is happening. Here is my issue:

I have a .csv file which I am uploading to my server where I want to run a script to import all of the data into a MySQL table. The csv looks like this:

id;herstellernr;hersteller;firmenname;url
123;ABC;Hersteller1;Firmenname1;http://www.test.com
234;DEF;Hersteller2;Firmenname2;http://www.test2.de
345;GHI;Hersteller3;Firmenname3;http://www.test3.net

... and so on.

After uploading the csv file, I first want to create a new table (temporary) where my data should be imported. Creating the table works without any problems; getting my Data imported from the csv file doesn't.

Here is my script (which comes after the upload script):

$temp = $pdo->prepare("

CREATE TABLE ".$name."
(
   id int(11),
   herstellernr varchar(3),
   hersteller varchar(150),
   firmenname varchar(255),
   url varchar(100),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

LOAD DATA INFILE '_csv/".$filename."' INTO TABLE ".$name."
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, herstellernr, hersteller, firmenname, url)

");

$temp->execute(array());

CREATE TABLE works but after that there is nothing happening with my LOAD DATA INFILE

Plus: I don't really know if it should be '\n' or '\r\n'. Anyway both cases didn't work.

I tried really hard and I am sure the syntax is just right. But I can't make it. Can you help? Thank you

You cannot use multiple queries with PDO. Break each statement into a separate query. You do not need to prepare the query since you're not actually using parameters. A straight query will work fine for each statement. — aynber
– aynber, Commented Jul 17, 2018 at 14:25
Thank you a lot. I'm not that fit with SQL and PDO. Can you show me a solution with my example?! Would be just GREAT :) — muzzie
– muzzie, Commented Jul 17, 2018 at 14:27

aynber · Accepted Answer · 2018-07-17 14:39:27Z

2

You cannot use multiple queries with PDO. You'll need to break them up into separate queries. Since you're not using bound parameters, you can skip the prepare, as well:

$pdo->query("CREATE TABLE ".$name."
(
   id int(11),
   herstellernr varchar(3),
   hersteller varchar(150),
   firmenname varchar(255),
   url varchar(100),
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci");

$pdo->query("LOAD DATA INFILE '_csv/".$filename."' INTO TABLE ".$name."
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(id, herstellernr, hersteller, firmenname, url)");

edited Jul 17, 2018 at 14:39

answered Jul 17, 2018 at 14:33

aynber

23.2k9 gold badges57 silver badges69 bronze badges

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

10 Comments

muzzie Over a year ago

Thank you. This is resulting in Fatal error: Uncaught Error: Call to undefined method PDO::execute() in /www/htdocs/.../:65 Stack trace: #0 {main} thrown in /www/htdocs/.../_update_csv.php on line 65

muzzie Over a year ago

Which is this line: $pdo->execute("CREATE TABLE ".$name."

aynber Over a year ago

Oops, I think that should have been query, not execute. Let me update that.

muzzie Over a year ago

Now it is creating the table but the data import is still not working :/ 0 rows. Thank you anyway

aynber Over a year ago

Another thing to note is that LOAD DATA INFILE needs a full path, and mysql must be able to access that directory. You can use PDO error handling to check for errors after your query to see if it works.

|

Collectives™ on Stack Overflow

MySQL import data from a csv file via LOAD DATA INFILE does not work

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related