Page 1 of 1
Text Delim insert
Posted: Tue Jan 06, 2004 5:42 pm
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.
Posted: Tue Jan 06, 2004 6:07 pm
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.
Posted: Tue Jan 06, 2004 6:15 pm
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.
Posted: Tue Jan 06, 2004 6:19 pm
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...
...'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.
Posted: Tue Jan 06, 2004 6:20 pm
by scorphus
http://www.hotscripts.com and
http://www.scriptsearch.com are surely your friends
Cheers,
Sco.
Edit: URL correction.
Posted: Tue Jan 06, 2004 6:30 pm
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?
Posted: Tue Jan 06, 2004 6:56 pm
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.
Posted: Tue Jan 06, 2004 7:05 pm
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.
Posted: Tue Jan 06, 2004 7:22 pm
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.