How to push SQL Query from Console Output into an Excel File?

Question

I'm new at coding and so here.

Right now I'm creating an perl script, which automatically creates an excel file with the output of an SQL Query.

SQL Query:

init_db_connections();
my @row;
my $curHnd = INV::DBI::execute('----'.':------') or die $INV::DBI::errstr;
while ($row[0] = $curHnd->fetchrow_hashref()) {
        printf("Row1: >%s<\n", $row[0]{Row1}),
        printf("Row2: >%s<\n", $row[0]{Row2}),
        printf("Row3:      >%s<\n", $row[0]{Row3})
}
exit 0;

sub init_db_connections {
    INV::DBI::init({
        ------         => '--------',
        ------- => q{select Row1, Row2, Row3
from table1
}

Create the Excel:

my $workbook = Excel::Writer::XLSX->new( 'perl.xlsx' );
my $worksheet = $workbook->add_worksheet();
my $format = $workbook->add_format();
$format->set_bold();
$format->set_color( 'black' );
$format->set_underline;
 
my $col = my $row = 0;
$worksheet->write( $row, $col, 'SQL Report', $format );

$workbook->close();

My Problem is now that i don't know how i can combine these two, so that the Query gets automatically pushed into the Excel.

Any Ideas would be great.

Your Excel file seems to be empty. And you do not seem to print your SQL query to file. — TLP
– TLP, Commented Jun 30, 2021 at 14:11
Is there something wrong with combining the code and printing directly to the worksheet with the rows from the SQL? What have you tried and how did that not work? — TLP
– TLP, Commented Jun 30, 2021 at 14:18
If you do not care enough about your question to answer comments, it is unlikely that other people will. — TLP
– TLP, Commented Jun 30, 2021 at 21:39

Boyd · Accepted Answer · 2021-07-01 21:27:25Z

I think your problem is with the hashref and dereferencing it properly. It's a subtle mistake easily made when just starting out.

my $hashref;
while ( $hashref = $curHnd->fetchrow_hashref() ) {
        printf("Row1: >%s<\n",      $hashref->{Row1}),
        printf("Row2: >%s<\n",      $hashref->{Row2}),
        printf("Row3:      >%s<\n", $hashref->{Row3})
}

A hashref is a reference to a hash and they are sweet, especially when you use postfix dereferencing like I've done in the example. The $hash{row1} code you were using is for accessing the value for row1 in %hash. (and for completeness, the old way of dereferencing a hashref would be ${$hashref}{row1} )

You don't really need @row array there. You were only ever assigning to the first element $row[0], so why not just use a scalar.

As for writing out to Excel, I think you'll be using the write method inside the while loop and incrementing the row counter with $row++ .

If you're going to be doing a lot of DBI coding, pick up a copy of Programming the Perl DBI by Descartes and Bunce for chapters 4 and 5. Old but still incredibly useful. (still got mine)

Collectives™ on Stack Overflow

How to push SQL Query from Console Output into an Excel File?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related