Posted by kenoli on 03/02/07 10:42

I have tried two php scripts in an attempt to load data exported from
an Exel file into a mysql database. One I found in the archive for
this group and another elsehwere. Here are the scripts:

Script 1

<?

//Connect to database

$fcontents = file ('list_core.csv');
# expects the csv file to be in the same dir as this script

for($i=0; $i<sizeof($fcontents); $i++) {
$line = trim($fcontents[$i]);
$arr = explode(",", $line);
#if your data is comma separated
# instead of tab separated,
# change the '\t' above to ','

$sql = "INSERT INTO TBL_PERSON (col1, col2, etc.) VALUES ('".
implode("','", $arr) ."')";
mysql_query($sql);
echo $sql ."<br>\n";
if(mysql_error()) {
echo mysql_error() ."<br>\n";
}
}
?>

This one gives this error: You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for the
right syntax to use

=============================

Script 2

<?

//Connect to database

//Let' put the data in
for($i=0; $i<count($sqlData); $i++){
$query="INSERT INTO tbl_person ";
//change your column names
$query.="(letter, confirm, contact_person, first_name,
last_name, organization, email, phone, race, gender, geography,
sector_1, sector_2, admin_comment, csf, biotech_wc) ";
$query.="VALUES (";
$query.="\"".$sqlData[$i][0]."\", ";
$query.="\"".$sqlData[$i][1]."\", ";
$query.="\"".$sqlData[$i][2]."\", ";
$query.="\"".$sqlData[$i][3]."\", ";
$query.="\"".$sqlData[$i][4]."\", ";
$query.="\"".$sqlData[$i][5]."\", ";
$query.="\"".$sqlData[$i][6]."\", ";
$query.="\"".$sqlData[$i][7]."\", ";
$query.="\"".$sqlData[$i][8]."\", ";
$query.="\"".$sqlData[$i][9]."\", ";
$query.="\"".$sqlData[$i][10]."\", ";
$query.="\"".$sqlData[$i][11]."\", ";
$query.="\"".$sqlData[$i][12]."\", ";
$query.="\"".$sqlData[$i][13]."\", ";
$query.="\"".$sqlData[$i][14]."\", ";
$query.="\"".$sqlData[$i][15]."\"";
$query.=");";
//Do it
//$dbResult=mysql_query($query);
}//End for loop

echo $query;

echo "<br/><br/>";

echo("done.don't run me again.. data would be duplicated!!");

} else {

echo("No data was read from csv file");
}//End if

?>

This one enters data, but incorrectly, as if there were the wrong
number of fields in each record (i.e. data ends up in wrong fields),
though I have checked carefully to make sure this is not the case.

I'm not sure about the line terminations in the csv file. I tried
using what is created by the Excel export. I also tried opening it
and saving it in TextWrangler as I know it inserts proper linux line
terminators. Same problem.

Here are a few lines of my csv file:

,,Jordanna,Steve,Falk,Chamber of Commerce,,,,,Citywide,Association,,,Y
,,Jordanna,Mike,Farrah,Liaison to Small Business
Commission,,,,,Citywide,Govt - bus liaison,,,Y


HELP!!!!!

Thanks,

--Kenoli

[Back to original message]