Reading CSV or TXT file in PERL

[premkiran]

Hi all,

I'm new in PERL, and trying to set up code that will allow me to create a new Table (Table 2) from Table 1:

Table 1
No. Time    Person
1   9:42 AM  A
2   9:42 AM  B
3   9:42 AM  A
4   9:42 AM  C
5   9:42 AM  B
6   9:42 AM  B
7   9:42 AM  C
8   9:42 AM  A
9   9:42 AM  C

and Table 2. may look like this:

Time    Person  Total count
9:42 AM  A         3
9:42 AM  B         3
9:42 AM  C         3

Thanks in advance!

[2teez]

Hi premkiran,

Welcome to learning, Perl. It is one of the best Computer languages you can learn. What you want is quite easily done in Perl.

To read a text file you will use open function to open the file and readin the file using, the readline command.

Please check the documentation of perl for these commands like so:

perldoc -f open and perldoc -f readline from your Command-Line-Interface.

To read a CSV file, though split could be used on CSV files, there are module from CPAN namely: Text::CSV and Text::CSV_XS which does a better job and so quite easily.

Example of Solution to your question:

use warnings;
use strict;

my %hash;
while (<DATA>) {
    chomp;
    next unless /^\d+?/;
    $hash{ ( split /\s+/, $_, 2 )[1] }++;
}

print join( " " => $_, $hash{$_} ), $/ for sort keys %hash;

__DATA__
Table 1
No. Time    Person
1   9:42 AM  A
2   9:42 AM  B
3   9:42 AM  A
4   9:42 AM  C
5   9:42 AM  B
6   9:42 AM  B
7   9:42 AM  C
8   9:42 AM  A
9   9:42 AM  C

I did show how to use open here but indcate the usage of readline, like <...>.

Output for the following is this:

9:42 AM  A 3
9:42 AM  B 3
9:42 AM  C 3

[premkiran]

@2teez,

Thanks for your guidlines. I am trying to download the module text::csv from CPAN.

[2teez]

You can use cpan from your system to download and install if you are using a Linix or Unix system OS.

Something like:
cpan
gets you into the cpan shell from your CLI, and then you see something like
cpan[1]>
from that prompt you can then input
install Text::CSV
i.e
cpan[1]> install Text::CSV

But if you are on window OS, and you are using ActivePerl, I think you will have to use ppm instead of cpan from the CLI.

Check this links:
1. how-install-cpan-modules

2. how-install-cpan-modules

3. how-install-cpan-modules

[premkiran]

@2teez,

Thank you, I have downloaded the text::csv module. it works but is it ok to use %hash function for the csv data for reading it and writing to another csv file?
Thanks.

[2teez]

Hi premkiran,

Thank you, I have downloaded the text::csv module.

Nice one..

it works but is it ok to use %hash function for the csv data for reading it and writing to another csv file?

I don't understand what you mean by %hash function However, you can read data from CSV files into %hash variable and still output your values.

Take the module for a spin and see how it works.

:)

[premkiran]

Thanks for your reply. I do not understand how to read csv file of my data and write the result in csv. I would be thankful if you write that code for me in addition to the code you have written. Thanks

[2teez]

Since you have Text::CSV installed in youru system all you need do is check the documentation like so:
perldoc Text::CSV and you will have example to follow.
However, if you have a particular problem, they try out something, atleast so that you will see how it works and if having problems with some areas then ask for help.

[premkiran]

I have done following code, because I tried csv read and write but wasn't sucess. Now I want to read csv instead of excel and write the result in csv too. your help will be much appreciated. Thanks

#!/usr/bin/perl 
#!perl -w


use Spreadsheet::ParseExcel; 
#my %hash;
my $total=0;
my @array={};

my $book=new Spreadsheet::ParseExcel::Workbook->Parse("d:/myfile.xls");

print"$book->{File}\n"; 
print"$book->{SheetCount}\n"; 

        print"------ SHEET: $sheet->{Name}\n"; 

        foreach my $row($sheet->{MinRow}..$sheet->{MaxRow}) 
        {

            foreach my $col($sheet->{MinCol}..$sheet->{MaxCol})

            {

              my $cell=$sheet->{Cells}[$row][$col] or next;

              if($col==4){

                  $array[$total]= $cell->Value;
                  $total=$total+1;
                  #print"($row,$col)=>",$cell->Value,"\n";
              }



            }

        }

}

print "total=$#array\n";

foreach $i (@array){
    $hash{"$i"}++;
}

$total2=0;
$total3=0;
my @array2={};
my @array3={};

while ( ($key, $value)=each %hash ) {
      print "$key => $value\n";
      $array2[$total2]=$key;


      $array3[$total2]=$value;
      $total2=$total2+1;
}

open FILE, ">>d:\\output.txt";
print FILE "Time\t";
print FILE "person_name\t";
print FILE "count_total\t";
print FILE "\n";

foreach $i (0..$total2-1){
    print FILE "09:42 PM to 12:30 AM\t";
    print FILE "$array2[$i]\t";
    print FILE "$array3[$i]\t";
    print FILE "\n";# 
}
system(pause);

[2teez]

Hi,

First off, you didn't show the data you are processing and the desired output. So there is no way to check the code you gave in respect to what you wanted done.

Secondly, you must use both use warnings and use strict in your perl scripts.

I may walk through your code, but without the data you are working with it might be difficult to tell. So include a sample of data you are working with and desired out.

[2teez]

On a second look at your code, you are parsing an excel format data not a CSV data. I know it is possible to open a csv file with excel.
So, if your original data is csv format don't open it with excel hoping to parse it.Just use Text::CSV module to parse your file.

If the formal data sample you gave is what you are to work with I don't think that is a csv formatted data sample.

Check this Example of csv format file

[premkiran]

@2teez,

I was trying to use text:csv, But it was not success. I convert the the following csv file into excel and then write that code to output in a text file.

1   11/12/2014 9:46 A
2   11/12/2014 9:47 B
3   11/12/2014 9:48 A
4   11/12/2014 9:49 A
5   11/12/2014 9:50 A
6   11/12/2014 9:51 A
7   11/12/2014 9:52 B
8   11/12/2014 9:53 B
9   11/12/2014 9:54 B
10  11/12/2014 9:55 B
11  11/12/2014 9:56 B
12  11/12/2014 9:57 C
13  11/12/2014 9:58 C
14  11/12/2014 9:59 C
15  11/12/2014 10:00    A
16  11/12/2014 10:01    B
17  11/12/2014 10:02    C
18  11/12/2014 10:03    C

and the output look like this:

Time    Person  Total_count
11/12/2014  9:46:18 AM to 11/12/2014  10:03:18 AM   A   6
11/12/2014  9:46:18 AM to 11/12/2014  10:03:18 AM   B   7
11/12/2014  9:46:18 AM to 11/12/2014  10:03:18 AM   C   5

Now I need to change the excel parse to csv and also the output in csv instead of text.

Thanks

[2teez]

Hi,
did you ckeck the example of what a csv files is like I gave in my last post? I would have expected that your data would have some , i.e comma in it.
So for me, the data you are presenting is a plain text file format, though it might be save as a csv file.
If this is how your data is, then you can just use open and do like I first showned.

However, if you have a csv formatted file, you can do something like this for example:

use warnings;
use strict;

use Text::CSV;
use Data::Dumper;

my %hash_val;

my $csv = Text::CSV->new( { binary => 1 } )
  or die Text::CSV->error_diag();

while ( my $row = $csv->getline(*DATA) ) {
    next unless ( $row->[0] =~ m/^\d/ );

    # get the value according to year
    push @{ $hash_val{ $row->[0] } } => [ @{$row}[ 1 .. $#$row ] ];
    print join( " " => @{$row} ), $/;    # print out
}

{
    $Data::Dumper::Sortkeys = 1;  # for sorting hash keys
    print Dumper \%hash_val;
}

__DATA__
Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.00
1999,Chevy,"Venture ""Extended Edition""","",4900.00
1999,Chevy,"Venture ""Extended Edition, Very Large""",,5000.00
1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.00

The Text::CSV documentation has a lot more to tell.

[premkiran]

Thanks for your help, I have done it. Now I have another problem related to the time and date column in the csv file. For example in the previous post data, I want to result the person and the person count with respect to the time and date. Suppose from 11/12/2014 9:46 AM to 11/12/2014 10:03 AM, how many A or B or C occur.
The result looks like:

start_time_end_time                           Person   Total_count
11/12/2014  9:46:18 AM to 11/12/2014  10:03:18 AM   A   6
11/12/2014  9:46:18 AM to 11/12/2014  10:03:18 AM   B   7

Is there anyway to result above from the previous post data, considering the time and date. Thanks

[2teez]

Is there anyway to result above from the previous post data, considering the time and date. Thanks

If all you wanted is the number of times A, B and C occur, you can use the first posted code, it works without any change at all, even with the new data. Try it.