Coding Assistance: Parsing a file then inputting in MySQL

Posts: 766 · Posted: Wed Sep 01, 2004 1:25 pm

I need a little coding assistance for my new site (not related to HostMySite.com) so I thought I might post it here so all could benefit from the solution.

Problem: I'm creating a database application for trading cards, and I would like to populate the database WITHOUT manually inputting some 3000 odd card details. I've found a text file on the internet that lists all of the cards and most of the details I need, so I'm going to simply import them.

To do this, I need a perl script that performs the following:

1. Opens the text file and reads the information, which is in the following format:

Card Name: "Famous Deaths" Judges
Card Set: Monty Python Set
Card Color: B
Mana Cost: 4B
Type & Class: Creature - Judges
Pow/Tou: 3/3
Card Text: T: Put a +1/+1 counter on each black creature. Use this ability only when a creature is put in a graveyard from play.
Flavor Text: "We are proud to be bringing to you one of the evergreen bucket kickers."
Artist: Flying Circus, Episode 1
Rarity: U
Card ID: 30205043

Card Name: "Twit of the Year" Contest
Card Set: Monty Python Set
Card Color: Z
Mana Cost: 1WU
Type & Class: Enchantment
Pow/Tou:
Card Text: No spell with more than two words in its name may be played.
Flavor Text: "Now they're under starter's orders ... and they're off... Ah no, they're not. No, they didn't realize they were supposed to start."
Artist: And Now for Something...
Rarity: U
Card ID: 30205106

Card Name: [Goblin token card]
Card Set: Unglued
Card Color: X
Mana Cost:
Type & Class: N/A
Pow/Tou:
Card Text:
Flavor Text:
Artist: Pete Venters
Rarity: U
Card ID: 5503

2. Some of the information I don't really want (Artist and Card ID) but the rest needs to be added to a MySQL database through a DSN connection.

I can get into the specifics on the MySQL database later - that's under construction right now. Any suggestions offhand?

Posts: 17 · Posted: Wed Sep 01, 2004 10:43 pm

Code:

#!/usr/bin/perl
#################################
# We will be adding code here #
# will probably look like: #
# use DBI;
#################################
use strict;
#################################
# We will be adding code here #
#################################
# Probably something like #
# my $dbh = DBI->connect(qq(DBI:mysql:database=dbname),
# 'name', 'pass');
#################################

my $IN = 'cards.txt'; # card txt database
open (IN, '<', $IN) or die "Can't open $IN $!\n";

my %card; # data from one card
my ($term, $data); # data from one line
while(<IN>) # foreach line of the file
{
while (chomp){}; # remove trailing ws
if (length $_) # Line has data?
{
if (m/\:/) # does it have a ":"
{
($term, $data) = split /:/; # split at the ":"
while (chomp $term){} # trailing spaces
$data =~ s/^ *//; # leading spaces
$card{$term} = $data; # store in Card hash
}
else
{ # is a continued line
while (chomp){} # clean
$card{$term} .= " $_"; # add it to the data there
}
}
else
{ # it is an "end of card" blank line
storecard(%card); # add to database
%card=(); # clear the data
}
}
if (keys %card){storecard(%card)};
close IN;

sub storecard
{
my %card = @_;
my $sql;
##################################
# More code will be added here #
##################################
my $sth = $dbh->prepare($sql);
$sth->execute;
}
sub textornull
{
my ($text) = @_;
if ($text && length($text)>0)
{
$text = $dbh->quote($text);
}
else
{
$text = "NULL";
}
return $text;
}
1;