Text Delim insert

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
ronack
Forum Newbie
Posts: 8
Joined: Tue Jan 06, 2004 5:42 pm

Text Delim insert

Post by ronack »

Does anyone know of a script that will take a text delimited file of say 1000 + lines and insert it into a mySQL table.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Sure. Play around with [php_man]file[/php_man]() and/or [php_man]fopen[/php_man]() / [php_man]fread[/php_man]() / [php_man]fgets[/php_man](), [php_man]explode[/php_man](), [php_man]mysq_query[/php_man](). to start with.

There are various of ways depending on how the file actually looks.
ronack
Forum Newbie
Posts: 8
Joined: Tue Jan 06, 2004 5:42 pm

Post by ronack »

That's easy for you to say, I've only been at this for a couple of months now. I'll check the books but I was looking for a little more than that. Something that is already written.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Ah, sorry. But you didn't mention much in your post so I wouldn't know how far you have advanced. =)

Can you give us more info?
Is the file one big 'ol line with stuff? Or are they rather 1000's of lines with words, or sentences, or...

Code: Select all

foo;bar;text;code;1;values
...'garbled' info?

Please copy n' paste a couple of lines in code tags for us to look at, if wanted. Easier to explain with something you can relate to.
Last edited by JAM on Tue Jan 06, 2004 6:21 pm, edited 1 time in total.
User avatar
scorphus
Forum Regular
Posts: 589
Joined: Fri May 09, 2003 11:53 pm
Location: Belo Horizonte, Brazil
Contact:

Post by scorphus »

http://www.hotscripts.com and http://www.scriptsearch.com are surely your friends ;)

Cheers,
Sco.

Edit: URL correction.
Last edited by scorphus on Tue Jan 06, 2004 7:29 pm, edited 1 time in total.
ronack
Forum Newbie
Posts: 8
Joined: Tue Jan 06, 2004 5:42 pm

Post by ronack »

Thanks Jam here is what I have 1000's of links, each link can be a line we can format the lines in the text file. The DB looks like this (hope yous see it all.

Code: Select all

INSERT INTO `gl_pllinks` VALUES (2, 'Site Name', 'http://www.xxxx.com', NULL, '2004-01-06 00:00:00', '', 3, '123abc.gif', '', '', '', 'admin@xxxx.com', 0, 0, '', '');
for the values the 2 is incremental, 3 is the category, we need NULL, Date Time stamp but as you see my test worked with zeros for the time, and we nee the two 0's.

Does that help?
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

Well... still abit confused, as this is the inserting part only, not an example from the actual source... (You arn't moving data from one database to another by any chance?)

But if I didn't misunderstand completely, this example might be interesting?

text.txt :

Code: Select all

PHPDN|http://forums.devnetwork.net|foo@devnetwork.net
FOO|http://www.foo.net|foo@foo.org

Code: Select all

// read the file into an array
    $array = file("text.txt");

    // loop the file, line by line, each time giving $val a new line-data
    foreach ($array as $val) {

        // create a new array using explode() and the delim's
        $data = explode("|",$val);

        // example debug, shows what we have now...
        print_r($data);

        $sql = "INSERT INTO gl_pllinks VALUES
(
    '',             // an auto_inc field can be left ''. incr. itself
    '{$data[0]}',   // in this example, Sitename
    '{$data[1]}',   // in this example, www addy
    NULL,
    NOW(),          // Current date
    '',
    3,
    '123abc.gif',
    '',
    '',
    '',
    '{$data[2]}',   // in this example, Email addy
    0,
    0,
    '',
    '')";

        // debugging, should be the actual call to the database here...
        echo $sql;
    }
It might look funny, but it's only to try to explain whats happening... If I missed the point, feel free to tell me/us more.
mwong
Forum Commoner
Posts: 34
Joined: Sun Dec 28, 2003 2:58 am

Post by mwong »

Example from SAMS Teach Yourself MySQL by Julie C. Meloni
LOAD DATA [LOCAL} INFILE 'file_name.txt'
INTO TABLE table_name
FIELDS TERMINATED BY 'somechar'
ECLOSED BY 'somechar'
ESCAPED BY 'somechar'
LINES TERMINATED BY 'somechar';
I had to do the same thing. Take info from a text file...that worked. I'm not sure how your text file looks like. Maybe you could post a few lines so we can see what it looks like.
ronack
Forum Newbie
Posts: 8
Joined: Tue Jan 06, 2004 5:42 pm

Post by ronack »

I'll have to say you guys are great, and quick. I'll give these a try.

I wish is was going from db to db but unfortunately it's not.
Post Reply