Web Hosting Forums

Results 1 to 4 of 4

This is a discussion on Inserting Unformated Data to SQL in the Hosting Talk & Chit-chat forum
Hey... Just to let you know these forums have been very helpful. so thank you very much!!! I have another question for anyone who may ...

  1. #1
    Loyal Client
    Join Date
    Oct 2003
    Location
    Alaska
    Posts
    83

    Inserting Unformated Data to SQL

    Hey...

    Just to let you know these forums have been very helpful. so thank you very much!!!

    I have another question for anyone who may have an answer.

    I have a text database of about 684 Entries. It is basically unformatted besides the normal spaces between data columns.

    So for Example*

    James Denver 22
    Fred Portland 25
    Nancy Miami 86

    My question is:

    Is there a way to upload all the info from the text file? If so, does it have to be formatted to SQL? If so, how do I do it? Or is there a program out there that can do it for me? So it'll create a table and place all the info in the right columns for me?

    Thanks in advance.
    "Play the best song in the world, or I'll eat your soul."
    I am a D fan in the arctic wastes of Alaska.

  2. #2
    Community Leader jason's Avatar
    Join Date
    Sep 2001
    Location
    Rochester, NY
    Posts
    5,884
    I could have swarn that there was some way to do this automatically in phpMyAdmin, but I can't find it right now. Maybe it was taken out of the CPanel version for some reason.

    The easiest way to do this, barring finding the phpMyAdmin way, is probably to write a short php script to do the task.

    PHP Code:
    <?php
        $data 
    file("./YOURFILE.TXT"); //read in the file

        //make the db connection
        
    $db mysql_connect('localhost''USER''PASS');
        
    mysql_select_db('DBNAME'$db);

        
    //loop thru each line of the file
        
    for($i=0$i count($data); $i++) {
            
    //split the data into parts
            
    list($name$city$age) = explode(" "$data[$i]);

            
    //insert it into the DB
           
    mysql_query("INSERT INTO TABLENAME(Name, City, Age) VALUES($name$city$age);"$db);
        }
        
    mysql_close($db);
    ?>
    You'll of course have to modify this to your individual circumastances and it may take a little trial and error to get it working correctly, so don't experiment on a "production" database.

    Good luck.

    --Jason
    Jason Pitoniak
    Interbrite Communications
    www.interbrite.com www.kodiakskorner.com

  3. #3
    Loyal Client
    Join Date
    Oct 2003
    Location
    Alaska
    Posts
    83
    Thanks Jason. Your answers always seem to by nice, and non-judgemental. It's appreciated.

    Anyways...I did find the way to do it through phpadmin. Thank god I had a pipe delimited copy of the database as well. Anyways click on your database, then on a table, when it loads, you'll see at the very bottom words that say...

    "Insert data from a textfile into table"

    Click on it.

    All I had to do after that was find the file on my HD...change the Fields terminated by field to a pipe(|). And then change the lines terminated by to just \r. Select data local and save. It loaded just fine...

    WOOT!

    On another note....your table needs to have fields that are equal or more then the amount of fields in the text file.

    Thanks again Jason.
    "Play the best song in the world, or I'll eat your soul."
    I am a D fan in the arctic wastes of Alaska.

  4. #4
    Community Leader jason's Avatar
    Join Date
    Sep 2001
    Location
    Rochester, NY
    Posts
    5,884
    I knew that option was there somewhere, I just couldn't find it when I was looking the other day. I'm glad you found it. Its definitely the easiest way to import your data.

    --Jason
    Jason Pitoniak
    Interbrite Communications
    www.interbrite.com www.kodiakskorner.com

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
  •