Jump to content

[SOLVED] only first line of Excell imported to Mysql


bobinindia

Recommended Posts

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

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';

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.

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

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

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.

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;

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.