Loading Data into table question?

[macunkie]

I'm in the process of working through the MySQL Tutorial http://dev.mysql.com/doc/mysql/en/Tutorial.html.

I'm at the step of attempting to load data in the table:http://dev.mysql.com/doc/mysql/en/Loading_tables.html

I've intially inputed the example data in an excel worksheet and saved the file as .txt file.
The file saves with the required "tab" between each row of data.

When I use terminal command:LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet;

only the first row of data is loaded into the table? I've also tried to load the .txt file via phpmyadmin and have had no luck correctly adding the data. When I try this approach, again only the first row is added, but the first three rows of the .txt file are added to FIRST row of the mysql table.

I want to be able to organise data for input into MySQL data bases. Is there a better program than excel to store and organise data for later input into MySQL data bases?

I'm using a G4 PowerBook with OSX 3.7.

Thanks,

MacUnkie.

[guelphdad]

Do you mean use tab between each column (i.e. each piece of data in the row)?
You would need soemthing like:

 LOAD DATA INFILE
 'path/to/your/file/filename.txt'
 INTO TABLE tablename
 FIELDS TERMINATED BY '/t'
 LINES TERMINATED BY '/n'

You may need to try different variations on the /n if you are getting trouble still. You may need /r/n or /r/f for instance. I'm not sure what the actual line separator is on a Mac.
YOu are not specifying what is being used to separate the lines so therefore you are not getting separate lines input. If you don't use a tab or some other character between your columns of data you will also run into trouble.

[macunkie]

Do you mean use tab between each column (i.e. each piece of data in the row)?
You would need soemthing like:

 LOAD DATA INFILE
 'path/to/your/file/filename.txt'
 INTO TABLE tablename
 FIELDS TERMINATED BY '/t'
 LINES TERMINATED BY '/n'

You may need to try different variations on the /n if you are getting trouble still. You may need /r/n or /r/f for instance. I'm not sure what the actual line separator is on a Mac.
YOu are not specifying what is being used to separate the lines so therefore you are not getting separate lines input. If you don't use a tab or some other character between your columns of data you will also run into trouble.

Thanks for your help Dave!

Ultimatly, I will be using phpmyadmin to load data into the tables of my mysql databases. So what I really need is an application that I can input data into and save as a sql script. i.e. if my data contained the following:

EXAMPLE FIELDS:

LastName Firstname Sex Birthdate

Example data:

Smith Bob M 06-05-76
etc, etc.......

Is there an application that I can input the data and save the data as a data.sql file to run a quiery on?

Thanks,

MacUnkie.

[macunkie]

Do you mean use tab between each column (i.e. each piece of data in the row)?
You would need soemthing like:

 LOAD DATA INFILE
 'path/to/your/file/filename.txt'
 INTO TABLE tablename
 FIELDS TERMINATED BY '/t'
 LINES TERMINATED BY '/n'

You may need to try different variations on the /n if you are getting trouble still. You may need /r/n or /r/f for instance. I'm not sure what the actual line separator is on a Mac.
YOu are not specifying what is being used to separate the lines so therefore you are not getting separate lines input. If you don't use a tab or some other character between your columns of data you will also run into trouble.

I ended up successfully adding data by the following modification to the above code:

 LOAD DATA INFILE
 'path/to/your/file/filename.txt'
 INTO TABLE tablename
 FIELDS TERMINATED BY '/t'
 LINES TERMINATED BY '/r'

Thanks again for your help!

Macunkie.