quickest way to import data

[Fandelem]

i have a 2.5meg mysqldump file -- and i need to import it into another database, however the file needs to be stripslashes()'d -- how can i achieve this? i believe every line terminates with a ';' -- however i can't say "go until '\n'" because there are \n's and \r\n's in the fields. so i need some way to say something like.. "loop through the file until eof, grabbing buffer length up to ';' then stripslash it, then write to an output file..

so far, i have this (it doesn't work)


$infile = "lyrics.sql";
$outfile = "lyrics_out.sql";
$fp = fopen("$infile","r+");

while (!feof($fp)) {

$read_again = true;
$buffer_size = '4096';
$full = "";
while ((!feof ($fp)) && ($read_again)) {
$read_again = false;
$line = fgets ($infile, $buffer_size);
$full .= stripslashes($line);
fwrite($fp, $full);
if (strlen($line) >= ($buffer_size - '1')) {
if ($line[($buffer_size - 1)] != ';') {
$read_again = true;
}
}
}
}


thanks :D

~kyle

[jemfinch]

Post a sample file, if you can. It's far easier to write to an example than to a specification.

I have no idea what mysqldump file looks like.

Jeremy

[YaRness]

what the hell is stripslash?

is that a shell script?

where's my teddy bear?

:confused:

you could do everything, except perhaps the stripslash part, with just a good script language with regexps (perl, python... umm, i dunno if there are any others). and could prolly find a simple hack to get the chunks between semicolons stripslashed (though it may involve some ungainly communication between processes).

<edit> i seem to have a "Fandelem" directory in my scripts directory, so forget i mentioned python, since he's clearly been wooed to the dark side which is perl already :D

[ 26 April 2001: Message edited by: YaRness ]

[Fandelem]

(this is going to make this post look really annoying i fear)

INSERT INTO lyrics VALUES (86,'Black Sabbath','Trashed (4:10)','Ahhh, Ah\r\n<br>\r\nIt really was a meeting\r\n<br>\r\nThe bottle took a beating\r\n<br>\r\nThe ladies of the manor\r\n<br>\r\nWatched me climb into my car\r\n<br>\r\nAnd I was going down the track about a hundred and five\r\n<br>\r\nThey had the stopwatch rolling\r\n<br>\r\nHad the headlights blazing I was really alive\r\n<br>\r\nAnd yet my mind was blowing\r\n<br>\r\nI drank a bottle of tequila and I felt real good\r\n<br>\r\nI had the tape deck roaring\r\n<br>\r\nBut on the twenty-fifth lap at the canal turn I went off\r\nexploring\r\n<br>\r\n\r\n<br>\r\nI knew I wouldn\'t make it the car just couldn\'t take it\r\n<br>\r\nI was turning, tires burning\r\n<br>\r\nThe ground was in my sky\r\n<br>\r\nI was laughing. The ***** was trashed\r\n<br>\r\nAnd death was in my eye\r\n<br>\r\n\r\n<br>\r\nStarted pretty good and I was feeling my way\r\n<br>\r\nHad the wheels in motion\r\n<br>\r\nThere was Peter and the Greenfly laughing like drains\r\n<br>\r\nInebri-ocean\r\n<br>\r\nCrowd was roaring I was at Brands Hatch in my imagination\r\n<br>\r\nAt the canal turn I hit an oily patch\r\n<br>\r\nInebriation\r\n<br>\r\n\r\n<br>\r \nKnew I wouldn\'t make it the car just couldn\'t take it\r\n<br>\r\nI was turning, tires burning\r\n<br>\r\nThe ground was in my sky\r\n<br>\r\nI was laughing. The ***** was trashed\r\n<br>\r\nAnd death was in my eye\r\n<br>\r\n\r\n<br>\r\nOoh Mr. Miracle you saved me from some pain\r\n<br>\r\nI thank you Mr. Miracle I won\'t get trashed again\r\n<br>\r\nOoh can ya hear my lies?\r\n<br>\r\nDon\'t you bother with this fool just laugh into my eye\r\n<br>\r\nI was turning, tires burning\r\n<br>\r\nThe ground was in my sky\r\n<br>\r\nI was laughing. The ***** was trashed\r\n<br>\r\nDeath was in my eye\r\n<br>\r\n\r\n<br>\r\nOoh Mr. Miracle you saved me from some pain\r\n<br>\r\nI thank you Mr. Miracle I won\'t get trashed again\r\n<br>\r\nOoh can ya hear my lies?\r\n<br>\r\nDon\'t you bother with this fool just laugh into my eye...\r\n<br>\r\n\r\n<br>\r\nBack to the bar and hit the bottle again\r\n<br>\r\nBut there was no tequila\r\n<br>\r\nThen we started on the whiskey just to steady our brains\r\n<br>\r\n\'Cos there was no tequila\r\n<br>\r\nAs we drank a little faster at the top of our hill\r\n<br>\r\nWe began to roll\r\n<br>\r\n(And) as we got trashed we were laughing still\r\n<br>\r\nWell bless my soul!\r\n<br>\r\nYo ho jack off','Born Again','0000-00-00 00:00:00',2,'2001-04-21 03:17:28','');

that is one line. i have about 2200 of them ;o)

any language would be cool.. it is just a matter of replacing \' with ' throughout the file.. so you could probably do it with one swoop in perl.. hmmm.. that gets me thinking now.. something like..

s/\\'/'/gs;

?

thanks =)

[ 26 April 2001: Message edited by: Fandelem ]

[YaRness]

yeah. if that's all you need, you should be able to do that easily F. i think we did enough stuff on the scripts we worked on so that you could figure that out yerself. i'd suggest giving it a shot and asking if ya need any help. and look up the s/// operator just to see if there's any other useful tags.

short of it is what yer asking could pretty much be done with a perl one-liner.

[nanode]

This is a job for my latest project:
PHP Audio Warehouse (http://beta.yunt.net/paw/lite)

If I get an error in my DB, I can always recreate from using a PHP app. I hope you weren't inserting those mp3s manually :)

[Mikey123]

Have you looked at mysql.com?
http://www.mysql.com/doc/L/O/LOAD_DATA.html or http://www.mysql.com/doc/m/y/mysqlimport.html


I'm sure you should be able to find a pre-existing utilty to do what you want.

Let us know.

Doesn't mysqldump quote what needs to be quoted anyways?

[ 26 April 2001: Message edited by: Mikey123 ]

[Mikey123]

I think I missed your original intent. You want to unquote the quoted file to import it into another db? Mysql db? if so why would you unquote it? there is probably a command line switch for mysqldump to not quote the dump, although i have never used mysqldump.

[jcrowe]

why not just write a page in php to send old info into the new database.

jcrowe

[Fandelem]

i fixed up my php script and it did it fine

laugh.


Query OK, 2289 rows affected (1.12 sec)
Records: 2289 Deleted: 0 Skipped: 0 Warnings: 9785


*ponders what those warnings were*

[YaRness]

Originally posted by Fandelem:
<STRONG>Warnings: 9785</STRONG>'
that's what makes coding fun.