SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member macunkie's Avatar
    Join Date
    Aug 2004
    Location
    Lost Wages
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Loading Data into table question?

    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.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Do you mean use tab between each column (i.e. each piece of data in the row)?
    You would need soemthing like:

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

  3. #3
    SitePoint Member macunkie's Avatar
    Join Date
    Aug 2004
    Location
    Lost Wages
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by daveman
    Do you mean use tab between each column (i.e. each piece of data in the row)?
    You would need soemthing like:

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

  4. #4
    SitePoint Member macunkie's Avatar
    Join Date
    Aug 2004
    Location
    Lost Wages
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Solved!

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

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

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •