Inserting Unformated Data to SQL

[kornmonkie]

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.

[jason]

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
    $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

[kornmonkie]

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.

[jason]

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