|
|
Posted by Steve on 03/02/07 14:08
"kenoli" <kenoli.p@gmail.com> wrote in message
news:1172832169.649337.178690@h3g2000cwc.googlegroups.com...
|I have tried two php scripts in an attempt to load data exported from
| an Exel file into a mysql database.
kenoli, when are you just going to either break down and hire someone or,
learn to read the manual?
btw, you do know that a csv file doesn't have ANYTHING to do with
excel...right?
| $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
so you realize that either your columns specified under/out number the
values being inserted, or your values have unescaped ticks (') in the
values. now run along and see what the case is...you know you can output the
line number that is causing the problem...right?
| //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.
not carefully enough!
| 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.
not an issue.
| 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
you do realize this is a shitty format for csv...right? the data should be
encapsulated (usually with quotes). that way you can be more certain that
"jordanna, steve" is one value rather than two values going into two
different fields. if you don't account for this, you will have...you guessed
it...outnumbered the columns of the insert statement with 'extra' values.
christ kenoli, you have two examples of how to do this. the least you could
do is try to output some debugging info in the code. and, if you can't use
that to help yourself figure it out, you could at least post the line's data
here so we aren't guessing.
my recommendation: don't quit your day job.
Navigation:
[Reply to this message]
|