...

View Full Version : php & mysql: splitting a csv and inserting into database



Scrowler
02-09-2006, 05:38 AM
i have a script, here's the code:


<?php

include "config.php";

/*

$all = mysql_query("SELECT * FROM Wineries");

while($row = mysql_fetch_assoc($all)){

$password = generatePassword();

if(!mysql_query("UPDATE Wineries SET Password = '".md5($password)."' WHERE id = '".$row["id"]."' LIMIT 1")) echo mysql_error();

mail("pr.testemail@mindmediagroup.com", $row["Name1"] . " ".$row["Surname"]."'s New Password", $password, "FROM: WhatWine <whatwine@mindmediagroup.com>");

}

*/

$fp = file("wine.csv");

foreach($fp as $key => $current){

if($key > 0){

$sql = "INSERT INTO Wineries VALUES('',";

$pieces = explode(',', $current);

foreach($pieces as $p => $piece){

/*if(strstr($piece, "@")){

list($user, $domain) = explode("@", $piece);

$piece = $user . "@mindinternet.co.nz";

}*/

#$piece = str_replace("\r\n", "", $piece);
$piece = trim($piece);
$piece = str_replace('"', '', $piece);
$piece = str_replace("\n", '', $piece);
$piece = str_replace("\r", '', $piece);

$sql .= "\n\t'".mysql_real_escape_string($piece)."'";

$column_count++;

if($p + 1 < count($pieces)) $sql .= ",";

}

#$sql = substr($sql, 0, strlen($sql)-1);

$sql .= ");\n\n";

}

if($sql){

if(!mysql_query($sql)){ echo '<pre style="color: red">' . $sql . '</pre><br /><br />'.$column_count.'<br /><br />' . mysql_error(); } else { if(!$ca){ echo '<pre style="color: green">'.$sql.'</pre><br /><br />'.$column_count; $ca = true; } }
unset($sql);

}

unset($column_count);

}


echo '<br /><br /><span style="color: green">done</span>';

?>

this script takes each line (except the first, which has titles in it) and splits it by commas, then creates the sql and executes it, problem is, the sql query is real screwed, some queries go through fine, others are missing the last set of '' in the insert area which triggers a bad column count error, others come out like this:


INSERT INTO Wineries VALUES('',
'');

and


INSERT INTO Wineries VALUES('',
'Ktest Estate was established and is owned by Wallace Hilder and Charles Forkworth. Our sales office is run by Jonathan Holder our vines are managed by Cathryn Maccar and our wines are made by Dean Shed.');

originally i thought it was because the csv i exported from excel had some commas inside some of the cells, so i removed them all, but it still gives errors.

help greatly appreciated, this is relatively urgent!

fci
02-09-2006, 05:56 AM
so.. what is the actual error message you are getting?

firepages
02-09-2006, 02:05 PM
I have not looked at your code but from experience I found fgetcsv (http://www.php.net/fgetcsv) very useful

degsy
02-09-2006, 03:49 PM
Not sure what you have to work with, but I find it easier to use phpMyAdmin to import a CSV file.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum