[28547] in Perl-Users-Digest
Perl-Users Digest, Issue: 9911 Volume: 10
daemon@ATHENA.MIT.EDU (Perl-Users Digest)
Tue Oct 31 14:20:29 2006
Date: Tue, 31 Oct 2006 11:20:21 -0800 (PST)
From: Perl-Users Digest <Perl-Users-Request@ruby.OCE.ORST.EDU>
To: Perl-Users@ruby.OCE.ORST.EDU (Perl-Users Digest)
Perl-Users Digest Tue, 31 Oct 2006 Volume: 10 Number: 9911
Today's topics:
Problem using Spreadsheet::Excel <jdbarcelo@gmail.com>
Re: Problem using Spreadsheet::Excel <glex_no-spam@qwest-spam-no.invalid>
Regex question <john@doe.com>
Re: Regex question <jurgenex@hotmail.com>
Re: Regex question <wahab@chemie.uni-halle.de>
Re: Regex question <DJStunks@gmail.com>
Re: Regex question <jl_post@hotmail.com>
Re: Regex question <john@doe.com>
Re: Regex question <DJStunks@gmail.com>
Re: Regex question <tadmc@augustmail.com>
Re: Regex question <wahab@chemie.uni-halle.de>
Re: Regex question <john@doe.com>
Digest Administrivia (Last modified: 6 Apr 01) (Perl-Users-Digest Admin)
----------------------------------------------------------------------
Date: 31 Oct 2006 09:49:02 -0800
From: "dave" <jdbarcelo@gmail.com>
Subject: Problem using Spreadsheet::Excel
Message-Id: <1162316942.608563.58050@e64g2000cwd.googlegroups.com>
Hello all,
I have been searching the archives for a while and can't seem to find a
solution to my problem. The quick and dirty of it is I am pulling data
from an Oracle database via DBI. I am using the following code to pull
data and write it into an excel spreadsheet:
59 while ( my @row = $sth->fetchrow_array() ) {
60 my $array_ref = \@row;
61 $worksheet1->keep_leading_zeros();
62 $worksheet1->write_row( $row_num, 0, $array_ref );
63 $row_num = $row_num + 1;
64
65 my $line = join("|", map{defined($_) ? $_ : ""} @row);
66 print $line, "\n";
67 push(@DOCIDS, "$row[2]");
68 push(@DATA, $line);
69 }
After executing this I fill up the array @DATA and @DOCIDS and I print
each row (ie line 66) so I know I am fetching the data correctly. But
my Excel doc is not being written. If I were to put a die statement at
line 62 it would say "File not found". What does this mean. This is
really driving me nuts! It should work. Let me know if I need to
supply more info. Below is the entire code from script:
1 #!/usr/bin/perl
2
3 use warnings;
4 use strict;
5
6 use DBI;
7 use Spreadsheet::WriteExcel;
8 use Getopt::Std;
9
10 # Database variables
11 # my $ORAINST = "BLAH";
12 my $ORAINST = "BLAH.BLAH.COM";
13 my $DBUSER = "xxxxxx";
14 # my $DBPASSWD = "xxxxxxx";
15 my $DBPASSWD = "xxxxxxxx";
16 my $DBH =
DBI->connect("dbi:Oracle:$ORAINST","$DBUSER","$DBPASSWD") or
17 die "Couldn't open database connection: $!";
18
19 # Options
20 use vars qw/%opt/;
21
22 # Globals
23 my @DATA = ();
24 my $ROW_LIMIT = 65536;
25 my @DOCIDS = ();
26 my $BUFFER_SIZE = 5000000;
27 my $LONG_RAW_TYPE = 113;
28
29
30 my $workbook = Spreadsheet::WriteExcel->new('test.xls');
31 $worksheet1 = $workbook->add_worksheet("test 1");
32
33 # Build initial headings
34 $worksheet1->write(0, 0, 'Supplier Id');
35 $worksheet1->write(0, 1, 'Agreement Id');
36 $worksheet1->write(0, 2, 'Document Id');
37 $worksheet1->write(0, 3, 'Outlet Id');
38 $worksheet1->write(0, 4, 'Supplier Customer Number');
39 $worksheet1->write(0, 5, 'Outlet');
40 $worksheet1->write(0, 6, 'Address 1');
41 $worksheet1->write(0, 7, 'City');
42 $worksheet1->write(0, 8, 'State');
43 $worksheet1->write(0, 9, 'Zip');
44 $worksheet1->write(0, 10, 'Display Name');
45 $worksheet1->write(0, 11, 'Display Type');
46 $worksheet1->write(0, 12, 'Product Name');
47 $worksheet1->write(0, 13, 'UPC');
48 $worksheet1->write(0, 14, 'Wholesaler Item Code');
49 $worksheet1->write(0, 15, 'Order Quantity');
50
51 #&init();
52
53 my $sqlStmt = &sql_statement_setup( $opt{S}, $opt{P} );
54
55 my $sth = $DBH->prepare($sqlStmt) || die "\nPrepare error: $DBI::err
... $DBI::err str\n";
56 $sth->execute() || die "\nExecute error: $DBI::err ...
$DBI::errstr\n";
57
58 my $row_num = 1;
59 while ( my @row = $sth->fetchrow_array() ) {
60 my $array_ref = \@row;
61 $worksheet1->keep_leading_zeros();
62 $worksheet1->write_row( $row_num, 0, $array_ref );
63 $row_num = $row_num + 1;
64
65 my $line = join("|", map{defined($_) ? $_ : ""} @row);
66 print $line, "\n";
67 push(@DOCIDS, "$row[2]");
68 push(@DATA, $line);
69 }
70
71
72
73
74
75
76
77 # Subroutines
78
79 sub init() {
80 my $opt_string = 'hS:P:';
81 getopts("$opt_string", \%opt) or usage();
82 if (!defined($opt{S})) { $opt{S} = 1248; }
83 if (!defined($opt{P})) { $opt{P} = 18; }
84 usage() if $opt{h};
85
86 return 0;
87 }
88
89 sub usage() {
90 print <<EOF;
91 usage: $0 -S <supp id> -P <prgrm id>
92
93 -h :Prints this help message.
94 -S <supplier id> :The supplier id number.
95 -P <program id :The program id number.
96 EOF
97
98 return 1;
99 }
100
101
102 # sql_statement_setup()
103 sub sql_statement_setup() {
104 my $supplier_id = $_[0];
105 my $program_id = $_[1];
106
107 my $sql = <<END;
108 SELECT a.supplier_id,
109 a.agreement_id,
110 a.document_id,
111 a.outlet_id,
112 a.supplier_customer_number,
113 b.outlet_name,
114 c.address_1,
115 c.city,
116 c.state,
117 c.zip,
118 e.display_name,
119 e.display_type,
120 h.product_name,
121 h.upc,
122 (SELECT z.wholesaler_item_code
123 FROM stsdm.product_lu z
124 WHERE z.wholesaler_id = a.supplier_id
125 AND z.p_display_id = f.p_display_id
126 AND z.upc = h.upc
127 AND z.wholesaler_id = a.supplier_id) AS
WHOLESALER_ITEM_CODE,
128 nvl((SELECT z.order_quantity
129 FROM stsdm.product_lu z
130 WHERE z.wholesaler_id = a.supplier_id
131 AND z.p_display_id = f.p_display_id
132 AND z.upc = h.upc),
133 (SELECT max(z.order_quantity)
134 FROM stsdm.product_lu z
135 WHERE z.p_display_id = f.p_display_id
136 AND z.upc = h.upc)) AS ORDER_QUANTITY
137 FROM stsdm.agreement a,
138 sts.outlet b,
139 sts.location c,
140 stsdm.agreement_detail d,
141 stsdm.display e,
142 stsdm.program_display f,
143 stsdm.product_lu h
144 WHERE a.supplier_id = \'$supplier_id\'
145 AND a.program_id = \'$program_id\'
146 AND a.outlet_id = b.outlet_id
147 AND b.loc_id = c.loc_id
148 AND a.agreement_id = d.agreement_id
149 AND d.p_display_id = f.p_display_id
150 AND f.display_id = e.display_id
151 AND f.p_display_id = h.p_display_id
152 ORDER BY a.supplier_id, a.outlet_id, e.display_name
153 END
154
155 return $sql;
156 }
157
------------------------------
Date: Tue, 31 Oct 2006 12:29:13 -0600
From: "J. Gleixner" <glex_no-spam@qwest-spam-no.invalid>
Subject: Re: Problem using Spreadsheet::Excel
Message-Id: <454795de$0$10308$815e3792@news.qwest.net>
dave wrote:
> But
> my Excel doc is not being written. If I were to put a die statement at
> line 62 it would say "File not found". What does this mean.
It means the file it's trying to write isn't found. Put those die
statements back in, and add them to the rest of your code to help you
debug it. You have it for your DBI related code, so why not the rest?
Also, you could narrow down your issue by working only the
WriteExcel related code, once it writes row 0, then add in
some fake data for the rows, then add in the DBI.
> 30 my $workbook = Spreadsheet::WriteExcel->new('test.xls')
or die "Can't create text.xls: $!";
> 31 $worksheet1 = $workbook->add_worksheet("test 1");
Hang on.. you have enabled strict but it doesn't complain about
$worksheet1 not being defined?
> 53 my $sqlStmt = &sql_statement_setup( $opt{S}, $opt{P} );
Don't use '&', also give placeholders a try, in your SQL.
> 54
> 55 my $sth = $DBH->prepare($sqlStmt) || die "\nPrepare error: $DBI::err
> ... $DBI::err str\n";
> 56 $sth->execute() || die "\nExecute error: $DBI::err ...
> $DBI::errstr\n";
> 57
> 58 my $row_num = 1;
> 59 while ( my @row = $sth->fetchrow_array() ) {
Could use fetchrow_arrayref.
> 60 my $array_ref = \@row;
No reason to do that.
> 61 $worksheet1->keep_leading_zeros();
Possibly you only need to call this once.
> 62 $worksheet1->write_row( $row_num, 0, $array_ref );
> 63 $row_num = $row_num + 1;
$worksheet1->write_row( $row_num++, 0, \@row );
> 64
> 65 my $line = join("|", map{defined($_) ? $_ : ""} @row);
What is the map doing that join('|', @row) doesn't do?
------------------------------
Date: Tue, 31 Oct 2006 16:31:59 +0100
From: "Boerni" <john@doe.com>
Subject: Regex question
Message-Id: <ei7q9f$p4h$1@atlas.ip-plus.net>
Hi
I have a string which looks like this
text text %%slotname%% text text %%slotname%%...
What I would like to do, is to get the values between the '%%' signs. The
Problem is, I dont know how many of those %%slotname%% occur in the string.
Is there any way to solve this problem with a regex?
what i've tried is something like:
($slot1, $slot2, $slot3, $slot4, $slot5) = $a_msg =~
/.*?%%(.*?)%%.*?%%(.*?)%%.*?%%(.*?)%%.*?%%(.*?)%%.*?%%(.*?)%%/;
(assuming there are no more than 5 slots in a string)... the Problem is,
this only works if there are exactly 5 slots.
What i'd like is something like:
(@slots) = $a_msg =~ /some regex/
Is there any way to do this?
Thanks in advance
Bernard
------------------------------
Date: Tue, 31 Oct 2006 15:40:55 GMT
From: "Jürgen Exner" <jurgenex@hotmail.com>
Subject: Re: Regex question
Message-Id: <b8K1h.4037$pU3.2959@trndny08>
Boerni wrote:
> I have a string which looks like this
>
> text text %%slotname%% text text %%slotname%%...
>
> What I would like to do, is to get the values between the '%%' signs.
> The Problem is, I dont know how many of those %%slotname%% occur in
> the string.
> Is there any way to solve this problem with a regex?
Why regexp?
I would just split() the string at '%%' and then every other element
contains the values you are looking for.
jue
------------------------------
Date: Tue, 31 Oct 2006 17:11:49 +0100
From: Mirco Wahab <wahab@chemie.uni-halle.de>
Subject: Re: Regex question
Message-Id: <ei7ss9$jp$1@mlucom4.urz.uni-halle.de>
Thus spoke Boerni (on 2006-10-31 16:31):
> I have a string which looks like this
> text text %%slotname%% text text %%slotname%%...
>
> What i'd like is something like:
> (@slots) = $a_msg =~ /some regex/
> Is there any way to do this?
Yes, but you have to 'quote* the %%
properly, otherwise it gets expanded
on the way to the match, like:
...
my $text = 'text text %%slotname%% text text %%slotname%% text';
my $rg = '\%\%([^\%]+)\%\%';
my @slots = $text =~ /$rg/g;
...
Regards
Mirco
------------------------------
Date: 31 Oct 2006 08:18:55 -0800
From: "DJ Stunks" <DJStunks@gmail.com>
Subject: Re: Regex question
Message-Id: <1162311535.048706.324260@e64g2000cwd.googlegroups.com>
Boerni wrote:
> Hi
>
> I have a string which looks like this
>
> text text %%slotname%% text text %%slotname%%...
>
> What I would like to do, is to get the values between the '%%' signs. The
> Problem is, I dont know how many of those %%slotname%% occur in the string.
>
> Is there any way to solve this problem with a regex?
>
> what i've tried is something like:
> ($slot1, $slot2, $slot3, $slot4, $slot5) = $a_msg =~
> /.*?%%(.*?)%%.*?%%(.*?)%%.*?%%(.*?)%%.*?%%(.*?)%%.*?%%(.*?)%%/;
>
> (assuming there are no more than 5 slots in a string)... the Problem is,
> this only works if there are exactly 5 slots.
>
> What i'd like is something like:
>
> (@slots) = $a_msg =~ /some regex/
use the /g regular expression modifier.
-jp
------------------------------
Date: 31 Oct 2006 08:19:29 -0800
From: "jl_post@hotmail.com" <jl_post@hotmail.com>
Subject: Re: Regex question
Message-Id: <1162311569.735314.314540@f16g2000cwb.googlegroups.com>
Boerni wrote:
>
> I have a string which looks like this
>
> text text %%slotname%% text text %%slotname%%...
>
> What I would like to do, is to get the values between the '%%' signs. The
> Problem is, I dont know how many of those %%slotname%% occur in the string.
>
> Is there any way to solve this problem with a regex?
>
> What i'd like is something like:
>
> (@slots) = $a_msg =~ /some regex/
You're very close, Bernard. You just need to learn about the "/g"
option used with regular expressions. If you read "perldoc perlop" in
the "m/PATTERN/cgimosx" section, you'll see this text:
The "/g" modifier specifies global pattern matching--that is,
matching as many times as possible within the string. ...
In list context, it returns a list of the substrings matched
by any capturing parentheses in the regular expression.
Therefore, write your code with the "/g" option, like:
@slots = $a_msg =~ m/%%(.*?)%%/g;
and all the text found between "%%" will be placed as an element in the
@slots array.
I hope this helps, Bernard.
-- Jean-Luc
------------------------------
Date: Tue, 31 Oct 2006 17:23:16 +0100
From: "Boerni" <john@doe.com>
Subject: Re: Regex question
Message-Id: <ei7tae$pa4$1@atlas.ip-plus.net>
I would just split() the string at '%%' and then every other element
> contains the values you are looking for.
yep...thats how I solved it in the meantime.. but somehow i wondered if it
could be done with a regex.
Thank you very much anyway
------------------------------
Date: 31 Oct 2006 08:25:43 -0800
From: "DJ Stunks" <DJStunks@gmail.com>
Subject: Re: Regex question
Message-Id: <1162311943.146765.53480@m7g2000cwm.googlegroups.com>
Mirco Wahab wrote:
> Thus spoke Boerni (on 2006-10-31 16:31):
>
> > I have a string which looks like this
> > text text %%slotname%% text text %%slotname%%...
> >
> > What i'd like is something like:
> > (@slots) = $a_msg =~ /some regex/
> > Is there any way to do this?
>
> Yes, but you have to 'quote* the %%
> properly, otherwise it gets expanded
> on the way to the match, like:
>
> ...
> my $text = 'text text %%slotname%% text text %%slotname%% text';
> my $rg = '\%\%([^\%]+)\%\%';
????
maybe you should start trying some of this stuff before posting?
-jp
------------------------------
Date: Tue, 31 Oct 2006 10:34:55 -0600
From: Tad McClellan <tadmc@augustmail.com>
Subject: Re: Regex question
Message-Id: <slrnekeupf.2dq.tadmc@tadmc30.august.net>
Boerni <john@doe.com> wrote:
> I have a string which looks like this
>
> text text %%slotname%% text text %%slotname%%...
>
> What I would like to do, is to get the values between the '%%' signs. The
> Problem is, I dont know how many of those %%slotname%% occur in the string.
>
> Is there any way to solve this problem with a regex?
Use m//g in a list context:
my @matches = $a_msg =~ /%%(.*?)%%/g;
--
Tad McClellan SGML consulting
tadmc@augustmail.com Perl programming
Fort Worth, Texas
------------------------------
Date: Tue, 31 Oct 2006 17:36:45 +0100
From: Mirco Wahab <wahab@chemie.uni-halle.de>
Subject: Re: Regex question
Message-Id: <ei7ub2$12k$1@mlucom4.urz.uni-halle.de>
Thus spoke DJ Stunks (on 2006-10-31 17:25):
> Mirco Wahab wrote:
>> my $rg = '\%\%([^\%]+)\%\%';
>
> ????
>
> maybe you should start trying some of this stuff before posting?
Upps, yes ... I should at least have tried it once ...
a simple
...
my $text = 'text text %%slotname%% text text %%slotname%% text';
my @slots = $text =~ /%%([^%]+)%%/g;
...
would, of course, have done it ...
(I was somehow influenced by the possibility
to get the %'s expanded somehow ...)
Regards & Thanks
M.
------------------------------
Date: Tue, 31 Oct 2006 18:18:05 +0100
From: "Boerni" <john@doe.com>
Subject: Re: Regex question
Message-Id: <ei80gg$ph9$1@atlas.ip-plus.net>
Thanks alot for your help everyone... i'm going to read up on the /g
modifier
Bernard
------------------------------
Date: 6 Apr 2001 21:33:47 GMT (Last modified)
From: Perl-Users-Request@ruby.oce.orst.edu (Perl-Users-Digest Admin)
Subject: Digest Administrivia (Last modified: 6 Apr 01)
Message-Id: <null>
Administrivia:
#The Perl-Users Digest is a retransmission of the USENET newsgroup
#comp.lang.perl.misc. For subscription or unsubscription requests, send
#the single line:
#
# subscribe perl-users
#or:
# unsubscribe perl-users
#
#to almanac@ruby.oce.orst.edu.
NOTE: due to the current flood of worm email banging on ruby, the smtp
server on ruby has been shut off until further notice.
To submit articles to comp.lang.perl.announce, send your article to
clpa@perl.com.
#To request back copies (available for a week or so), send your request
#to almanac@ruby.oce.orst.edu with the command "send perl-users x.y",
#where x is the volume number and y is the issue number.
#For other requests pertaining to the digest, send mail to
#perl-users-request@ruby.oce.orst.edu. Do not waste your time or mine
#sending perl questions to the -request address, I don't have time to
#answer them even if I did know the answer.
------------------------------
End of Perl-Users Digest V10 Issue 9911
***************************************
