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

In article <1172832169.649337.178690 [at] h3g2000cwc.googlegroups.com>,
"kenoli" <kenoli.p [at] gmail.com> wrote:

> 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:

[snip]

> 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.

Why not do:

echo $sql . "\n";

to see what you are actually asking mysql to do.

-- tim

On Fri, 02 Mar 2007 11:42:49 +0100, kenoli <kenoli.p [at] gmail.com> wrote:

> 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:

fgetcsv() is your friend.

<?php
// set parameters
$file =3D '/my/csv/file';
$tablename =3D 'table';
$fields =3D array(
'field1',
'field2',
'field3');
//Go:
$handle =3D fopen($file,'r') or die('File not found.');
$cols =3D count($fields);
$row =3D '('.str_repeat("'%s'",$cols).')';
$query =3D "INSERT INTO `{$tablename}` (`".implode('`,`',$fields)."`) VA=
LUES =

";
$line =3D 1;
$inserts =3D array();
while($array =3D fgetcsv($handle)){ //change parameters according to for=
mat =

of you csv
if(count($array) !=3D $cols) die ('Invalid data on line '.$line);
array_walk($array,'mysql_real_escape_string');
$insert[] =3D vsprintf($row,$array);
$line++;
}
if(!empty($inserts)){
$query .=3D implode(',',$inserts);
//connect to db
mysql_query($query) or die('Problem with query:'.$query.' MySQL =

said:'.mysql_error());
} else {
echo 'Nothing to insert';
}
?>

Allthough it's not advisable to insert integers this way in a database..=
...
-- =

Rik Wasmus

"kenoli" <kenoli.p [at] gmail.com> wrote in message
news:1172832169.649337.178690 [at] 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.

On Mar 2, 6:08 am, "Steve" <no.... [at] example.com> wrote:

> you do realize this is a shitty format for csv...right?

I at least suspected as much. This format is what Excel puts out and
I have been trying to understand regular expressions well enought to
correct it in text wrangler. As you can tell, I am learning this as I
go along. I can see that the two scripts above also change the format
though I wasn't able to exactly figure out how the syntax works. I
did try.

>
> 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.

I wasn't aware of the debugging output I could request and will put
some time into figuring out how to do this. The earlier suggestions
on this are helpful.

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.

Don't worry, I barely have a day job to quit. I am doing this for
some community projects I am involved in. A friend and I are involved
in a project to help communities develop a vision for themselves and
then put together the resources to make it happen. We work hand to
mouth and one of the reasons I am doing this is that we can't afford
to hire anyone else to do it. There is no question I an a novice. I
never expect to be a professional, though I would love to learn enough
to make it all go more easily and quickly.

The assistance that people on this list have given is hugely valuable
and appreciated. I'm sorry if my own ignorance makes my questions
incomplete or foollish. I think I'm getting better. At any rate, my
code needs less and less debugging. It is an achievement for me when
I can type out a dozen lines of code and they actually work the first
time!

I ended up taking the novice's way out here and downloaded a
demonstration copy of Navicat. It made importing the records very
easy.

--Kenoli

"kenoli" <kenoli.p [at] gmail.com> wrote in message
news:1172941734.509915.110720 [at] z35g2000cwz.googlegroups.com.. .
| On Mar 2, 6:08 am, "Steve" <no.... [at] example.com> wrote:
|
| > you do realize this is a shitty format for csv...right?
|
| I at least suspected as much. This format is what Excel puts out and
| I have been trying to understand regular expressions well enought to
| correct it in text wrangler. As you can tell, I am learning this as I
| go along. I can see that the two scripts above also change the format
| though I wasn't able to exactly figure out how the syntax works. I
| did try.

look, you can save excel data in a number of formats...why not try xml?


| > 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.
|
| I wasn't aware of the debugging output I could request and will put
| some time into figuring out how to do this. The earlier suggestions
| on this are helpful.

'request'?!!! it consists of:

foreach (rowsOfData as id => row)
{
if (some condition is fubar)
{
print out the offending row number (id)
then crack open your file and go to that line
and look for something amis
}
}

debugging and testing is up to no one but you.


| > my recommendation: don't quit your day job.
|
| Don't worry, I barely have a day job to quit. I am doing this for
| some community projects I am involved in. A friend and I are involved
| in a project to help communities develop a vision for themselves and
| then put together the resources to make it happen.

so you've dropped the realestate application you've been working on for the
past year or so?

| We work hand to
| mouth and one of the reasons I am doing this is that we can't afford
| to hire anyone else to do it.

but a business plan together and get a financier. almost any non-profit
starts this way. there are plenty of philanthropic people around...the rest
whom give money do it for the tax breaks or good pr.

besides, you treat this ng like we *are* your hired help. do you not have
enough money to buy a beginners book on php? i do get rude with you from
time to time, but i'm being sincere here. if you were to express in english
what you wanted out of a function or other code, you could more easily look
for functions in the manual or book that would accomplish your goals.

if you just keep mindlessly asking us 'how can i do this?', you will learn
nothing and we can be assured in answering you that we'll hear from you
again in a day or two asking how to do your next set of functions...that's
why i think you consider us to be your UN-paid 'hired' help.

i just get sick of seeing others that don't know better, anwering your posts
thinking they are helping you out...when it is really they who need help
from being taken advantage of.

| There is no question I an a novice. I
| never expect to be a professional, though I would love to learn enough
| to make it all go more easily and quickly.

there is nothing quick nor easy about learning something - with few
exceptions. an exception would be a savaunt or person who has a natural
knack. since you are neither of these regarding php, you have stated a
contradiction...you can EITHER want to LEARN...OR you want something QUICK
and EZ.

from what i've seen over the course of the past year or so, is that you have
NO interest in the former - unless it is a fortunate side-effect - and are
solely focused on the latter.

| The assistance that people on this list have given is hugely valuable
| and appreciated.

i'm sure it has been and is appreciated. you forgot to mention, FREE.

| I'm sorry if my own ignorance makes my questions
| incomplete or foollish.

it's not alway HOW you're asking what you need. it's more that you ARE
asking without you having done ANY work to supply your own answer...such as
RTFM or looking up examples on the net.

| I think I'm getting better.

uhhhmmmm...no, you're not. you are modus-apparandi; business as usual.

| At any rate, my
| code needs less and less debugging.

the quality of those who supply it to you must have improved.

| It is an achievement for me when
| I can type out a dozen lines of code and they actually work the first
| time!

i imagine a great deal of truth lies in that statement...as you rarely ever
originate one line, much less a dozen.

| I ended up taking the novice's way out here and downloaded a
| demonstration copy of Navicat. It made importing the records very
| easy.

good for you. so does that mean you're going to take the professional
approach to programming and start writing your own code, reading the manual,
and looking at examples from the web or a book? anything less, and you
should change 'i ended up taking ...' with 'i'm still taking the novice way
out ...' and just finish the sentence with 'and posted yet another
help-write-my-code'. hell, we all know your game enough. why not just
title/subject your posts with 'IT'S ME AGAIN'...that way i don't have to
check for 'kenoli' as the op before deciding to help or not.

On Mar 3, 9:49 am, "Steve" <no.... [at] example.com> wrote:

> so you've dropped the realestate application you've been working on for the
> past year or so?

I don't know what you ar referring to. I have never worked on a real
estate applicaion.

>
> | We work hand to
> | mouth and one of the reasons I am doing this is that we can't afford
> | to hire anyone else to do it.
>
> but a business plan together and get a financier. almost any non-profit
> starts this way. there are plenty of philanthropic people around...the rest
> whom give money do it for the tax breaks or good pr.

We're working on it. We have a description of what we are doing and
are approaching foundations. We have a small grant for one project
and are seeking funding for others.

> besides, you treat this ng like we *are* your hired help. do you not have
> enough money to buy a beginners book on php? i do get rude with you from
> time to time, but i'm being sincere here. if you were to express in english
> what you wanted out of a function or other code, you could more easily look
> for functions in the manual or book that would accomplish your goals.

I'm sorry to be an annoyance. I have bought a number of books and do
use them. I also find a lot of assistance on the internet. When I
get stuck I have come to this list and have gotten some great help.
What I have experienced is that there is a threshold of understanding
that I need to get past in order to even really understand how to find
the information I need. For this reason, I'm sure many of my
questions are not as focused as they could be. I will continue to do
my best to solve problems on my own and to articulate questions well
when I do post them.

> if you just keep mindlessly asking us 'how can i do this?', you will learn
> nothing and we can be assured in answering you that we'll hear from you
> again in a day or two asking how to do your next set of functions...that's
> why i think you consider us to be your UN-paid 'hired' help.
>
> i just get sick of seeing others that don't know better, anwering your posts
> thinking they are helping you out...when it is really they who need help
> from being taken advantage of.

So what is the criteria here for a question to be appropriate or for
someone to answer a question. Do people feel taken advantage of by my
asking questions about things I don't understand? What is this
listserv for? I thought it was a place to ask questions when we get
stuck and learn from each other.

I've tried to help were I can. I helped someone having trouble
installing mysql on a Mac, something I struggled with for a long
time. It is possible to do it all on your own, but I felt it was
useful to at least share what I knew. I didn't feel taken advantage
of. In fact, I was happy I could help. It made me feel good. I
remembered how clueleess I felt when I was at the stage this person
was at. I was passing on some information that I had learned, some on
my own and some from others.

> | There is no question I an a novice. I
> | never expect to be a professional, though I would love to learn enough
> | to make it all go more easily and quickly.
>
> there is nothing quick nor easy about learning something - with few
> exceptions. an exception would be a savaunt or person who has a natural
> knack. since you are neither of these regarding php, you have stated a
> contradiction...you can EITHER want to LEARN...OR you want something QUICK
> and EZ.

I'm not sure what the contradiction is. I want to learn and am
learning. As I learn more, writing code becomes easier. This seems
to make sense.

> from what i've seen over the course of the past year or so, is that you have
> NO interest in the former - unless it is a fortunate side-effect - and are
> solely focused on the latter.

?? From my pespective, I've learned a lot.

>
> | The assistance that people on this list have given is hugely valuable
> | and appreciated.
>
> i'm sure it has been and is appreciated. you forgot to mention, FREE.

Is it supposed to cost? Isn't the information here free requested and
freely offered. Did I miss something? I help people with all kinds
of things for free. As a matter of fact I probably do more for free
than I get paid for. I emjoy doing this. It seems to me that a great
way to be in the world is to share what we can with each other.

> | I'm sorry if my own ignorance makes my questions
> | incomplete or foollish.
>
> it's not alway HOW you're asking what you need. it's more that you ARE
> asking without you having done ANY work to supply your own answer...such as
> RTFM or looking up examples on the net.

You are making some assumptions here that are not true. I spent a
good 10 hours struggling with this import issue before I asked any
questions. I searched the archive of this lists and found the two
scripts I posted. I tried to use both of them many times and didn't
get them to work. I attempted to re-format the csv file I got from
Excel with some success and some failure. After this, I posted the
query you responded to.

I would suggest that if you feel resentful about responding to a query
that you not respond. Responding when you do feel resentful and then
ranting at me that I should never have asked a question is not doing
you or me much good. If you feel taken advantage of by responding,
you can solve that problem by not responding. There is nothing about
any of questions that says you have to answer.

> good for you. so does that mean you're going to take the professional
> approach to programming and start writing your own code, reading the manual,
> and looking at examples from the web or a book? anything less, and you
> should change 'i ended up taking ...' with 'i'm still taking the novice way
> out ...' and just finish the sentence with 'and posted yet another
> help-write-my-code'. hell, we all know your game enough. why not just
> title/subject your posts with 'IT'S ME AGAIN'...that way i don't have to
> check for 'kenoli' as the op before deciding to help or not.

I really don't know where you are coming from. I do write most of my
code. I come here when I get stuck. And the code I've gotten from
this list I've had to adapt and make work in the context I am using
it. I spend much of my free time reading books on this subject.

I think you should get off of this list if you feel so resentful of
people who ask questions. I don't appreciate the huge assumptions
about where I am coming from that you are projecting on me.

Maybe I'll just look for your replies and ignore them. I'd rather
work on code than listen to your complaints about being taken
advantage of.

--Kenoli