[SOLVED] only first line of Excell imported to Mysql

[bobinindia]

I am trying to load a csv file to my Mysql database.

For some reason only the first line comes through.

I have tried tab delimiter, comma csv and txt files

 

load data infile 'file.txt' into table birthdays

is my line og SQL in phpmyadmin

i change it to csv when it is a csv.

 

Maybe there is a btter method of exporting from Microsoft Excell

Baffled

 

MySQL 5.0.41

[pikemsu28]

to import data from a file you need to know some characeristics of the file being loaded (column value separators, line separators, values enclosed with, etc)

 

a csv file usually separates the values using a comma (,) encloses the values with quotation marks (") and separates the lines with a return (\r)

 

try using this query:

 

load data infile 'file.csv' into table birthdays
fields terminated by ',' enclosed by '"'
lines terminated by '\r';

[bobinindia]

SO I tried that and the first row is empty and the second row has the first row of data.

The rest didn't make it

Looking inside the csv shows me the encloses values has nothing. There are only commas. also no \r is there.

 

[fenway]

SO I tried that and the first row is empty and the second row has the first row of data.

The rest didn't make it

Looking inside the csv shows me the encloses values has nothing. There are only commas. also no \r is there.

Post the first 10 lines of your file -- you wont "see" \r, it's a carriage return, and it might be CR/LF if it's from a windows box.

[bobinindia]

17-Mar-08,Ja****ree,Son,1 yr,7-Jun,9884****,,Not Visited.,Had called and enquired. It_s the first b'day and will invite more than 150 ppl. Just gave details of our offer.,Not interested as we will not be able to accommodate everybody.,,

,,,,,,,,,,,

17-Mar-08,N****i,,,27-Mar,97*******3,,Visited,Enquired and gave her all the details.,Was satisfied and had her get together successfully.,,

,,,,,,,,,,,

20-Mar-08,**e,Y**m,5 yr,31-Mar,9*****360,,Visited,Gave her all the details.,"Said she will get back. On 26th, she said she had decided on another venue.",,

,,,,,,,,,,,

 

 

Here are the first three records. Can't put the phone numbers/names in a forum so to save time only three records

[fenway]

OK... now show us your table layout... I see lots of potential problems already.

[bobinindia]

CREATE TABLE `birthdays` (
  `birthday_reg_date` varchar(20) NOT NULL,
  `birthday_child` varchar(40) NOT NULL,
  `birthday_parent` varchar(40) NOT NULL,
  `birthday_age` varchar(11) NOT NULL,
  `birthday_party_date` varchar(15) NOT NULL,
  `birthday_contact_number` varchar(15) NOT NULL,
  `birthday_email` varchar(25) NOT NULL,
  `birthday_visited` char(1) NOT NULL,
  `birthday_action` text NOT NULL,
  `birthday_status` text NOT NULL,
  PRIMARY KEY  (`birthday_reg_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

Thanks

[fenway]

Well, it looks like you have the same number of fields as columns, though I'd be explicit about it.

 

Did you try \r\n?

[bobinindia]

I tried \r\n and no luck.

I still think the format of the export file from excel may be the problem.

export as csv(windows), csv(ms-dos) and text(tabbed delimited) i have tried.

Any others that might work? I am using phpmyadmin to load the file and I am using Mamp whch means I am on a mac. These may be influencing factors that I am unaware of.

[pikemsu28]

the apostrophe's (') and quotes (") will give you some issues when trying to load this data.  

 

when you try to load the data, is it giving you any error messages?

[pikemsu28]

in your table structure, you have `birthday_visited` char(1) NOT NULL, and you are trying to load 'Not Visited' and 'Visited'......looks like your table structure may not be correct.  also, your primary key is `birthday_reg_date` so it can not be duplicated, you have two records with the same date.  you may need to add an 'ID' column.

 

try this:

CREATE TABLE `birthdays` (
  `ID` int(11) NOT NULL,
  `birthday_reg_date` varchar(20) NOT NULL,
  `birthday_child` varchar(40) NOT NULL,
  `birthday_parent` varchar(40) NOT NULL,
  `birthday_age` varchar(11) NOT NULL,
  `birthday_party_date` varchar(15) NOT NULL,
  `birthday_contact_number` varchar(15) NOT NULL,
  `birthday_email` varchar(25) NOT NULL,
  `birthday_visited` char(20) NOT NULL,
  `birthday_action` text NOT NULL,
  `birthday_status` text NOT NULL,
  PRIMARY KEY  (`ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

[bobinindia]

Thank you thank you

It was the structure. Char 1 and primary key with duplicate dates.

Thanks again pikemsu28 and fenway too.