How do I import a CSV to MySQL database

[kpetsche20]

Hey, I am trying to find a good tutorial on how to upload a csv file to mysql.  If you know of any good tutorials or can explain it in a post, please let me know.

[Adam]

Just use PHP to work with the data. I don't know CSV backwards at all and I've never tried this before but just try something like:

 

$rows = explode("\n", $csv_src);

foreach ($rows as $row) {
    $cols = explode(",", $row);
    $insert = mysql_query("INSERT INTO ... (field1, field2, field3) VALUES ('{$cols[0]}','{$cols[1]}','{$cols[2]}')");
}

 

Obviouslly you'll need to play around with it.. might need to use a second foreach loop to build the query string or something but good start!

 

Adam

[kpetsche20]

Won't a while or for loop work just as well

[Garethp]

foreach is a for loop made easier. foreach ($rows as $row) { is the same (essentially) as for($i=0;$i<len($rows);$i++){$row=$rows[$i];

 

Maybe Len is the wrong syntax but all the same, foreach is just easier

[Adam]

Yeah.. It's just designed to make more sense when you read it back.. I believe..

[kpetsche20]

The explode function won't work, because there are dates in some of the querys that have commas.

 

 

Here is a row of data I'm trying to insert, as you can see not every comma is used as a row separator.

 

http://www.anrdoezrs.net/click-<pid>-10595228?url=http://www.stubhub.com/memphis-grizzlies-playoff-tickets/4-4-2009-277266/,Memphis,Any information on Memphis Grizzlies Playoff Tickets will be posted here.,4/4/2009,4,4,April    ,2009,19:00,Memphis Grizzlies Playoff Tickets,NBA Tickets,NBA Playoff Tickets,277266,http://www.stubhub.com/data/venue_maps/13023/stubhub_37720_nba_Grizzlies_400.png,Memphis,TN,Basketball - Flash,FedEx Forum,13023,38103

[Mchl]

There's dedicated MySQL statement for loading data from text files (especially CSV). LOAD DATA INFILE

 

Here is a row of data I'm trying to insert, as you can see not every comma is used as a row separator.

 

THen it is not a proper CSV file and you will have problems with processing it.

[Adam]

Yeah I was going to say, proper CVS files should have quotes around fields that contain commas (aparently).. I weren't aware of MySQL statements for loading CSV though, I imagine they'd be a lot easier!

 

Adam