Hi,
I have a pipe delimited flat file database of students that I mail out certificates to.
I wrote a label preparation script which basically passes through the file and prepares a mailing label from each record with a reference for which certificate to enclose (also from the database). (Happy to share this if anyone wants it).
Thing is that I get a label for each certificate when I could put up to 4 certificates in a single mailing - at the moment I'm just not using three out of four labels.
I am therefore looking to add a subroutine that loads the database and appends up to four certificate references into each student's field (and remove the records that it appends), just before the labels are printed. There can be up to 3,000 records in the database on heavy days but this is unusual, there are normally more like 500.
The database is like this:
ID|Name|Address|CertRef|Date
345|Paul|13 Somwhere|CG6129|100707
387|Steve|15 Somwhere|CG6129|100707
345|Paul|13 Somwhere|CG6089|100707
After the routine I need a database (can be a new file) to look like this:
ID|Name|Address|CertRef|Date
345|Paul|13 Somwhere|CG6129 CG6089|100707
387|Steve|15 Somwhere|CG6129|100707
I am assuming that I need to slurp the contents of the database into an array but I would appreciate help on the code to look for matches, append and delete.
Please ask if you need any more info. Many thanks.
Paul
3 1889
You will want to use a hash, where the hash keys are the ID and the rest of the data can be stored in a hash, with the CertRef being an array of the values. See if this helps get you started on how to work with complex dta such as a hash of hashes where the values of the hash of hashes is mixed data types: perldoc Complex Data Tutorial
Observe the following script: -
# Constants
-
use Readonly;
-
Readonly my $ID => 0;
-
Readonly my $NAME => 1;
-
Readonly my $ADDR => 2;
-
Readonly my $CERT => 3;
-
Readonly my $DATE => 4;
-
-
Readonly my $MERGE_MAX => 4;
-
-
use strict;
-
-
# Load Data
-
my @records = ();
-
while (<DATA>) {
-
chomp;
-
push @records, [split '\|'];
-
}
-
my $header = shift @records;
-
-
# Sort Data
-
@records = sort {$a->[$ID] <=> $b->[$ID] || $a->[$CERT] cmp $b->[$CERT]} @records;
-
-
# Merge Data
-
my @merged = ();
-
foreach my $rec (@records) {
-
if (@merged && $merged[-1][$ID] eq $rec->[$ID] && @{$merged[-1][$CERT]} < $MERGE_MAX) {
-
push @{$merged[-1][$CERT]}, $rec->[$CERT];
-
-
} else {
-
$rec->[$CERT] = [ $rec->[$CERT] ]; # Turn cert field into an array
-
push @merged, $rec;
-
}
-
}
-
-
# Output Data
-
foreach my $rec (@merged) {
-
# Translate Cert field into a string.
-
$rec->[$CERT] = join(' ', @{$rec->[$CERT]});
-
-
print join('|', @$rec) . "\n";
-
}
-
-
__DATA__
-
ID|Name|Address|CertRef|Date
-
345|Paul|13 Somwhere|CG6129|100707
-
387|Steve|15 Somwhere|CG6129|100707
-
1|Foo|1 Foo St.|CG6089|100707
-
345|Paul|13 Somwhere|CG6089|100707
-
387|Steve|15 Somwhere|CG6089|100707
-
2|Bar|1 Bar St.|CG6089|100707
-
345|Paul|13 Somwhere|CG6001|100707
-
3|Baz|1 Baz St.|CG6089|100707
-
345|Paul|13 Somwhere|CG6002|100707
-
4|Bit|1 Bit St.|CG6089|100707
-
345|Paul|13 Somwhere|CG6003|100707
-
387|Steve|15 Somwhere|CG6003|100707
-
5|Bot|1 Baz St.|CG6089|100707
-
345|Paul|13 Somwhere|CG6004|100707
-
Outputs -
>perl scratch.pl
-
1|Foo|1 Foo St.|CG6089|100707
-
2|Bar|1 Bar St.|CG6089|100707
-
3|Baz|1 Baz St.|CG6089|100707
-
4|Bit|1 Bit St.|CG6089|100707
-
5|Bot|1 Baz St.|CG6089|100707
-
345|Paul|13 Somwhere|CG6001 CG6002 CG6003 CG6004|100707
-
345|Paul|13 Somwhere|CG6089 CG6129|100707
-
387|Steve|15 Somwhere|CG6003 CG6089 CG6129|100707
-
There are other ways to go about this. And I definitely suggest that you read up Kevin's tutorial link as it provides a lot of tools useful for processing data.
- Miller
Wow thanks, I'll give this a go and will report back.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: googlinggoogler |
last post by:
Hiya,
The title says it all really, but im a newbie to python sort of. I can
read in files and write files no probs.
But what I want to do is read in a couple of files and output them to
one...
|
by: amywolfie |
last post by:
I have a curious mission: I converted an Excel file to a relational
Access 2002 database, and now I have to export data back to IT as a
FLAT .csv file.
I know how to create many records from...
|
by: Ross A. Finlayson |
last post by:
Hi,
I'm scratching together an Access database. The development box is
Office 95, the deployment box Office 2003.
So anyways I am griping about forms and global variables. Say for
example...
|
by: Stuart Norris |
last post by:
Dear Readers,
I am developing an application that stores "messages" in array list
and writes new entries to a disk file at the end. This file is used
incase the user choses to restart the...
|
by: Ben |
last post by:
So, at my place of employment, we use a national standard to transmit
data between certain applications. This standard consists of a fixed
width, flat file 4500-some-odd chars wide that contain...
|
by: Ken Capriell |
last post by:
I know that subject probably did not adequately explain anything so here
goes...
I have an access file that needs to be transposed from its current format to
a new format so that I can then...
|
by: vunet.us |
last post by:
Hi,
I would like to use flat file data storage instead of database. Since I
am new to it, I am wondering:
What text file extension is a safe one to store my data online and how
cost- and...
|
by: lxyone |
last post by:
Using a flat file containing table names, fields, values whats the
best way of creating html pages?
I want control over the html pages ie
1. layout
2. what data to show
3. what controls to...
|
by: Tim |
last post by:
Hi Folks,
I'm used to a UNLOAD command that allows me to dump to a named flat
file the results of any SELECT statement. Hence one can build a single
SQL file which contains multiple SQL...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
| |