[KLUG Members] Inserting data into MySQL]

[Buist Justin]

I don't know if this will help you at all or not... but I'll toss out a skeleton of sorts of what I'd do to tackle the solution.

BTW... I'm taking the really lazy route and just printing the SQL right to stdout rather than update the DB.

#!/usr/bin/perl

#Map the text-file field name to the DB column name.
#create an entry for each Field: value in the text file
#if you don't want to record it give it the value "ignore"

$db_field_name{"Card Name"} = "txtName";
$db_field_name{"Card Color"} = "txtColor";
$db_field_name{"Artist"} = "ignore"; 
#... etc, etc.

# Assuming nothing fails... 
open(DATA, "mtg-deck.txt");
# No idea if you need these or not...
$deck_name = <DATA>;
$manufacturer = <DATA>;
$release_date = <DATA>;

# run the file right past the statistical information
# which we'll be able to generate once we've got our
# DB anyway
while($_ !~ m/^Card Name:/) {
	<DATA>;
}

#begin processing card by card
do { # each line from the DATA file will get pulled into the internal $_ variable
	chomp; # pull whitespace off $_
	if ($_ =~ m/^Card Name:/) {
		if ($#fields > 0) {
			print "INSERT INTO tblMtg " . 
				"(" . join(',', @fields) . ") " .
				" VALUES " . 
				"(" . join(',', @values) . ");\n";
		}
		@fields = (); # I think that's valid.. I'm rusty.
		@values = ();
	}

	($field, $data) = split(/:/, $_);
	
	$db_field = $db_field_name{$field};
	if (length($db_field) != 0 && $db_field ne "ignore") { # or is that strlen() ?
		# apparently this line started with a field we care about.
		push(@fields, $db_field);
		push(@values, "'" . some_escape_function($data) . "'");
	} elsif ($db_field ne "ignore") {
		# it's a continuation of a previous field
		# -or- we didn't define it in the db_field_name hash.
		chop($values[$#values]); # remove that closing ' we previously put onto the value
		$values[$#values] .= some_escape_function($_) . "'"; # add the whole line of data and close it with a ' again
	}
} while (<DATA>);
close(DATA);

Hope that helps... the previous post I saw didn't look like it handled data that spanned multiple lines.  I might have missed that though.

Justin Buist


> -----Original Message-----
> From: randy perry [mailto:randallp@domain-logic.com]
> Sent: Wednesday, August 07, 2002 10:30 AM
> To: members@kalamazoolinux.org
> Subject: [KLUG Members] Inserting data into MySQL]
> 
> 
> Yeah, I could post this to a SQL group, but I trust the noodledge in
> this group. (Besides, I don't belong to any SQL list).
> 
> We need to be able to parse text files similar to the following:
> 
> <text data>
> Judgment Spoiler List
> Wizards of the Coast, Inc.
> (Released May 21, 2002)
> 
> 143 Cards Total
> Color: 16 Black, 26 Blue, 33 Green, 27 Red, 33 White, 3 Land,
> 4 Multicolored
> 
> Rarity: 55 Common, 44 Uncommon, 44 Rare
> 
> Card Name:	Ancestor's Chosen
> Card Color:	W
> Mana Cost:	5WW
> Type & Class:	Creature - Cleric
> Pow/Tou:	4/4
> Card Text:	"First strike When Ancestor's Chosen comes into play, 
> 		you gain 1 life for each card in your graveyard."
> Flavor Text:	Empowered by generations of strength.
> Artist:		Pete Venters
> Rarity:		U
> Card #:		1/143
> 
> Card Name:	Aven Warcraft
> Card Color:	W
> Mana Cost:	2W
> Type & Class:	Instant
> Pow/Tou:	n/a
> Card Text:	Creatures you control get +0/+2 until end of turn. 
> 		Threshold - Creatures you control also gain protection 
> 		from the color of your choice until end of turn. (You 
> 		have threshold if seven or more cards are in your 
> 		graveyard.)
> Flavor Text:	n/a
> Artist:		Roger Raupp
> Rarity:		U
> Card #:		2/143
> </text data>
> 
> Ok, the tags aren't going to be there, just what is in 
> between them.  I
> snipped it down to 2 to show you, but there are really 
> hundreds.  There
> are also a lot of other lists that each have a hundred or so records.
> 
> I know that it won't port directly, so I am looking for any 
> solution of perl, php, whatever. The solution should format 
> the text and precede it with insert into cards 
> values(....whatever).  (php on webpage
> PHP is preferred, but I could set that up as a front end to 
> any solution.
> Ideas??? Anyone?? Anyone?
> 
> 
> Randall Perry
> 
> 
> _______________________________________________
> Members mailing list
> Members@kalamazoolinux.org
> 
>