Perl Data

Posted on by Ted

  Perl is an established language with lots of code examples provided on the Web.   It is a powerful language (meaning you can do a lot with very little code).   Perl has a rich library set, especially in regards to text processing.  The language can take some getting use to because of its heavy use of control character.  For example: ‘.’ means concatenate strings, and the leading character (@, $, & and %) indicates the data type being accessed (eg. $foobar is a scalar while @foobar is an array). Below is an example of a database containing two tables, customers and purchase order.  The Customer table has customerid (the unique identifier for the customer), name (The customer’s name) and age (the customer’s age).  The PurchaseOrder table has POID (the unique id for the purchase order), customerid (which refers back to the customer who made the purchase) and Purchase (what the purchase was).

Example:
Customer
CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
PurchaseOrder
POID CustomerID Purchase
1 3 Fiction
2 1 Biography
3 1 Fiction
4 2 Biography
5 3 Fiction
6 4 Fiction
 
     
SELECT
Create the file select.pl with the following code:

#!/usr/bin/perl -w

my @file_lines;

# Loop through data and print lines

while ( my $line = <> ) {

# Print Line

print $line . \n ;

# Populate next line

push @file_lines, $line; }

Then run the code:
perl select.pl customer.txt

CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
ORDER BY
Create the file orderby.pl with the following code:

#!/usr/bin/perl -w

# Open the file

open(DATA, $ARGV[0]); my $line;
my $iCnt;

# Initialize variables

$iCnt =0;
$iLoop =0;

# Loop through data and populate a array for later use.

while($line = )
{

# Copy data to an arry skipping header (first line with field names)

if ($iCnt >0)
{
$lines[$iCnt-1] = $line;
}

$iCnt ++;
}

# Close file object

close DATA;

# Sort using on custom sort based on second field

@sorted = sort{@PANa = split( \, , $a); @PANb = split( \, , $b); return $PANa[2] cmp $PANb[2] } (@lines);

# Loop through data and print lines

while($iLoop < $iCnt-1)
{
print $sorted[$iLoop];
$iLoop ++;
}

Then run the code:
perl orderby.pl customer.txt

CustomerID Name Age
5 Susan 18
1 Joe 23
3 Lin 34
2 Mika 45
4 Sara 56
WHERE
Create the file select_by_id.pl with the following code:

#!/usr/bin/perl -w my @file_lines;

# Loop through passed file

while ( my $line = <> ) {

# Split line using a comma

@PAN =split( \, , $line);
$out = $PAN[0];

# If ID is passed ID then print

if ($out eq 1 )
{
print $line . \n ;
}

# Populate next line

push @file_lines, $line; }

Then run the code:
perl select_by_id.pl customer.txt 1

CustomerID Name Age
5 Susan 18
INNER JOIN
Create the file innerjoin.pl with the following code:

#!/usr/bin/perl -w

# Open both files

open(DATA1, $ARGV[0]);
open(DATA2, $ARGV[1]); my $lineCust;
my $lineOrder;
my $iCnt;
my $iLoop;

# Initialize variables

$iCnt =0;

# Loop through data and populate a array for later use.

while($lineOrder = )
{
$orders[$iCnt] = $lineOrder;
$iCnt ++;
}

# Close file object

close DATA2;
while($lineCust = )
{

# Split line using a comma

@PAN1 =split( \, , $lineCust);
$out1 = $PAN1[0];

# Remove newline character

chop($PAN1[2]);
chop($PAN1[2]);

$iLoop =0;

while($iLoop < $iCnt)
{

# Split line using a comma

@PAN2 =split( \, , $orders[$iLoop]);
$out2 = $PAN2[1];

#If there is a match print

if ($out1 eq $out2)
{
print $PAN1[0] . , . $PAN1[1] . , . $PAN1[2] . , . $orders[$iLoop];
}

$iLoop ++;
}
}

# Close file object

close DATA1;

Then run the code:
perl innerjoin.py customer.txt orders.txt

CustomerID Name Age POID Purchase
1 Joe 23 2 Biography
1 Joe 23 3 Fiction
2 Mika 45 4 Biography
3 Lin 34 1 Fiction
3 Lin 34 5 Fiction
4 Sara 56 6 Fiction
LEFT OUTER JOIN
Create the file leftouterjoin with the following code:

#!/usr/bin/perl -w

# Open both files

open(DATA1, $ARGV[0]);
open(DATA2, $ARGV[1]); my $lineCust;
my $lineOrder;
my $iCnt;
my $iLoop;
my $iFound;

# Initialize variables

$iCnt =0;

# Loop through data and populate a array for later use.

while($lineOrder = )
{
$orders[$iCnt] = $lineOrder;
$iCnt ++;
}

# Close file object

close DATA2; while($lineCust = )
{

# Split line using a comma

@PAN1 =split( \, , $lineCust);
$out1 = $PAN1[0];

# Remove newline character

chop($PAN1[2]);
chop($PAN1[2]);

$iLoop =0;
$iFound =0;

while($iLoop < $iCnt)
{

# Split line using a comma

@PAN2 =split( \, , $orders[$iLoop]);
$out2 = $PAN2[1];

#If there is a match print

if ($out1 eq $out2)
{
print $PAN1[0] . , . $PAN1[1] . , . $PAN1[2] . , . $orders[$iLoop];
$iFound =1;
}

$iLoop ++;
}

#If there was no match print

if ($iFound ==0)
{
print $PAN1[0] . , . $PAN1[1] . , . $PAN1[2] . , . , . , . ,\n ;
}
}

# Close file object

close DATA1;

Then run the code:
perlleftouterjoin.pl customer.txt orders.txt

CustomerID Name Age POID Purchase
1 Joe 23 2 Biography
1 Joe 23 3 Fiction
2 Mika 45 4 Biography
3 Lin 34 1 Fiction
3 Lin 34 5 Fiction
4 Sara 56 6 Fiction
5 Susan 18 NULL NULL
GROUP BY
Create the file groupby.pl with the following code:

#!/usr/bin/perl -w

#Open the file

open(DATA1, $ARGV[0]); my $line;
my $iCnt;
my $iLoop;

# Initialize variables

$iCnt =1;
$iloop =0;

# Loop through data

while($line = )
{

# Split line using a comma

@PAN1 =split( \, , $line);
$id = $PAN1[0];
$name = $PAN1[1];
$age = $PAN1[2];

# Remove newline character

chop($age);
chop($age);

if ($iloop <2 )
{
$prior_id = $id ;
$iCnt = 0;
}

if ($prior_id eq $id)
{
$iCnt ++;
}
else
{
print $prior_name . , . $prior_age . , . $iCnt . \n ;
$iCnt =1;
}

$iloop ++;
$prior_id = $id ;
$prior_name = $name ;
$prior_age = $age ; }

print $prior_name . , . $prior_age . , . $iCnt . \n ;

# Close file object

close DATA;

Then run the code:
perl groupby.pl customer.txt

Name Age Orders
Joe 23 1
Mika 45 2
Lin 34 2
Sara 56 1
UPDATE
Create the file update.pl with the following code:

#!/usr/bin/perl -w

# Open the file

open(DATA, $ARGV[0]);

# Loop through data

while()
{

# Split line using a comma

@PAN =split( \, , $_);
$out = $PAN[0];

# Pint if ID is not equal to passed ID otherwise update.

if ($out eq $ARGV[1])
{
print $PAN[0]. , . $PAN[1] . , . $ARGV[2] . \n ;
}
else
{
print $_ ;
}

# Close file object

close DATA;
}

  Then run the code:
perl groupby.pl customer.txt 1 23

Customer
CustomerID Name Age
1 Joe 26
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
INSERT
Create the file insert.pl with the following code:

#!/usr/bin/perl -w

#Open the file

open(DATA, $ARGV[0]);

# Loop through data and print lines

while()
{
print $_ ;
}

# Add new line from passed arguments

print $ARGV[1]. , . $ARGV[2] . , . $ARGV[3] . \n ;

# Close file object

close DATA;

Then run the code:
perl insert.pl customer.txt 6 Joe 34

Customer
CustomerID Name Age
1 Joe 23
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18
6 Terry 50
DELETE
Create the file delete.pl with the following code:

#!/usr/bin/perl -w

#Open the file

open(DATA, $ARGV[0]);

# Loop through data

while()
{

# Split line using a comma

@PAN =split( \, , $_);
$out = $PAN[0];

# If ID is not passed ID then print

if ($out ne $ARGV[1])
{
print $_ ;
} }

# Close file object

close DATA;

Then run the code:
perl delete.pl customer.txt 1

Customer
CustomerID Name Age
2 Mika 45
3 Lin 34
4 Sara 56
5 Susan 18

Leave a Reply

Your email address will not be published. Required fields are marked *