PyrexKidd has asked for the wisdom of the Perl Monks concerning the following 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;
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/;
which, well... didn't work.<.p>
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;
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";
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
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.
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re: timtowtdi but which is the [quote fingers]best[/quote fingers]
by GrandFather (Saint) on Apr 14, 2011 at 07:48 UTC | |
by AnomalousMonk (Archbishop) on Apr 14, 2011 at 18:26 UTC | |
|
Re: timtowtdi but which is the [quote fingers]best[/quote fingers]
by JavaFan (Canon) on Apr 14, 2011 at 08:44 UTC | |
by BrowserUk (Patriarch) on Apr 14, 2011 at 09:14 UTC | |
| |
|
Re: timtowtdi but which is the [quote fingers]best[/quote fingers]
by Your Mother (Archbishop) on Apr 14, 2011 at 14:45 UTC |