George_Smiley has asked for the wisdom of the Perl Monks concerning the following question:

Where do I find info on how to do the following: using a CSV file of say 5000 last names with addresses, City and States. Then create a report that totals how many people live in each City, and how many people live in each state

Replies are listed 'Best First'.
Re: Creating Reports
by diotalevi (Canon) on Jul 26, 2004 at 20:08 UTC

    This is a sample implementation of summing by just state and then also by city and state.

    while ( ... )
{
    my $last_name = ...;
    my $address = ...;
    my $city = ...;
    my $state = ...;

    # Aggregate by state
    $SumStates{ $state }++;

    # Aggregate by city
    $SumCityStates{ $state }{ $city }++;
}

# Print sums
for my $state ( keys %SumStates )
{
    print "$state: $SumStates{ $state }\n";

    for my $city ( keys %{ $SumCityStates{ $state } } )
    {
        print "    $city: $SumCityStates{ $state }{ $city }\n";
    }
}

    [download]
[reply]
[d/l]
Re: Creating Reports
by dragonchild (Archbishop) on Jul 26, 2004 at 19:47 UTC
    Use DBD::CSV to allow you to use the file as an SQL database. Then, use SQL to find the information you're looking for.

    ------
    We are the carpenters and bricklayers of the Information Age.

    Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

    I shouldn't have to say this, but any code, unless otherwise stated, is untested

[reply]
Re: Creating Reports
by sschneid (Deacon) on Jul 26, 2004 at 19:50 UTC
    Do you have anything you've written in an attempt to do this yet? Sounds a bit like homework, so here are some pointers to get you started:

    First: open and close will allow you to read files.

    split will allow you to seperate fields per line from a CSV file.

    -s.
[reply]
[d/l]
[select]
      Never EVER suggest using split to parse a CSV file. That is an extremely reckless and stupid thing to do. Text::CSV, Text::CSV_XS, Text::xSV, and DBD::CSV and all extremely good solutions. Most are even PurePerl, so loading them isn't an issue on any platform.

      ------
      We are the carpenters and bricklayers of the Information Age.

      Then there are Damian modules.... *sigh* ... that's not about being less-lazy -- that's about being on some really good drugs -- you know, there is no spoon. - flyingmoose

      I shouldn't have to say this, but any code, unless otherwise stated, is untested

[reply]

        Never EVER suggest using split to parse a CSV file. That is an extremely reckless and stupid thing to do.

        I have to say that I think that is somewhat overstating the case. I can't see any problem whatsoever with processing character separated data with split when the data is from a known source and is consistent in its format - say: 

        FOO,1,2
BAR,2,3
BARG,29,38

        [download]
        after all we do have split for a reason. On the other hand I think it would be true that if one were to try and process the CSV output of, say, MS Excel with split one is likely to be disappointed with the results, but I wouldn't go so far as you have.

        /J\

[reply]
[d/l]
[select]
        Just checked out the page on DBD::CSV and that looks like a darn good place to start, thank you
[reply]
        Text::CSV, Text::CSV_XS, Text::xSV, and DBD::CSV and all extremely good solutions

        So is Text::ParseWords. And that's a standard Perl module.

        --
        <http://www.dave.org.uk>

        "The first rule of Perl club is you do not talk about Perl club."
        -- Chip Salzenberg

[reply]
      nope not homework, I know how to manipulate the files just fine, I just don't know how to create totals on fields that are not predetermined.
[reply]

Back to Seekers of Perl Wisdom