CSV - Trouble Parsing out last column of data from a CSV file 1

[Sqeezebox]

Hello there.

I have a CSV file that has some data including a timestamp (column 6). I am trying to parse out the last field of data from each row in the CSV file and then use each field separately to compare with one another. Basically I am trying to see if the last two fields are 300 seconds apart. If I could only parse the data out correctly, I could compare the two values. PLEASE HELP, I am a Perl Newbie!! The CSV file looks like:

TIME,SERVERNAME,COUNTERNAME,CURRENTVALUE,THRESHOLDBROKEN,TIMEINSECONDS
Mon Apr  9 11:31:11 2007,MULTI1,C:,22.7,20%,1176143471
Mon Apr  9 11:36:12 2007,MULTI1,C:,22.7,20%,1176143772

I have tried Text::CSV, Text::CSV_XS etc but I just can't figure out how to work them. I seem to be getting Array Refs back rather than useful data. I think I came closest to figuring it out when I tried this code:

#!/usr/local/bin/perl
#
# Log File Base Directory
$logdir = "C:\\Inetpub\\wwwroot\\MRTG\\Servers\\[servernamehere]\\thresh\\";
$logfile = "server.storage1.txt";
$filename = $logdir . $logfile;
use Text::CSV::Simple;

        # Only want certain fields?
        my $parser = Text::CSV::Simple->new;
        $parser->want_fields(5);
        my @data = $parser->read_file($filename);
        
        #should be the header field (TIMEINSECONDS);
        print $data[0];
        #should be the first row, 5th column data field;
        print $data[1];
        #should be the fifth row, 5th column data field;
        print $data[2];

However this code only prints the following:

ARRAY(0x1a0a414)ARRAY(0x1a0a408)ARRAY(0x1a0a3fc)

I believe these are array refs but I have no idea on how to extract the data I want through them. Thanks so much!

 

[WinblowsME]

#!/usr/local/bin/perl
#
# Log File Base Directory

$logdir   = "C:\\Inetpub\\wwwroot\\MRTG\\Servers\\[servernamehere]\\thresh\\";
$logfile  = "server.storage1.txt";
$filename = $logdir . $logfile;

our %seconds = ();

open ( IN, "< $filename" );
   my $header = <IN>;

   while ( <IN> ) 
   {
      chomp ( $_ );
   
      my ( $time, $servername, $countername, $currentvalue, $thresholdbroken, $timeinseconds ) = split ( /,/, $_ );

      $seconds{$time}{SERVERNAME}      = $servername;
      $seconds{$time}{COUNTERNAME}     = $countername;
      $seconds{$time}{CURRENTVALUE}    = $currentvalue;
      $seconds{$time}{THRESHOLDBROKEN} = $thresholdbroken;
      $seconds{$time}{TIMEINSECONDS}   = $timeinseconds;
   }
close ( IN );

foreach my $t ( sort keys %seconds )
{
   #print "$seconds{$t}{SERVERNAME}~";
   #print "$seconds{$t}{COUNTERNAME}~";
   #print "$seconds{$t}{CURRENTVALUE}~";
   #print "$seconds{$t}{THRESHOLDBROKEN}~";
   print "$seconds{$t}{TIMEINSECONDS}\n";
}

 

[travs69]

what I think you get out of Text::CSV::Simple is that a lot of files have embedded commas that are not really field seperators like 1,2,"this,is,one,field",4,5

 

[Sqeezebox]

Woah,

WinBlowsMe, What exactly did you do there. The script seems to work! I don't understand what you did though, care to enlighten me?

THANKS!

 

[KevinADC]

pretty much rght out of the Text:CSV module documentaton:

[url=http://perldoc.perl.org/functions/use.html][black][b]use[/b][/black][/url] [green]Text::CSV[/green][red];[/red]
[url=http://perldoc.perl.org/functions/my.html][black][b]my[/b][/black][/url] [blue]$csv[/blue] = Text::CSV->[maroon]new[/maroon][red];[/red]

[olive][b]while[/b][/olive][red]([/red]<DATA>[red])[/red][red]{[/red]
   [olive][b]if[/b][/olive] [red]([/red][blue]$csv[/blue]->[maroon]parse[/maroon][red]([/red][blue]$_[/blue][red])[/red][red])[/red] [red]{[/red]
    [black][b]my[/b][/black] [blue]@field[/blue] = [blue]$csv[/blue]->[maroon]fields[/maroon][red];[/red]
    [black][b]my[/b][/black] [blue]$count[/blue] = [fuchsia]0[/fuchsia][red];[/red]
    [olive][b]for[/b][/olive] [blue]$column[/blue] [red]([/red][blue]@field[/blue][red])[/red] [red]{[/red]
      [url=http://perldoc.perl.org/functions/print.html][black][b]print[/b][/black][/url] ++[blue]$count[/blue], [red]"[/red][purple] => [/purple][red]"[/red], [blue]$column[/blue], [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
    [red]}[/red]
    [black][b]print[/b][/black] [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
  [red]}[/red] [olive][b]else[/b][/olive] [red]{[/red]
    [black][b]my[/b][/black] [blue]$err[/blue] = [blue]$csv[/blue]->[maroon]error_input[/maroon][red];[/red]
    [black][b]print[/b][/black] [red]"[/red][purple]parse() failed on argument: [/purple][red]"[/red], [blue]$err[/blue], [red]"[/red][purple][purple][b]\n[/b][/purple][/purple][red]"[/red][red];[/red]
  [red]}[/red]
[red]}[/red]  
[teal]__DATA__[/teal]
[teal]TIME,SERVERNAME,COUNTERNAME,CURRENTVALUE,THRESHOLDBROKEN,TIMEINSECONDS[/teal]
[teal]Mon Apr  9 11:31:11 2007,MULTI1,C:,22.7,20%,1176143471[/teal]
[teal]Mon Apr  9 11:36:12 2007,MULTI1,C:,22.7,20%,1176143772[/teal]

[tt]------------------------------------------------------------
Other Modules used :
[ul]
[li]Text::CSV[/li]
[/ul]
[/tt]

------------------------------------------
- Kevin, perl coder unexceptional!

 

[Sqeezebox]

Ahhh see I didn't understand the while statement there. All you have to do is say while (data file) and then perform the actions within the while loop. I read the documentation but couldn't figure out where to specify what file to act upon with the Text::CSV module. I am new to this, and many things are unclear to me.. Thanks

 

[WinblowsME]

our %seconds = ();           # Declare a hash

open ( IN, "< $filename" );  # Open file for input
   my $header = <IN>;        # Shift off header line

   while ( <IN> )            # Read one line at a time
   {
      chomp ( $_ );          # Remove trailing newline from current line
   
      my ( $time, $servername, $countername, $currentvalue, $thresholdbroken, $timeinseconds ) = split ( /,/, $_ );
                             # Split commas on current line and store the field values 
                             # into the variables on the lefthand side
                             # Like Trav mentioned, split will not handle embedded commas properly unless 
                             # you use a complicated regular expression
 
      # Store values into a hash

      $seconds{$time}{SERVERNAME}      = $servername;
      $seconds{$time}{COUNTERNAME}     = $countername;
      $seconds{$time}{CURRENTVALUE}    = $currentvalue;
      $seconds{$time}{THRESHOLDBROKEN} = $thresholdbroken;
      $seconds{$time}{TIMEINSECONDS}   = $timeinseconds;
   }
close ( IN );

# Loop through all the stored times in the hash and print out the contents

foreach my $t ( sort keys %seconds )
{
   #print "$seconds{$t}{SERVERNAME}~";
   #print "$seconds{$t}{COUNTERNAME}~";
   #print "$seconds{$t}{CURRENTVALUE}~";
   #print "$seconds{$t}{THRESHOLDBROKEN}~";
   print "$seconds{$t}{TIMEINSECONDS}\n";
} 

# Another way to print out TIMEINSECONDS

print "$seconds{'Mon Apr  9 11:31:11 2007'}{TIMEINSECONDS}\n";
print "$seconds{'Mon Apr  9 11:36:12 2007'}{TIMEINSECONDS}\n";

 

[dmazzini]

Generic routine form perl cookbook:

Cheers

open (CSV, "myfile.csv");

   while ($file = <CHECKBOOK>) {
          @fields = parse_csv($file);
	  print "$fields[0],$fields[1],$fields[2]......\n";             	
   }

close CSV;   

sub parse_csv {
    my $text = shift;      # record containing comma-separated values
    my @new  = ();
    push(@new, $+) while $text =~ m{
        "([^\"\\]*(?:\\.[^\"\\]*)*)",?
           |  ([^,]+),?
           | ,
       }gx;
       push(@new, undef) if substr($text, -1,1) eq ',';
       return @new;      # list of values that were comma-separated
}

dmazzini
GSM System and Telecomm Consultant