We help IT Professionals succeed at work.

Perl script writing data to excel

7,077 Views
Last Modified: 2012-08-13
Hi All,
I am very new to perl and having problem writing data to excel sheet..here is my code..pls help..
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::DB2;
use DBD::DB2::Constants;
use Time::Local;
use Spreadsheet::WriteExcel;


my $region = ("NORTH", "SOUTH", "WEST");

# Connect and turn autocommit off
my $dbh = DBI->connect( 'dbi:DB2:eastdit', 'fttpdit', 'dit1234', { AutoCommit => 0 } )
  || die "$DBI::errstr";
my $Excelfile = "/tmp/VWReport.xls";

 #$i= ("South", "North" , "West");
   
    #create a new instance
    my $excel = Spreadsheet::WriteExcel->new("$Excelfile");
    my $worksheet = $excel->addworksheet("VWFalloutNorth");
      #my $worksheet = $excel->addworksheet("VWFalloutSouth");
      #my $worksheet = $excel->addworksheet("VWFalloutWest");
      my $now = localtime time;
      $worksheet->write(0, 0, "Report generated on :$now" );
      my $stmt = "select activity,duration,duedate,errorcode, startdate, order,assignedto from ivapp.voicewing_south_1";
   
      my $sth = $dbh->prepare($stmt);
      my @data;
      
     $sth->execute() or die $dbh->errstr;
      
      while ( @data = $sth->fetchrow_array()){
            my $activity = $data[1];
            my $duration = $data[2];
            my $duedate = $data[3];
            my $errorcode = $data[4];
            my $startdate = $data[5];
            my $order = $data[6];
            my $assignedto = $data[7];
#foreach $stmt (\@data) {
      
#      $worksheet->write_row('A4', \@data);
      my $row = 0;
      my $col = 0;

      #$row++;
      
#}

foreach my $stmt(@data)
                  
                        {
                              
                              $worksheet->write($row++, @data);
                              #$colheader = "Y";
                              last;
                        }

                  
            
      #$worksheet->write_row('A1', \@data);
      #$worksheet->write_row('A2', \@data);
            
            #$worksheet->write('A4', $activity);
      #$worksheet->write('B4', $duration);
      #$worksheet->write('C4', $duedate);
      #$worksheet->write('D4', $errorcode);
      #$worksheet->write('E4', $startdate);
      #$worksheet->write('F4', $order);
      #$worksheet->write('G4', $assignedto);

            
            #print "\t $activity : $duration : $duedate: $errorcode: $startdate: $order: $assignedto \n";
      }
      
print "DONE \n";


      #$worksheet->write_col('A4', $array_ref);
      

$sth->finish();
      $dbh->disconnect();
It is writing only one data to excel ..pls help
Comment
Watch Question

Commented:
You need to clean this code up (remove all the commented out lines and fix indention).

Then you might get some responses.

Author

Commented:
Removed all commnets and posting the script again ..pls help me with this..
#!/usr/bin/perl -w
use strict;
use DBI;
use DBD::DB2;
use DBD::DB2::Constants;
use Time::Local;
use Spreadsheet::WriteExcel;


my $region = ("NORTH", "SOUTH", "WEST");

# Connect and turn autocommit off
my $dbh = DBI->connect( 'dbi:DB2:eastdit', 'fttpdit', 'dit1234', { AutoCommit => 0 } )
  || die "$DBI::errstr";
my $Excelfile = "/tmp/VWReport.xls";
    #create a new instance
    my $excel = Spreadsheet::WriteExcel->new("$Excelfile");
    my $worksheet = $excel->addworksheet("VWFalloutNorth");
            my $now = localtime time;
      $worksheet->write(0, 0, "Report generated on :$now" );
      my $stmt = "select activity,duration,duedate,errorcode, startdate, order,assignedto from ivapp.voicewing_south_1";
   
      my $sth = $dbh->prepare($stmt);
      my @data;
      
     $sth->execute() or die $dbh->errstr;
      
      while ( @data = $sth->fetchrow_array()){
            my $activity = $data[1];
            my $duration = $data[2];
            my $duedate = $data[3];
            my $errorcode = $data[4];
            my $startdate = $data[5];
            my $order = $data[6];
            my $assignedto = $data[7];
              my $row = 0;
              my $col = 0;
foreach my $stmt(@data)
                  
                        {
                              
                              $worksheet->write($row++, @data);
                              last;
                        }
      }
      
print "DONE \n";
      $sth->finish();
      $dbh->disconnect();
      
   
I want to Query the daya base and insert the results in to exxcel..if you can give me the code it will be greatly appricated..
Top Expert 2009

Commented:
my $region = ("NORTH", "SOUTH", "WEST");

# Connect and turn autocommit off
my $dbh = DBI->connect( 'dbi:DB2:eastdit', 'fttpdit', 'dit1234', { AutoCommit => 0 } ) || die "$DBI::errstr";

my $Excelfile = "/tmp/VWReport.xls";
my $excel = Spreadsheet::WriteExcel->new("$Excelfile");
my $worksheet = $excel->addworksheet("VWFalloutNorth");

my $now = localtime time;

$worksheet->write(0, 0, "Report generated on :$now" );


my $stmt = "select activity,duration,duedate,errorcode, startdate, order,assignedto from ivapp.voicewing_south_1";
my $sth = $dbh->prepare($stmt);


my @data;

$sth->execute() or die $dbh->errstr;

my $row = 1;
while ( @data = $sth->fetchrow_array()){
      my $activity = $data[1];
      my $duration = $data[2];
      my $duedate = $data[3];
      my $errorcode = $data[4];
      my $startdate = $data[5];
      my $order = $data[6];
      my $assignedto = $data[7];
      
      for(my $col=0; $col<=$#data; $col++) {
            $worksheet->write($row, $col @data);
      }
      $row++;
}

print "DONE \n";
$sth->finish();
$dbh->disconnect();

Top Expert 2009

Commented:
Typo.... missing comma.  This:
    $worksheet->write($row, $col @data);
Should be:
    $worksheet->write($row, $col, @data);

Author

Commented:
Thanks for the advise..but it not inserting data to excel..i see only one record..in excel...

Commented:
Since I don't know what data your querying or parsing, I can't run this script to test to see what it does...

change
my $Excelfile = "/tmp/VWReport.xls";
to
my $Excelfile= '/tmp/VWReport.xls';

You want to avoid having Perl interpreting your slashes.

Also, I would recommend printing all of your values out to a text file to see if there is data to print to excel first.  That way you'll know if the problem is with a lack of data or with the way your calling/writing to excel.

for example I'd start with your $now variable and print it to the command line or to a log file to see if it has a value.

Lastly, I'd check your syntax.  I think addworksheet should probably be add_worksheet.  This website seems to be a pretty straighforward example on how to use the Spreadsheet::WriteExcel module:
http://homepage.eircom.net/~jmcnamara/perl/WriteExcel.html

Author

Commented:
Hi Adam ..it worked partially..
I am not sure..was my question is in right direction..but the code you gave me worked in a way but i i have data like this..
XXX AAA BBB DDD EEE
YYY SSS FFF GGG HHH and want to insert in the same way in excel...but now i see as XXX YYY in excel..and also some empty data in between them pls...plssssss help

Author

Commented:
This is the qurey result
ESAP-ASSIGN-COMP : 19D 02H 44M  : 07/19/2007: ESP_ACCOUNT_CLOSED: 1186006523
:  : CSSI059221935
         ESAP-ASSIGN-COMP : 10D 09H 20M  : 08/12/2007: ESP_ACCOUNT_CLOSED: 1186760382
:  : CSSI048985886
         ESAP-COMP : 10D 10H 09M  : 08/10/2007: ESP_ACCOUNT_CLOSED: 1186757439
:  : CSSI048466464
         ESAP-ASSIGN-COMP : 10D 10H 10M  : 08/10/2007: ESP_ACCOUNT_CLOSED: 1186757363
:  : CSSI048289472
         ESAP-ASSIGN-COMP : 10D 09H 20M  : 08/12/2007: ESP_ACCOUNT_CLOSED: 1186760381
:  : CSSI048860380
         ESAP-ASSIGN-COMP : 31D 14H 12M  : 06/18/2007: ESP_ACC_NOT_IN_ACTIVE_STATE: 1184928434
:  : VENNNN
I want to insert all the data in to excel..one by one..
ESAP-ASSIGN-COMP : 31D 14H 12M  : 06/18/2007: ESP_ACC_NOT_IN_ACTIVE_STATE: 1184928434: ---- : VENNNN
in one row..(----) no data
Top Expert 2009

Commented:
The script will add data to excel in the same order as your SQL statement.  If you want a different order, it'd be easiest to change it there.

Author

Commented:
Adam pls help me with this..I am not seeing diff data in the excel..same data is repeating..n excel..but the query has diff data as pasted before..
Top Expert 2009

Commented:
What is the output from this:


my $row = 1;
while ( @data = $sth->fetchrow_array()){
      my $activity = $data[1];
      my $duration = $data[2];
      my $duedate = $data[3];
      my $errorcode = $data[4];
      my $startdate = $data[5];
      my $order = $data[6];
      my $assignedto = $data[7];
     
      print "Data:   " . join(", ", @data) . "\n";
      for(my $col=0; $col<=$#data; $col++) {
            $worksheet->write($row, $col @data);
      }
      $row++;
}

Author

Commented:
This is the out put from:
         ESAP-ASSIGN-COMP : 19D 02H 44M  : 07/19/2007: ESP_ACCOUNT_CLOSED: 1186006523
:  : CSSI059221935
Data: ESAP-ASSIGN-COMP,19D 02H 44M ,07/19/2007,ESP_ACCOUNT_CLOSED,1186006523
,CSSI059221935,
         ESAP-ASSIGN-COMP : 10D 09H 20M  : 08/12/2007: ESP_ACCOUNT_CLOSED: 1186760382
:  : CSSI048985886
Data: ESAP-ASSIGN-COMP,10D 09H 20M ,08/12/2007,ESP_ACCOUNT_CLOSED,1186760382
,CSSI048985886,
         ESAP-COMP : 10D 10H 09M  : 08/10/2007: ESP_ACCOUNT_CLOSED: 1186757439
:  : CSSI048466464
Data: ESAP-COMP,10D 10H 09M ,08/10/2007,ESP_ACCOUNT_CLOSED,1186757439
,CSSI048466464,
         ESAP-ASSIGN-COMP : 10D 10H 10M  : 08/10/2007: ESP_ACCOUNT_CLOSED: 1186757363
:  : CSSI048289472
Data: ESAP-ASSIGN-COMP,10D 10H 10M ,08/10/2007,ESP_ACCOUNT_CLOSED,1186757363
,CSSI048289472,
         ESAP-ASSIGN-COMP : 10D 09H 20M  : 08/12/2007: ESP_ACCOUNT_CLOSED: 1186760381
:  : CSSI048860380
Data: ESAP-ASSIGN-COMP,10D 09H 20M ,08/12/2007,ESP_ACCOUNT_CLOSED,1186760381
,CSSI048860380,
         ESAP-ASSIGN-COMP : 31D 14H 12M  : 06/18/2007: ESP_ACC_NOT_IN_ACTIVE_STATE: 1184928434
:  : VENNNN
Data: ESAP-ASSIGN-COMP,31D 14H 12M ,06/18/2007,ESP_ACC_NOT_IN_ACTIVE_STATE,1184928434
,VENNNN,
DONE
But in excel i see this:
ESAP-ASSIGN-COMP      ESAP-ASSIGN-COMP      ESAP-ASSIGN-COMP      ESAP-ASSIGN-COMP      ESAP-ASSIGN-COMP      ESAP-ASSIGN-COMP      ESAP-ASSIGN-COMP
I need this in excel:
Data: ESAP-ASSIGN-COMP,31D 14H 12M ,06/18/2007,ESP_ACC_NOT_IN_ACTIVE_STATE,1184928434
,VENNNN,
Top Expert 2009

Commented:
.....
my $row = 1;
while ( @data = $sth->fetchrow_array()){
      my $activity = $data[1];
      my $duration = $data[2];
      my $duedate = $data[3];
      my $errorcode = $data[4];
      my $startdate = $data[5];
      my $order = $data[6];
      my $assignedto = $data[7];
     
      for(my $col=0; $col<=$#data; $col++) {
            $worksheet->write($row, $col, $data[$col]);
      }
      $row++;
}
.....

Commented:
It seems that your data is coming out of your DB query with commas and colons, but you are not changing the colons to commas which is what excel is expecting.  

You are going to need to substitute the colons with commas.  
Use s/:// on all of your data before pushing it into excel.

Author

Commented:
Hi..Its Just the Print statement which is pringing it...
print "\t $activity : $duration : $duedate: $errorcode: $startdate: $order: $assignedto \n";

Adam thx for the script but i see the last data is inserting in excel..not all the data.i mean only one record(lastone)

Author

Commented:
In Excel:
ESAP-ASSIGN-COMP      31D 14H 12M       06/18/2007      ESP_ACC_NOT_IN_ACTIVE_STATE      1184928434      VENNNN

Only one record..this is really killing me from past 4 days pls help..

Author

Commented:
Hi...can some one give me workaround so that i can Import data from Database to .csv and again to Excel plsssssssss
Top Expert 2009

Commented:
One record repeated, or only 1 row in excel?

Author

Commented:
Hi..Adam..
Only one row in Excel..
ESAP-ASSIGN-COMP      31D 14H 12M       06/18/2007      ESP_ACC_NOT_IN_ACTIVE_STATE      1184928434      VENNNN       
Thx alot Adam or the help in advance
Top Expert 2009

Commented:
what is the output from this:


my $row = 1;
while ( @data = $sth->fetchrow_array()){
      my $activity = $data[1];
      my $duration = $data[2];
      my $duedate = $data[3];
      my $errorcode = $data[4];
      my $startdate = $data[5];
      my $order = $data[6];
      my $assignedto = $data[7];
     
      print  "Data in [$row]:  " . join(", ", @data) . "\n";
      for(my $col=0; $col<=$#data; $col++) {
            $worksheet->write($row, $col, $data[$col]);
      }
      $row++;
}
.....

Author

Commented:
Hi..
Useless use of single ref constructor in void context at ./voicewingdata.pl line 92.
Data in [1]:  ESAP-ASSIGN-COMP, 31D 14H 12M , 06/18/2007, ESP_ACC_NOT_IN_ACTIVE_STATE, 1184928434
, VENNNN,
Data in [1]:  ESAP-ASSIGN-COMP, 19D 02H 44M , 07/19/2007, ESP_ACCOUNT_CLOSED, 1186006523
, CSSI059221935,
Data in [1]:  ESAP-ASSIGN-COMP, 10D 10H 10M , 08/10/2007, ESP_ACCOUNT_CLOSED, 1186757363
, CSSI048289472,
Data in [1]:  ESAP-COMP, 10D 10H 09M , 08/10/2007, ESP_ACCOUNT_CLOSED, 1186757439
, CSSI048466464,
Data in [1]:  ESAP-ASSIGN-COMP, 10D 09H 20M , 08/12/2007, ESP_ACCOUNT_CLOSED, 1186760381
, CSSI048860380,
Data in [1]:  ESAP-ASSIGN-COMP, 10D 09H 20M , 08/12/2007, ESP_ACCOUNT_CLOSED, 1186760382
, CSSI048985886,
Data in [1]:  ESAP-ASSIGN-COMP, 31D 14H 12M , 06/18/2007, ESP_ACC_NOT_IN_ACTIVE_STATE, 1184928434
, VENNNN,
Data in [1]:  ESAP-ASSIGN-COMP, 19D 02H 44M , 07/19/2007, ESP_ACCOUNT_CLOSED, 1186006523
, CSSI059221935,
Data in [1]:  ESAP-ASSIGN-COMP, 10D 10H 10M , 08/10/2007, ESP_ACCOUNT_CLOSED, 1186757363
, CSSI048289472,
Data in [1]:  ESAP-COMP, 10D 10H 09M , 08/10/2007, ESP_ACCOUNT_CLOSED, 1186757439
, CSSI048466464,
Data in [1]:  ESAP-ASSIGN-COMP, 10D 09H 20M , 08/12/2007, ESP_ACCOUNT_CLOSED, 1186760381
, CSSI048860380,
Data in [1]:  ESAP-ASSIGN-COMP, 10D 09H 20M , 08/12/2007, ESP_ACCOUNT_CLOSED, 1186760382
, CSSI048985886,
DONE



Data in Excel:ESAP-ASSIGN-COMP      10D 09H 20M       08/12/2007      ESP_ACCOUNT_CLOSED      1186760382      CSSI048985886       
thx
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
HI Adam.
Thx alot for your help and effort ...
I made small mistake from your script and after going line by line i find some thing which i need to change in accordence with your code..
It worked perfectly......Thxxxxxxxxx alottttttttt hope to seek your help in future also..
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.