Monday 1 September 2008

importing a database .csv to mysql

** this post also included a solution for Invalid query: Duplicate entry '127' for key errors **

The mission -> I have databases given to me in .csv format created in Microsoft Excel and I would like to import them into my mysql database so I can manipulate them from there.

The Solution -> the key to writing the solution for this can be found by using http://uk3.php.net/fgetcsv .

here's the code I wrote from this page. You will need to create your table first that we write to.

#############################################################################

$row = 1;
$handle = fopen ("database.csv","r");
// ip-to-country.csv must be in the same directory with this php file
// customer number here will represent the row it's on database given.

while ($data = fgetcsv ($handle, 1000, ",")) {
$query = "INSERT INTO database_table(`id`, `field1`, `field2`, `field3`)

VALUES('', '".$data[0]."', '".$data[1]."', '".$data[2]."')";
$result = mysql_query($query) or die("Invalid query: " . mysql_error().__LINE__.__FILE__);
$row++;
}
fclose ($handle);

############################################################################


The only error I came across was
Invalid query: Duplicate entry '127'

and this was down to database table I had created as I had the 'id' field set to TINYINT(3) - the resolution was to change this to INT(6)

No comments: