Importing a .csv file to mysql table

[Joesavage]

my phpmyadmin has a simple tool used for importing .csv files, so i thought this would be straight forward. The problem is that the .csv file I am trying to import has sometimes has commas in one of the columns. The .csv file is the maxmind database that is used for finding out what country an IP address is in, it has nearly 1000000 rows, so I cannot go through and take all the commas out by hand, even though this would solve the problem. Whenever I try to import the file the import thingy gets to a row with a comma in a column and it aborts and reports an error.

 

Here are the settings I am using.

 

Fields terminated by: ,

Fields enclosed by: "

Fields escaped by: \

Lines terminated by: \n

 

I am pretty sure if I change this up I can make it import correctly and ignore those commas in the last column. Any suggestions?

[fenway]

LOAD DATA FROM INFILE...?

[Joesavage]

I tried that, I get an error message saying 'this command is not supported by your version of mysql' I think I am using MySQL 2.9. My host blows.

[fenway]

2.9? Try again.... anything older than 3.23 is ancient... plus, 2.9 wasn't a release, AFAIK...  maybe you're confusing it with phpmyadmin version?

[Joesavage]

Well i finally got it to work using this script.

 

<?php

 

$con = mysql_connect("localhost","Dylan","Rippy101");

mysql_select_db("dylan_Money", $con);

 

$row = 1;

$handle = fopen("csv.csv", "r");

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

    $num = count($data);

    echo "<p> $num fields in line $row: <br /></p>\n";

    $row++;

    for ($c=0; $c < $num; $c++) {

        echo $data[$c] . "<br />\n";

mysql_query("INSERT INTO csv (start_ip, end_ip, start, end, cc, cn)

VALUES ('$data[1]', '$data[2]', '$data[3]', '$data[4]', '$data[5]', '$data[6]')");

    }

}

fclose($handle);

?>

 

I think i made it alot more work than it had to be.