PHP + Save multiple rows from .csv to mysql

Question

I am trying to upload a .csv file and than save each row to a mysql db. So for each row in the .csv file, I would like to create a row in my mysql db. Excuse my english, but I hope you get what I mean.

I have got so far, that i am able to get the data from the first row:

$path   = $targetPath;
$row    = 1;

if (($handle = fopen($path, "r")) !== FALSE) {

    while (($data = fgetcsv($handle, 1000, ";")) !== FALSE) {

        $row++;
        $dataEntries[] = $data ;

    }

    fclose($handle);

} else{

    echo("There has been an error parsing ".$csvname);

}

//Everything seems to be okay, we can save it
foreach($dataEntries as $line){

    $oName  = $line[0];
    $oUrl   = $line[1];
    $oType  = $line[2];
    $oDescr = $line[3];

    //If there is just one row in the csv file, Output: "string"
    //If there are more rows, than the output is "stringstring1string2"
    echo($oName);

}

UPDATE

Just to let every1 know: @Greg P ´s solution worked great, but I had to add LOCAL to the infile command, so that I could access the csv file that was uploaded to my server:

LOAD DATA LOCAL INFILE '$targetPath' INTO TABLE tableName FIELDS TERMINATED BY ';' (name,url,type,descr)

What is your MySQL table structure?

Jeroen
– Jeroen

2012-05-13 14:48:47 +00:00
Commented May 13, 2012 at 14:48 — Jeroen
– Jeroen, Commented May 13, 2012 at 14:48

GDP · Accepted Answer · 2012-05-13 14:48:53Z

8

Why not use LOAD DATA INFILE, something like this:

LOAD DATA INFILE 'filename.csv' 
INTO TABLE filenames
FIELDS TERMINATED BY ',' 
(Name, Url, Type, Descr)

answered May 13, 2012 at 14:48

GDP

8,1636 gold badges48 silver badges84 bronze badges

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

4 Comments

r0skar Over a year ago

This seems like a great solution :) just one thing: i am a real noob.... so what would I put for filenames? It cant be the csv filename, since that got declared before? Because I get a syntax error. My query looks like this right now: LOAD DATA INFILE '$targetPath' INTO tableName filenames FIELDS TERMINATED BY ';' (name,url,type,descr)

r0skar Over a year ago

Nevermind. I think I got it. Now the only problem seems to be the filepath, but I guess I am able to sort that out myself :)

GDP Over a year ago

Don't overcomplicate it - it simply locates and loads the file that you specify, and dumps it into the database. It DOES depend on stuctures of course (but hey - that's a database for you). If it does get more exhaustive, there are many options available for the function in the manual at dev.mysql.com/doc/refman/5.1/en/load-data.html.

GDP Over a year ago

Glad it worked out for you - I do a ton of log processing, and even if it the source logs are not wonderfully structured they way I'd like, using this to get that raw file into a temporary database gets it into the game, then I can use the strengths of SQL to get the final results that I need. Beats writing a bunch of custom PHP to debug when MySQL will do it for me.

Collectives™ on Stack Overflow

PHP + Save multiple rows from .csv to mysql

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related