Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
Create a xls spreadsheet and graph from csv file [SOLVED]
View unanswered posts
View posts from last 24 hours

 
Reply to topic    Gentoo Forums Forum Index Portage & Programming
View previous topic :: View next topic  
Author Message
brent_weaver
Guru
Guru


Joined: 01 Jul 2004
Posts: 501
Location: Burlington, VT

PostPosted: Wed Mar 06, 2013 4:42 pm    Post subject: Create a xls spreadsheet and graph from csv file [SOLVED] Reply with quote

I am looking at taking a csv file like:

Code:

date,license 1,license 2, license 3
2/23/2011 05:00am,5,6,9
2/23/2011 05:15am,5,6,5
2/23/2011 05:30am,5,6,8
2/23/2011 05:45am,5,6,9
2/23/2011 06:00am,5,6,5


and convert it into a spreadsheet. The converting it to spreadsheet is fairly easy with Spreadsheet::WriteExecel. I have that down, but want to incorporate the ability to graph the data as well.

This script to convert a file/files into one spreadsheet:

Code:

#!/usr/bin/perl

use Spreadsheet::WriteExcel;

if ($#ARGV != 1){
        print "\nformat: excelFileName commaSaperatedListOfCSVs\n";
        print "example: example.xls \"*.csv\" | sheet1.csv,sheet2.csv\n\n";
        exit ;
}

print "\n** csv to xls converter **\n\n";

if ( grep(/\*/,$ARGV[1]) ){
   quotemeta($ARGV[1]);
   @csv_a = <$ARGV[1]>; }
else {
   @csv_a = split(/,/, $ARGV[1]); }

my $workbook = Spreadsheet::WriteExcel->new($ARGV[0]);

for my $csv (@csv_a){
        print "..processing $csv..\n";
        @name = split(/_/,$csv);
        $sheet=$workbook->add_worksheet($name[0]);
        $sheet->set_column(0,0,40);
   open (CSV, $csv) || die ("Could not open $csv");
        my $row = 0;
        while ( my $line = <CSV>){
                my $col = 0;
                @entries=split (/,/, $line);
                for my $entry (@entries){
                        $sheet->write($row, $col, $entry);
                        $col++;
                }
                $row++;
        }
        close (CSV);
}

$workbook->close();
print "\nFile $ARGV[0] succesfully created!\n\n";


I would like to figure out how to do this, any help is MUCH appreciated! I need to figure out how to get the array val's I read in as data into the spreadsheet.
_________________
Brent Weaver


Last edited by brent_weaver on Fri Mar 22, 2013 1:31 pm; edited 1 time in total
Back to top
View user's profile Send private message
chiefbag
Guru
Guru


Joined: 01 Oct 2010
Posts: 440
Location: The Kingdom of Kerry

PostPosted: Wed Mar 06, 2013 4:50 pm    Post subject: Reply with quote

I depends what you mean by graph the data?
Have you looked at GNUPlot

http://gnuplot.sourceforge.net/demo/
_________________
A place for useful technical information
Back to top
View user's profile Send private message
brent_weaver
Guru
Guru


Joined: 01 Jul 2004
Posts: 501
Location: Burlington, VT

PostPosted: Wed Mar 06, 2013 6:21 pm    Post subject: Reply with quote

Thank you very much for this information. I was hoping to be able to do it within a spreadsheet. By graphing I mean creating a line graph out of the data. There will be multiple col which is what I want to plot.

gnuplot seems a bit more cumbersome then the perl module...
_________________
Brent Weaver
Back to top
View user's profile Send private message
genterminl
Guru
Guru


Joined: 12 Feb 2005
Posts: 336
Location: Connecticut, USA

PostPosted: Thu Mar 07, 2013 4:03 pm    Post subject: Reply with quote

Between this and your other post, it's not clear what your exact problem is. First just work on getting the data into the spreadsheet. This example looks like it places one cell at a time; the other one had one array for the first row, and an array of arrays for the rest of the column data. The one thing I'd pay attention to is the exact format of numbers and dates, and whether you can set formats.

Once you get the data successfully into the spreadsheet, then look about graphing. I have no idea if Spreadsheet::WriteExcel does graphs. One other option, especially if you think the data will be reasonably consistent, is to create the graph in Excel, and just change the data. I know that wouldn't be completely automated - but you might also use Excel macros for the graphing.
Back to top
View user's profile Send private message
brent_weaver
Guru
Guru


Joined: 01 Jul 2004
Posts: 501
Location: Burlington, VT

PostPosted: Fri Mar 22, 2013 1:31 pm    Post subject: Reply with quote

I finally figured this out. It is not optimal code but it works.

Here goes:

Code:

#!/usr/bin/perl

use Spreadsheet::WriteExcel;
use Getopt::Long;
use Switch;

if ($#ARGV != 1){
        print "\nformat: excelFileName commaSaperatedListOfCSVs\n";
        print "example: example.xls \"*.csv\" | sheet1.csv,sheet2.csv\n";
        exit ;
}

if ( -e "$ARGV[0]" ) {
   die "*** Excel files already exists! ***\n"; }

@FILES = split(/,/,$ARGV[1]);

my $workbook    = Spreadsheet::WriteExcel->new($ARGV[0]);
my $BOLD    = $workbook->add_format( bold => 1);
my @DAYS    = ();

foreach(@FILES) {

   chomp();

   unless ( -e "$_" ) {
      print "File $_ does not exist, skipping...\n";
      next;
   } else {
      printf "... Processing file %-20s ...\n",$_; }
   
   open(IN,"$_") or next;
   my @TMP = (<IN>);
   close IN;

   my $HEAD    = shift(@TMP);
   my $LABEL   = shift(@TMP);

   my @LN1    = split(/,/,$HEAD);
   my @LN2      = split(/,/,$LABEL);
   
   if ( grep(/$LN1[2]/,@DAYS) ) {
      print "--> Skipping $LN1[2] as I already processed this day <--\n";
      next;
   } else {
      printf "... Creating tab for %-13s ...\n",$LN1[2]; }
   
   push(@DAYS,"$LN1[2]");
      
   $worksheet   = $workbook->add_worksheet("$LN1[2]");
   
   $worksheet ->set_column('A:K',30);   
   $worksheet->write(0,0,\@LN2,$BOLD);

   my $X = 1;
   foreach(@TMP) {
      chomp();
      @LINE = split(/,/,$_);
      $worksheet->write($X,0,\@LINE);
      $X++;
   }

   my $chart1 = $workbook->add_chart( type => 'line', name => $LN1[2] . ' Graph' );

   my $CNTROW = scalar @TMP;
   my $CNTCOL = scalar @LN2;

   my $X = 2;
   foreach(@LN2) {
      next if ( grep(/Timestamp/,$_) );
      switch ($X) {
         case 2      { $COL = "B" }
         case 3      { $COL = "C" }
         case 4      { $COL = "D" }
         case 5      { $COL = "E" }
         case 6      { $COL = "F" }
         case 7      { $COL = "G" }
         case 8      { $COL = "H" }
         case 9      { $COL = "I" }
         case 10      { $COL = "J" }
         case 11      { $COL = "K" }
      }   
      $chart1->add_series(
           categories => '=' . $LN1[2] . '!$A$2:$A$' . $CNTROW,
           values     => '=' . $LN1[2] . '!$' . $COL . '$2:$' . $COL . '$' . $CNTROW,
           name       => $_,
      );
      $X++;
   }

   $LN1[0] = uc($LN1[0]);

   $chart1->set_title( name => $LN1[0] . " License Utilization\n" . $LN1[2] . ", " . $LN1[3] . "\n" . $LN1[4]  );
   $chart1->set_x_axis( name => 'Timestamp', );
   $chart1->set_y_axis( name => 'License Used', );
   #$chart1->set_chartarea( color => 'gray', weight => 'hairline' );
}   
   
$workbook->close();

_________________
Brent Weaver
Back to top
View user's profile Send private message
dmitchell
Veteran
Veteran


Joined: 17 May 2003
Posts: 1159
Location: Austin, Texas

PostPosted: Sat Mar 23, 2013 12:34 am    Post subject: Reply with quote

I suggest R.
_________________
Your argument is invalid.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic    Gentoo Forums Forum Index Portage & Programming All times are GMT
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum