split a line into 2 variables and store in SQL

sharan has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, I am struggling to write a program in which i have to read many lines and then stores the values in 2 different variables and then transfer them into sql. My output is as:

apple <= banana;
cat := dog;
elephant <= flag;
 and so on....
[download]

i want to store apple, cat , elephant.. in one register say as $1 and banana, dog, flag in another variable as $2. Then transfer the $1 into one column of table in Sql and $2 into another column of table. my code looks like..

while(<>)
{
if(/case .* is/)    
        { if(/then/){
            @words = split(/=/, $_);
                print "$_";
                            
            $sth->execute($_[0], $_[1]) or die $DBI::errstr;
                       #even tried with @words[0] and @words[1].
    }}
[download]

But am unable to get the desired output. Please help me... Many thanks in advance..

Comment on split a line into 2 variables and store in SQL Select or Download Code

Replies are listed 'Best First'.
Re: split a line into 2 variables and store in SQL by moritz (Cardinal) on Jan 23, 2009 at 10:02 UTC
Since you store the result of the split in the array `@word`, you actually have to use it: `$sth->execute($words[0], $words[1]) or die $DBI::errstr;` [download] (As a side node $1 is not a register, but a (special) variable that is set by the regex engine.	[reply] [d/l] [select]
Re^2: split a line into 2 variables and store in SQL by sharan (Acolyte) on Jan 23, 2009 at 10:56 UTC
Thanks for ur reply... But still I have some doubt... I tried using `@words = split(/<=/, $_); print "$_"; $sth->execute(@words[0], @words[1]) or die $DBI::errstr;}` [download] But its not working... And will this work to store all the data into the sql table?? Thanks,	[reply] [d/l]
Re^3: split a line into 2 variables and store in SQL by Bloodnok (Vicar) on Jan 23, 2009 at 11:04 UTC
AFAICS, you wouldn't expect a statement of the form `apple banana` to work as its clearly not valid SQL - unless there's something I've missed in the postings thus far ... Maybe some sample output, failing though it is, would help us (to help you) A user level that continues to overstate my experience :-))	[reply] [d/l]
Re^4: split a line into 2 variables and store in SQL by sharan (Acolyte) on Jan 23, 2009 at 11:13 UTC
Re^5: split a line into 2 variables and store in SQL by Bloodnok (Vicar) on Jan 23, 2009 at 11:27 UTC
Re^3: split a line into 2 variables and store in SQL by moritz (Cardinal) on Jan 23, 2009 at 12:20 UTC
`@words[0]` No! I wrote `$words[0]`, and with reasons. But its not working How is it not working? What's the error message (or the error condition that you observe)?	[reply] [d/l] [select]
Re^4: split a line into 2 variables and store in SQL by sharan (Acolyte) on Jan 23, 2009 at 13:30 UTC
Re^5: split a line into 2 variables and store in SQL by moritz (Cardinal) on Jan 25, 2009 at 18:56 UTC
Re: split a line into 2 variables and store in SQL by erix (Prior) on Jan 23, 2009 at 13:00 UTC
With PostgreSQL, you'd use a one-liner: input file: `$ cat sharan.txt apple <= banana; cat := dog; elephant <= flag;` [download] import: `$ perl -ne ' chomp; chop; my@arr=split(); print $arr[0], "\t", $arr[2], "\n";' sharan.txt \ \| psql -d test -c " /* drop table if exists sharan_txt; / create table sharan_txt(col1 text, col2 text); copy sharan_txt from stdin csv delimiter E'\t'; "` [download] check: `$ psql -d test -c "select from sharan_txt" col1 \| col2 ----------+-------- apple \| banana cat \| dog elephant \| flag (3 rows)` [download] hth	[reply] [d/l] [select]