Gentoo Forums
Gentoo Forums
Gentoo Forums
Quick Search: in
Perl Syntax help [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: 507
Location: Burlington, VT

PostPosted: Thu Mar 07, 2013 10:55 am    Post subject: Perl Syntax help [SOLVED] Reply with quote

I am using a sample script from cpan utilizing Spreadsheet::WriteExcel. There is a part of the script that does this:

Code:


my $headings = [ 'Category', 'Values 1', 'Values 2' ];
my $data = [
  [ 2, 3, 4, 5, 6, 7 ],
  [ 1, 4, 5, 2, 1, 5 ],
  [ 3, 6, 7, 5, 4, 3 ],
];



What is this syntax? How is it accessed? Here is the whole script context:

Code:

#!/usr/bin/perl -w
   
    ###############################################################################
    #
    # A simple demo of Line charts in Spreadsheet::WriteExcel.
    #
    # reverse('©'), December 2009, John McNamara, jmcnamara@cpan.org
    #
   
    use strict;
    use Spreadsheet::WriteExcel;
   
    my $workbook  = Spreadsheet::WriteExcel->new( 'chart_line.xls' );
    my $worksheet = $workbook->add_worksheet();
    my $bold      = $workbook->add_format( bold => 1 );
   
    # Add the worksheet data that the charts will refer to.
    my $headings = [ 'Category', 'Values 1', 'Values 2' ];
   
    my $headings = qw( Category Values 1 Values 2 );

    my $data = [
        [ 2, 3, 4, 5, 6, 7 ],
        [ 1, 4, 5, 2, 1, 5 ],
        [ 3, 6, 7, 5, 4, 3 ],
    ];
   
    $worksheet->write( 'A1', $headings, $bold );
    $worksheet->write( 'A2', $data );
   
   
    ###############################################################################
    #
    # Example 1. A minimal chart.
    #
    my $chart1 = $workbook->add_chart( type => 'line' );
   
    # Add values only. Use the default categories.
    $chart1->add_series( values => '=Sheet1!$B$2:$B$7' );
    $chart1->add_series( values => '=Sheet1!$C$2:$C$7' );
   
    ###############################################################################
    #
    # Example 2. A minimal chart with user specified categories (X axis)
    #            and a series name.
    #
    my $chart2 = $workbook->add_chart( type => 'line' );
   
    # Configure the series.
    $chart2->add_series(
        categories => '=Sheet1!$A$2:$A$7',
        values     => '=Sheet1!$B$2:$B$7',
        name       => 'Test data series 1',
    );
   
   
    ###############################################################################
    #
    # Example 3. Same as previous chart but with added title and axes labels.
    #
    my $chart3 = $workbook->add_chart( type => 'line' );
   
    # Configure the series.
    $chart3->add_series(
        categories => '=Sheet1!$A$2:$A$7',
        values     => '=Sheet1!$B$2:$B$7',
        name       => 'Test data series 1',
    );
   
    # Add some labels.
    $chart3->set_title( name => 'Results of sample analysis' );
    $chart3->set_x_axis( name => 'Sample number' );
    $chart3->set_y_axis( name => 'Sample length (cm)' );
   
   
    ###############################################################################
    #
    # Example 4. Same as previous chart but with an added series and with a
    #            user specified chart sheet name.
    #
    my $chart4 = $workbook->add_chart( name => 'Results Chart', type => 'line' );
   
    # Configure the series.
    $chart4->add_series(
        categories => '=Sheet1!$A$2:$A$7',
        values     => '=Sheet1!$B$2:$B$7',
        name       => 'Test data series 1',
    );
   
    # Add another series.
    $chart4->add_series(
        categories => '=Sheet1!$A$2:$A$7',
        values     => '=Sheet1!$C$2:$C$7',
        name       => 'Test data series 2',
    );
   
    # Add some labels.
    $chart4->set_title( name => 'Results of sample analysis' );
    $chart4->set_x_axis( name => 'Sample number' );
    $chart4->set_y_axis( name => 'Sample length (cm)' );
   
   
    ###############################################################################
    #
    # Example 5. Same as Example 3 but as an embedded chart.
    #
    my $chart5 = $workbook->add_chart( type => 'line', embedded => 1 );
   
    # Configure the series.
    $chart5->add_series(
        categories => '=Sheet1!$A$2:$A$7',
        values     => '=Sheet1!$B$2:$B$7',
        name       => 'Test data series 1',
    );
   
    # Add some labels.
    $chart5->set_title( name => 'Results of sample analysis' );
    $chart5->set_x_axis( name => 'Sample number' );
    $chart5->set_y_axis( name => 'Sample length (cm)' );
   
    # Insert the chart into the main worksheet.
    $worksheet->insert_chart( 'E2', $chart5 );
   
    __END__


Ultimately I am trying to figure out a way to take a csv file and create a spreadsheet and graph in excel. If I would only figure out how to use the values read in from a flat .csv file??!?!?

Thanks in advance for even looking at this!
_________________
Brent Weaver


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


Joined: 22 Jun 2003
Posts: 1151
Location: Austria

PostPosted: Thu Mar 07, 2013 12:29 pm    Post subject: Reply with quote

I usually use Text::CSV_XS to process files in CSV format.
_________________
Hello 911? How are you?
Back to top
View user's profile Send private message
genterminl
Guru
Guru


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

PostPosted: Thu Mar 07, 2013 3:55 pm    Post subject: Reply with quote

I've long since forgotten the issues with lists vs arrays (that's parens vs square brackets) but I don't think it's important here. (I also don't know why $headings gets assigned twice - the second just overrides the first - so it may just be showing two ways to do it.

Do you have a specific question, or just need a general explanation?

However, it looks like $headings is just an arrays of values to go into the first row of the worksheet, and $data is an array of arrays where each array is a column of values. You should just try running it and play with the values until you are comfortable with how it works. That's what I like about Perl - it's easy to just keep trying different things until you get what you want.
Back to top
View user's profile Send private message
brent_weaver
Guru
Guru


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

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

Thanks for the response. I need to know how to store the following in the same way:

Code:

Date, License 1, License 2, License 3
2/4/13,3,4,5
2/5/13,5,4,3
...

_________________
Brent Weaver
Back to top
View user's profile Send private message
genterminl
Guru
Guru


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

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

They are just text strings, I'm pretty sure (but admit I'm guessing). That's what I was referring to in the other post - you need to be sure things like dates and numbers are passed in in a format Excel will interpret correctly and not mess up, and then also check if you can set the format on the cells. If you want to be sure Excel takes something as text and does not even try to interpret as a number or date, prefix it with a single quote.

"3.4" will be seen as a number
"'3.4" will be seen as the text value "3.4"
"1/1/12" will (probably) be seen as the date value for 1 January 2012
"'1/1/12" will be seen as the text value

Out of curiosity - why are you going through this effort instead of just opening the csv with Excel (or LibreOffice) ?

NOTE: I have not used this perl module - I would check it's manual and examples to be sure it wants all values as text, or if it actually does something special with numbers or dates. The issue is that Excel does not always do what you want or expect, for example trimming leading 0's even if you consider a string of digits as an ID and not really a number. With dates - you also have to be careful of date/month vs month/date.
Back to top
View user's profile Send private message
dataking
Apprentice
Apprentice


Joined: 20 Apr 2005
Posts: 196

PostPosted: Thu Mar 07, 2013 10:34 pm    Post subject: Re: Perl Syntax help Reply with quote

brent_weaver wrote:

Code:


my $headings = [ 'Category', 'Values 1', 'Values 2' ];
my $data = [
  [ 2, 3, 4, 5, 6, 7 ],
  [ 1, 4, 5, 2, 1, 5 ],
  [ 3, 6, 7, 5, 4, 3 ],
];



What is this syntax? How is it accessed? Here is the whole script context:


Here, $headings is a reference to an array and (IIRC) can be accessed like:
Code:

# get the value
print $headings->[0]."\n";
# output:  Category

# set/change the value
$headings->[0] = 'Categories';


And $data is a reference to an array of arrays and can be accessed/set similarly to $headings, except that you need the double index.
Code:

# gets the value
print $data->[0][0];
# output:  2


brent_weaver wrote:

Thanks for the response. I need to know how to store the following in the same way:

Code:
Code:

Date, License 1, License 2, License 3
2/4/13,3,4,5
2/5/13,5,4,3
!


You would set this data the same way they have in the example:
Code:

$myheaders = [ 'Date', 'License_1', 'License 2', 'License 3'];
$mydata = [
    ['2/4/13', '2/5/13'],
    ['3', '5'],
    ['4', '4'],
    ['5', '3']
];

_________________
-= the D@7@k|n& =-
Back to top
View user's profile Send private message
brent_weaver
Guru
Guru


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

PostPosted: Fri Mar 08, 2013 11:42 am    Post subject: Reply with quote

This is EXACTLY what I am looking for, thank you so much. One more question is how do I take the csv file and store it in arrays like that? So how do I read in each line and store the data. I know that line 1 will be the headings, and 2+ will be data. I have no issue with reading in the line, it is how do I store it using this method.

Someone asked me why I am am doing this, it is because I need to process hundreds of files each monday, and I will not do this manually.

Again thank you all SOO MUCH for the reposes, Gentoo forums has some GREAT people helping!
_________________
Brent Weaver
Back to top
View user's profile Send private message
massimo
Veteran
Veteran


Joined: 22 Jun 2003
Posts: 1151
Location: Austria

PostPosted: Fri Mar 08, 2013 2:08 pm    Post subject: Reply with quote

Try [1]


[1] http://szabgab.com/talks/fundamentals_of_perl/process-csv-file.html
_________________
Hello 911? How are you?
Back to top
View user's profile Send private message
dataking
Apprentice
Apprentice


Joined: 20 Apr 2005
Posts: 196

PostPosted: Fri Mar 08, 2013 4:13 pm    Post subject: Reply with quote

I'm not too familiar with Text::CSV_XS, but if my assumptions are correct, the example given by massimo points out one important thing.

The secondary arrays in the (referenced) array of arrays ($data) are columns (not rows).

So if you were to parse this line-by-line, based on your example here:
Code:

Date, License 1, License 2, License 3
2/4/13,3,4,5
2/5/13,5,4,3
...

... you would have to do something like:
Code:

my $i = 0;
open IN, $my_csv_file or die "Couldn't open input file for reading: $! \n";
while (my $line = <IN>) {
    chomp $line;           # remove ending whitespace
    if ($i == 0) {
        @myheaders = split(/\,/, $line);
        $i++;
    } else {
        my @fields = split(/\,/, $line);
        push @dates, $fields[0];
        push @licenses_1, $fields[1];
        push @licenses_2, $fields[2];
        push @licenses_3, $fields[3];
    }
}
close IN;


Then to populate the spreadsheet, you would do something like:
Code:

$headings = \@myheaders;
$data = [
    \@dates,
    \@licenses_1,
    \@licenses_2,
    \@licenses_3
];


If Text::CSV_XS parses data into columns (as the example implies), then that is definitely the way to go, because I'm betting it can also tell if/when there is a comma (,) in a field, rather than being used as a delimiter. (And that is a very handy feature, if it exists.)

It's also possible that you may not need to dereference the arrays when populating the spreadsheet data. If that's the case, the code would look like this:
Code:

$headings = @myheaders;
$data = [
    @dates,
    @licenses_1,
    @licenses_2,
    @licenses_3
];


It's been a while since I've used Spreadsheet::WriteExcel, though I used to use it extensively. $headings may need a derferenced array (\@myheaders) while $data may not. You'll just have to play with it.

HTH
_________________
-= the D@7@k|n& =-
Back to top
View user's profile Send private message
brent_weaver
Guru
Guru


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

PostPosted: Wed Mar 13, 2013 8:26 pm    Post subject: Reply with quote

Again thank you VERY much for the info. How do I deal with a variable line csv file?
_________________
Brent Weaver
Back to top
View user's profile Send private message
dataking
Apprentice
Apprentice


Joined: 20 Apr 2005
Posts: 196

PostPosted: Thu Mar 14, 2013 2:29 am    Post subject: Reply with quote

brent_weaver wrote:
Again thank you VERY much for the info. How do I deal with a variable line csv file?

Need more information.

  1. Are you using Text::CSV_XS?
  2. What do you mean by "variable line csv file"? Is it a variable number of rows or a variable number of columns?
  3. Are you encountering a specific problem that may better be solved directly?


If a) I'm not really sure, simply because I'm not familiar with that module. If b) then a simple while loop should suffice (as demonstrated above). If you're processing multiple CSV files, the number of lines should be invisible to the while loop processing the file(s), as long as the arrays (@dates, @licenses_1, @licenses_2, @licenses_3) are global, or at least within scope of the loop handling the list of files and the writing of the spreadsheet. If you mean b) and you have a variable number of columns, you're kinda screwed. Now you get into regex matching data to figure out which column is which, etc., which is a total PITA (been there, done that).

If c), I would need more details about the specific problem to assist.
_________________
-= the D@7@k|n& =-
Back to top
View user's profile Send private message
sundialsvc4
Guru
Guru


Joined: 10 Nov 2005
Posts: 436

PostPosted: Thu Mar 14, 2013 3:24 am    Post subject: Reply with quote

Suggest that you transport this question to http://www.perlmonks.org. (See you there.)
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