I once had to work with mapping a lot of text data in a spreadsheet to keys in a database. The database tables were for products and the currencies they were sold in. Rather than doing a lot of very tedious (and error-prone) replacement of the text data with the database IDs, I chose to have Perl do it for me.

I exported the text from the spreadsheed into a tab-delimited file. I then wrote a Perl script that read the tables and created hashes for them with the key being the text name and the id being the value. Then, the script opened the text file and rewrote every line using a substitution. Here is the relevant snippet:

open FILE, '+< products.txt' or die $!;

my @lines = 
    map { s/^([^\t]+)\t(.*)/
        exists $product{$1} and exists $currency{$2}
            ? "$product{$1}\t$currency{$2}" 
            : "$1\t$2" /e; $_ } 
        <FILE>;

seek FILE, 0,0;
print FILE @lines;
truncate FILE, tell(FILE);
close FILE;
[download]

The text file was rather large and in about five minutes, I accomplished many hours of manual labor. I don't claim that this is terribly maintainable, but this was used to populate about four different lookup tables and then discarded.

Cheers,
Ovid

Update: I forgot to mention one little problem: I had gotten the columns reversed. Rather than messing around with the import function in the database, I did the following:

perl -pi.bak -e "s/(\d+)\t(\d+)/$2\t$1/" products.txt
[download]

Join the Perlmonks Setiathome Group or just click on the the link and check out our stats.