my initial question:

what is the best way to translate "TRUE" and "FALSE" to "1" or "0" respectively? or "string a", "String B", "String C" to "1-3" respectively.

my first thought was do a search/replace regex: 

s/TRUE/1/imgsx;
s/FALSE/0/imgsx;

[download]

but my thinking is invoking the regex engine twice causes twice the overhead. while this might not be a problem for the first 10k lines, what about 100k or 100,000k lines? So I thought I could use the tr engine, but that only translates characters. So I tried: 

tr/TRUEFALSE/111100000/;

[download]

which, well... didn't work.<.p>

my relatively closely related tangent

I'm working on an application to build a database. I have a pre existing ''csv database'' which consists of a number of ''csv tables''. the end goal is a MySQL back-end and a web GUI front-end... :]

sidenote: I am not a DBA, any one have any _good_ perl DBI links? I've searched google and the perldocs extensively; I think it's my SQL skills that are lacking...

anyway, during the process of writing the program to convert the database I have had to translate company names to company id's etc. strings to values. see the above for the question about true false values I had that started this post.

since i'm building my query as a string I open my string as a file handle and then print to my string as a file... any one have any comments about that? (not trolling, genuinely interested in feedback)

open my $Q_FH, '>', \my $query;

[download]

which is better: query the database for the customer id's based on the customer name or create a hash and use the has to translate the customer name to customer_id? i.e.: 

#create customer DB
print $Q_FH "INSERT INTO CUSOMER 
           (customer_id, company_name)\n";
print $Q_FH "VALUES";
print $Q_FH join(',', map{"\n($cust_id_href->{$_}, $_)"} keys %$cust_i
+d_href);
print $Q_FH ";\n";

#create vendor DB
print $Q_FH "INSERT INTO VENDOR (vendor_id, company_name)\n";
print $Q_FH "VALUES";
print $Q_FH join(',', map{"\n($vendor_id_href->{$_}, $_)"} keys %$vend
+or_id_href);
print $Q_FH ";\n";

[download]

I use map here because: it's cleaner than a foreach loop, and I only have two values.

here I am only parsing one csv at a time. how would you parse multiple CSV's to create/update/insert data in multiple tables in one DB?

I tried to use Tie::Handle::CSV but when I would attempt to convert the table to an array I would get the follwing error: 
sub table_to_array {
    my $table_fh = shift;
    my $table_aref = ();
    foreach (<$table_fh>){
        push @{$table_aref}, $_ if $_;
    }
    #@{$table_aref} = <$table_fh>;
    return $table_aref;
}


Use of uninitialized value in lc at /usr/local/lib/perl5/site_perl/5.1
+2.3/Tie/Handle/CSV/Hash.pm line 31, <$csv_fh> line 8014

[download]

which goes away if you treat the DB as a flat file instead of a CSV... go figure.

the funny thing is, no matter if the FH was a Tie'd FH or a standard flat FH the sub file_to_array worked: I was able to dereference the $file_aref and print out the array... haha, and looking back on it, the commented line is the original method, I came up with the WTFey method in a vain attempt to fix the 'bug'...

while this is even more off topic: what would you say is a _better_ practice: --creating multiple INSERT statements for all of the data, --or-- --creating one large INSERT statement for all of the data (as I have done above)?

as always thanks for the assistance.

In reply to timtowtdi but which is the [quote fingers]best[/quote fingers] by PyrexKidd

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.