| View previous topic :: View next topic |
| Author |
Message |
brent_weaver
Guru
Joined: 01 Jul 2004
Posts: 510
Location: Burlington, VT
|
 Posted: Wed Mar 06, 2013 4:42 pm Post subject: Create a xls spreadsheet and graph from csv file [SOLVED] |
 |
|
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 |
|
 |
chiefbag
Guru
Joined: 01 Oct 2010
Posts: 542
Location: The Kingdom
|
|
| Back to top |
|
|
brent_weaver
Guru
Joined: 01 Jul 2004
Posts: 510
Location: Burlington, VT
|
| Posted: Wed Mar 06, 2013 6:21 pm Post subject: |
|
|
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 |
|
|
genterminl
Guru
Joined: 12 Feb 2005
Posts: 523
Location: Connecticut, USA
|
| Posted: Thu Mar 07, 2013 4:03 pm Post subject: |
|
|
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 |
|
|
brent_weaver
Guru
Joined: 01 Jul 2004
Posts: 510
Location: Burlington, VT
|
| Posted: Fri Mar 22, 2013 1:31 pm Post subject: |
|
|
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 |
|
|
dmitchell
Veteran
Joined: 17 May 2003
Posts: 1159
Location: Austin, Texas
|
| Posted: Sat Mar 23, 2013 12:34 am Post subject: |
|
|
I suggest R.
_________________
Your argument is invalid. |
|
| Back to top |
|
|
|
|
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
|
|
Portage & Programming
