Mysql import - Where do I go wrong?

[ruffy]

I use phpMyAdmin 2.10.0.2 to administer Mysql 5.0
My PHP version is 5.2.1.

Always "affected rows" ends up to be only 1,
when I have over 300 rows in my datafile.
Can you tell me where my import procedure goes wrong?

Here's what I do to populate a table.

After choosing the database in the left pane,
the left pane lists that database's tables.
I select my table in that list.
In the right pane, I hit the Import tab.
Then I browse & "choose the file" that holds my data.

It's an Excel file in my Mac, in the htdocs directory.

My data file has no header row, so I put 0 for
"Number of recs to skip from start".

"Allow interrupt..." - I leave checked.

Not all the fields of the table have their values
columniized in my file, so I will use the
"USE CSV with LOAD DATA" option.

Replace data - I check because
Fields terminated - I put a comma.
Fields enclosed by - Tried blank, single & double quotes.
(I am not sure how Excel or ODT deliver their fields,
but I can massage the daa later in mysql - so I don't worry much about this.
Fields escaped by - Tried default \ and blank.
Lines terminated by - I leave at "auto".
(Could this be my problem?)
Use Local keyword - I check.
In "column names" I enter the column names of the fields that should be populated as per the data coming in,
keeping the names in their respective order as per the spreadsheet's order - separated by commas.

 

[ruffy]

Well - I then tried to do it via sql.

INSERT INTO Prospect
(prospectid, name, lastdialon, lastofferon, addr1, addr2, city, state, zip, redialdate, phone1, phone2, email, fax, jobtypeid, listid, lastfset,
demographic1, demographic2, businessid, sourceid, filler1, filler2, filler3, filler4, filler5)
VALUES
(,Adam Artleff,,,,,,,,,349 765 2788,,,,,CF03,,30p,,,,,,,,),
(,Jane Zoster,,,,,,,,,718 724 2916,,,,,CF03,,25p,Amherst NY 13701-6420,,,,,,,);

The table def is:
CREATE TABLE Prospect(
prospectid int AUTO_INCREMENT PRIMARY KEY,
name varchar(60),
lastdialon date default '0000-00-00',
lastofferon date default '0000-00-00',
addr1 varchar(50),
addr2 varchar(50),
city varchar(30),
state varchar(2),
zip varchar(10),
redialdate date default '0000-00-00',
phone1 varchar(25),
phone2 varchar(25),
email varchar(40),
fax varchar(10),
jobtypeid int,
listid varchar(4),
lastfset int(2) default 0,
demographic1 varchar(5),
demographic2 varchar(30),
businessid varchar(1),
sourceid int,
filler1 varchar(30),
filler2 varchar(30),
filler3 varchar(30),
filler4 varchar(30),
filler5 varchar(10)
);

 

[vacunita]

is your file xls or csv? i don't think phpmyadmin can read actual xls files.

Can you perhaps show us an example of your file?

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.

 

[ruffy]

I don't know why, but my sql worked when I wrapped the values in quotations.
These values were destined for varchar fields, but Mysql seems unable to figure that from what's between the commas, even though it should be "expecting" these values between the comma delimiters to be text data.

vacunita, thank you for your time.

 

[vacunita]

All text values in queries should always be wrapped in quotes.

Anyway glad you got it working.

----------------------------------
Ignorance is not necessarily Bliss, case in point:
Unknown has caused an Unknown Error on Unknown and must be shutdown to prevent damage to Unknown.