Results 1 to 14 of 14
  1. #1
    Join Date
    May 2004
    Location
    Oswego NY
    Posts
    5

    uploading a table to MySQl

    I have a bunch of tables in another db language.
    I need to upload them to MySQl database.
    They are in tab delimited .txt files.

    I've tried dozens of attempts to enter the data into the databse using phpMySQLAdmin and always get a #1064 syntxt error.

    What format do I need to upload a table?
    hanx for any assistance

  2. #2
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    You need to use the LOAD DATA INFILE query. Check the MySQL documentation.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  3. #3
    Join Date
    May 2004
    Location
    Oswego NY
    Posts
    5
    I'm using phpMyAdmin 2.5.6 to control my tables.

    where would I use the command that you recommend?

    Is there some sort of text line editor for my tables that I'm not aware of where I could type in individual commands like you recommend?

    hanx
    sfb

  4. #4
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    You would type it in the box that appears when you hit SQL in phpmyadmin.

    See this mysql manual entry for more information.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  5. #5
    Join Date
    May 2004
    Location
    Oswego NY
    Posts
    5
    I've come to find out that my databse is a flat database, and I don't need a relational database like MySQL. Do you have any other suggestions for software other then MySQL, read into dynamic web pages by .php?

  6. #6
    I guess I would have to ask what you are intending to do with the database before I can give a useful reccomendation of software.
    Advanced Forum Hosting
    http://www.boardnation.com
    Easily build a community today!

  7. #7
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    If I understand you correctly, you have a plain text file, that has fields delimited by tabs, and you would like to read this?

    If that is the case, you can use a function such as fgetcsv() to read the file.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  8. #8
    You can use that function above and build a small php script to uplod the data(in casu you don't have acces to mysql server via SSH)
    Hosting: Evolve Romania
    Scripting: Azteck Korp

  9. #9
    I recommend MySQL Front. It allows you to import data from many different databases. You may be able to go back to the original database and re-export it in native format instead of as a comma delimited text file. It also can import the data from a text file if you wanna go that way

  10. #10
    Join Date
    May 2004
    Location
    Oswego NY
    Posts
    5
    Thank you all for your insights, but I seem to be way in over my head with starting to work with MySQL. When trying to help me find answers, please treat me as a 10 hour newbie. (I 've actually been trying for about 30 hours now)


    danyprundus: What's an SSH?
    pyoor: MySQL Front downloads as a .exe file, which is not much use on my Mac.
    daveman: for now, I'm just trying to upload data, I can't get phpMyAdmin 2.5.6 to read my text file. I'm not even to the point of using the data, I just wanna get it in the .db.

    I have a simple tab delimited text file, with only 3 words of data.
    phpMyAdmin 2.5.6 has created a table for me, and there are only 3 text fields in it. It only has one row of data now.

    here is my text file to upload:
    Test Eastern Shore Associates Insurance SILVER

    here is an export, the data that typed in manually:

    # phpMyAdmin SQL Dump
    # version 2.5.6
    #
    # Host: localhost
    # Generation Time: May 25, 2004 at 01:52 AM
    # Server version: 3.23.41
    # PHP Version: 4.0.6
    #
    # Database : `test`
    #

    # --------------------------------------------------------

    #
    # Table structure for table `vendor`
    #

    CREATE TABLE `vendor` (
    `MEMBER_NAMEF` text NOT NULL,
    `MEMBER_NAMEL` text NOT NULL,
    `MEMBER_LEVEL` text NOT NULL
    ) TYPE=MyISAM;

    #
    # Dumping data for table `vendor`
    #

    INSERT INTO `vendor` VALUES ('Sharie & Mike', 'Edwards', 'CAPTAIN');

    Thanks again, (wishing I were a better geek)
    sfb

  11. #11
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    Well your test file has more than three words :

    Test Eastern Shore Associates Insurance SILVER

    Each space is considered a word. What I think the file represents is

    "Eastern Shore Associates" [tab] "Insurance" [tab] "SILVER"

    So, sample 3 lines from the file would be:
    Code:
    "Some company" "Finance" "Platinum"
    "Another company" "Stocks" "Gold"
    "Yet Another Co." "Real Estate" "Silver"
    I've added the quote symbols, and each space represents a tab.

    If your file is structured thusly, then you can use the following PHP script to read the file and generate the SQL statements which you can copy and paste into myphpadmin.

    PHP Code:
    $thefile "somefile.txt";
    //Replace somefile.txt with your filename (of course)
    $contents file($thefile);
    while(list(,
    $val) = each($contents))
    {
        
    $bits explode("\t",$val);
        while(list(
    $k,$v) = each($bits))
        {
               
    $bits[$k] = mysql_escape_string($v);
        }
        echo 
    "INSERT INTO vendor VALUES ('".$bits[0]."','".$bits[1]."','".$bits[2]."')";
        echo 
    "<br />";

    Let me know if this helps.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

  12. #12
    SSH= Secure Shell, a way to remote acces severs... But the script written by fyrestrtr should help you.
    I guess you could just send me the file and I'll put it on your database if u like
    Hosting: Evolve Romania
    Scripting: Azteck Korp

  13. #13
    Join Date
    May 2004
    Location
    Oswego NY
    Posts
    5
    fyrestrtr: Thank you for some of the missing syntax.
    I still get this error when I uses phpMyAdmin:

    MySQL said:
    #1083 - Field separator argument is not what is expected.
    I cannot find documentation on this error number.

    File contents:
    "Test" "Insurance" "SILVER"

    Also, Thank you for the .php script.
    I cannot read it, and don't know where to put it to operate it.
    Paste into phpMyAdmin? Where?
    I haven't had a chance to learn much .php yet, although it is obvious that I need to.

    I've done years of work with the SmithMicro product WebCatalog, so I thought that I knew database theory. But each reply you make raises more questions than answers, I am assuming that my experiences with other database coding software is useless.

    I realize that I'm an absolute beginner at this. I do have several manuals open on my desk, and I am trying to pick this up, but the starting curve seems aweful steep to me.

    danyprundus: Thank you for your offer, but uploading a file is step 1 of 1000 for me at this point. I don't see how it would help in the long run.

    Thank you all again. I'm going back to attempt to upload a file for the 300th time. :-)

  14. #14
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,099
    To use my script, follow these steps :

    1. Save the code in a file with a .php extension
    2. Change somefile.txt to the name of your file that contains the information.
    3. Upload both files to a location on your webserver (make sure both are in the same directory).
    4. Browse to the location of the .php file (if you called the file test.php, you should browse to http://www.yourserver.com/test.php)
    5. Copy the output from the script (whatever is displayed on the page).
    6. Login to phpmyadmin
    7. Click on the table called vendor on the left hand column
    8. In the right hand frame, click on the tab marked SQL
    9. Paste the text from step 5 in the resulting box (delete anything there first)
    10. Hit Go.
    In order to understand recursion, one must first understand recursion.
    If you feel like it, you can read my blog
    Signal > Noise

Posting Permissions

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