<html><head><meta name="color-scheme" content="light dark"></head><body><pre style="word-wrap: break-word; white-space: pre-wrap;">#!/usr/bin/perl -w

=head1 NAME

Normalizer - Converts unnormalized data, such as in files downloaded through Thomson SDC Platinum, to 3rd normal form.

(c) Ed Egan, 2007. All rights reserved. For now.

=head1 SYNOPSIS

perl Normalizer.pl -file=&lt;file&gt; [-headerdiscards=&lt;int&gt;] [-footerdiscards=&lt;int&gt;] [-columnnames=&lt;1|0&gt;] [-safenames=&lt;1|0&gt;] [-outfile=&lt;file&gt;] [h]

=head1 OPTIONS

    -file=&lt;file&gt;:           Name of file to Normalize
    -headerdiscards=&lt;int&gt;:  The number of header lines to discard (Default: 0)
    -footerdiscards=&lt;int&gt;:  The number of footer lines to discard (Default: 0)
    -columnnames=&lt;1|0&gt;:     Whether or not the file contains column names on the first line, after discards (Default: 1)
    -safenames=&lt;1|0&gt;:       Determines whether column names will be stripped of non-alphanumeric characters (Default: 1)
    -outfile=&lt;file&gt;:        The name of the outfile sequence (Default: file.txt - expands to file1.txt, file2.txt,etc)
    -h:                     Display help

=head1 Usage and Features

Normalizer.pl takes a tab-delimited, string-quoted textfile, such as might be made by
copying and pasting an excel sheet into a text file, and creates a series of files containing
the data in third normal form and suitable for import into a database.

Several major data providers, notably Thomson Financial's SDC service, use 'in cell' carriage returns
to seperate multiple items of data. This makes processing of the data very difficult (without some software to help!).
An example spreadsheet might look like this:

    --------------------------------------------------------
    |   col1   |   col2   |   col3   |   col4   |   col5   |
    --------------------------------------------------------
    |    A     |    A     |    A     |    A     |     A    |
    --------------------------------------------------------
    |    B     |    B     |    B     |    B     |     B    |
    |          |          |    B     |    B     |     B    |
    |          |          |          |    B     |     B    |
    --------------------------------------------------------        
    |          |    C     |    C     |    C     |     C    |
    |          |          |          |    C     |     C    |
    --------------------------------------------------------

Normalize.pl will prepare tables for import into the database, in 3rd normal form,
by doing the following:

=over 1

=item 1 Adding a unique key field (col0) to the 1st output file.

=item 2 Inserting all data (including null values) that has a 1:1 relationship with the key into the 1st output file.

=item 3 If, as in the case of col4 and col5 above, data has a many to one relationship with the key, it will be grouped into sets.

=item 4 Foreach of these sets (the example has two) a new output file, with the first key and it's own key, will be created.

=back 1

The set identification is fully automatic and dictated by the data.
In the example given, col1 and col2 would be assigned to the 1:1 file, while "col4 &amp; col5" and "col3" would be two seperate sets.

Note that if the software were given only the first line (A) it would only identify a 1:1 relationship.
Likewise if the software were given only the first (A) and the third (C) lines, it would identify two sets (col1, col2 &amp; col3; and col4 &amp; col5).
Thus, the larger the file, the more accurate the set identification.
However, a conceptual underidentification could be corrected manually after import into a database.

=head1 BUGS &amp; FEEDBACK

Well it seems to work fine for me...

Now, I'm not offering any support whatsoever, you understand, but if you find something that goes wrong,
or want an extra feature or two, and particularly if you aren't a perl programmer yourself,
then maybe I will help. Mail me and find out.

On the other hand, if you find a bug and fix it, or make some improvement to the code,
or just want to give me money, then please contact me right away.

Ed.

&lt;ed@edegan.com&gt;

=cut

#Include packages
use strict;
use warnings;
use Getopt::Long;
use Data::Dumper;
use Pod::Usage;

#Read command-line arguments
my $opt_show_help=0;
my $opt_headerline=0;
my $opt_footerline=0;
my $opt_safeheaders=1;
my $opt_header=1;
my $opt_outfilename="file.txt";
my ($opt_file);
GetOptions(
      "file=s"              =&gt; \$opt_file,
      "headerdiscards=i"    =&gt; \$opt_headerline,
      "footerdiscards=i"    =&gt; \$opt_footerline,
      "columnnames=i"       =&gt; \$opt_header,
      "safenames=i"	    =&gt; \$opt_safeheaders,
      "outfile=s"	    =&gt; \$opt_outfilename,
      "h"                   =&gt; \$opt_show_help
) || die pod2usage(2);
if ($opt_show_help  || !defined($opt_file)) {
   pod2usage(-exitstatus =&gt; 0, -verbose =&gt; 2);
}

#Print up the disply screen
print "##########################################################\n";
print "#                       Normalize.pl                     #\n";
print "#                           v0.01                        #\n";
print "#                     (c)Ed Egan, 2007                   #\n";
print "##########################################################\n\n";
print "Starting the program...\t\t\t\t[Done]\n";
print "Your run options are:\n";
print "\tFile to read from... \t\t\t[$opt_file]\n";
print "\tBasic write file name:... \t\t[$opt_outfilename]\n";
print "\tNo. of header lines discarded...\t[$opt_headerline]\n";
print "\tNo. of footer lines discarded...\t[$opt_footerline]\n";
print "\tFirst line contains Column Names...\t[".&amp;yesno($opt_header)."]\n";
print "\tUsing Safe Column Names... \t\t[".&amp;yesno($opt_safeheaders)."]\n";
print "Opening the input file to read...\t\t";
$|=1;

#Open the input file and dump the super-header
open(INPUT,"$opt_file") || die "\nThe input file was not found. Please check the file exists and retry.\n";
my $unprocessedfile=undef;
my $linecount=1;
while (&lt;INPUT&gt;) {
    if ($linecount &lt;= $opt_headerline) {$linecount++; next;}
    $unprocessedfile=$unprocessedfile.$_;
    $linecount++;
}
close (INPUT);
print "[Done]\n";

print "Determining true line breaks...\t\t\t";
#Find the true line breaks and put them back in
$unprocessedfile=~s/\n/### /g;
my @quotearray=split(/\"/,$unprocessedfile);
for (my $i=0;$i&lt;=$#quotearray;$i++) {
    if (($i/2) == int($i/2)) { #Note that even cells contain the true line breaks!
        $quotearray[$i]=~s/### /\n/g;
    }
}
print "[Done]\n";

print "Dumping Header/Footer &amp; naming columns...\t";
#Process the headers and dump the footer
my @processedfile=split("\n",join("",@quotearray));
undef my %column;
if ($opt_header) {
    my $headerline=shift(@processedfile);
    $headerline=~s/###/ /g;
    my @headerline=split("\t",$headerline);
    for (my $i=0;$i&lt;=$#headerline;$i++){
        if ($opt_safeheaders) {
            $headerline[$i]=~s/\W//g;
        }
        $headerline[$i]=~s/^\s*//;
        $headerline[$i]=~s/\s*$//;
        $column{$i+1}=$headerline[$i];
    }
}
else {
    my $firstline=$processedfile[0];
    my @firstline=split("\t",$firstline);
    for (my $i=0;$i&lt;=$#firstline;$i++){
        $column{$i+1}="Column.$i";
    }
}
my $footerlinecount=$opt_footerline;
while ($footerlinecount &gt; 0) {
    my $footline=pop(@processedfile);
    $footerlinecount--;
}
print "[Done]\n";

print "Loading the data for analysis...\t\t";
#Build the master hash and the lengths hash
my $Master={};
my $rowno=1;
foreach my $line (@processedfile) {
    my @cells=split("\t",$line);
    for(my $cellno=0;$cellno&lt;=$#cells;$cellno++){
        my @temp=split(/###/,$cells[$cellno]);
        if (!defined($temp[0])){@temp=(" ");}
        foreach (@temp) {
            s/^ //;
            s/ $//;
        }
        $Master-&gt;{$rowno}-&gt;{$cellno+1}=\@temp;
    }
    $rowno++;
}
my $Lengths={};
foreach my $row (sort (keys (%$Master))) {
    foreach my $cell (keys (%{$Master-&gt;{$row}})) {
        $Lengths-&gt;{$row}-&gt;{$cell}=scalar(@{$Master-&gt;{$row}-&gt;{$cell}});
    }
}
print "[Done]\n";

print "Determining the normalization sets...\t\t";
#Now determine the normalization sets!
my $sets={};
my $setsreport={};
my $proposedsetplan=undef;
foreach my $row (sort (keys (%$Lengths))) {
    if (!$proposedsetplan) {
        $proposedsetplan=1;
        foreach my $cell (keys (%{$Lengths-&gt;{$row}})) {
            my $length=$Lengths-&gt;{$row}-&gt;{$cell};
            $sets-&gt;{$length}-&gt;{$cell}=1;
        }
        my $noofsets=scalar(keys(%$sets));
        $setsreport-&gt;{$noofsets}=1;
    }
    else {
        #Set up the current set
        my $currentsets={};
        foreach my $cell (keys (%{$Lengths-&gt;{$row}})) {
            my $length=$Lengths-&gt;{$row}-&gt;{$cell};
            $currentsets-&gt;{$length}-&gt;{$cell}=1;
        }
        my $noofsets=scalar(keys(%$currentsets));
        if (defined($setsreport-&gt;{$noofsets})) {$setsreport-&gt;{$noofsets}++;}
        else {$setsreport-&gt;{$noofsets}=1;}       
        #Compare the current set to the proposed plan (the most sets wins!)
        if (scalar(keys(%$currentsets)) &gt; scalar(keys(%$sets))) {
            $sets=$currentsets;
        }
    }
}
print "[Done]\n";
print "\tSets per row\tFrequency\n";
foreach my $setsperrow (sort {$a &lt;=&gt; $b} (keys (%$setsreport))) {
	print "\t\t".$setsperrow."\t  ".$setsreport-&gt;{$setsperrow}."\n";
}
print "No. of sets used...\t\t\t\t[".scalar(keys(%$sets))."]\n";

print "Preparing the sets for output...\t\t";
#Check that there is a '1' length key, otherwise make one
if (!defined($sets-&gt;{1})) {
    $sets-&gt;{1}=undef;
}
#Make a renumbered sets hash
my $outputsets={};
my $outputindex=1;
foreach my $oldlengthkey (sort {$a &lt;=&gt; $b} (keys(%$sets))) {
    $outputsets-&gt;{$outputindex}=$sets-&gt;{$oldlengthkey};
    $outputindex++;
}
#Make a sets counter hash
my $outputsetscounter={};
foreach my $key (sort {$a &lt;=&gt; $b} (keys(%$outputsets))) {
    $outputsetscounter-&gt;{$key}=1;
}
print "[Done]\n";

print "Writing the output sets now...\t\t\t";
#Print the output files
foreach my $outputset (sort {$a &lt;=&gt; $b} (keys(%$outputsets))) {
    my ($filename,$extension)=split(/\./,$opt_outfilename);
    my $fullfilename=$filename."-".$outputset.".".$extension;
    #Prepare the header
    my $printheader="";
    $printheader=$printheader.$filename."-1";
    if ($outputset &gt; 1) {
        $printheader=$printheader."\t".$filename."-".$outputset;
    }
    foreach my $columnno (sort {$a &lt;=&gt; $b} (keys(%column))) {
        foreach my $outputcol (sort {$a &lt;=&gt;$b} (keys(%{$outputsets-&gt;{$outputset}}))) {
            if ($outputcol == $columnno) {
                $printheader=$printheader."\t".$column{$columnno};
            }
        }
    }
    open (OUTPUT,"&gt;$fullfilename") || die "I can't open the output file ($fullfilename) to write, so I died with this message. Sorry.";
    print OUTPUT $printheader."\n";
    #Now print the file contents
    foreach my $rowno (sort {$a&lt;=&gt;$b}(keys(%$Master))) {
        #Which set (length) are we dealing with here?
        my @setofcols=keys(%{$outputsets-&gt;{$outputset}});
        my $arraylength=scalar(@{$Master-&gt;{$rowno}-&gt;{$setofcols[0]}});
        for (my $i=0;$i&lt;$arraylength;$i++) {
            print OUTPUT $rowno;
            if ($outputset &gt; 1) {
                print OUTPUT "\t".$outputsetscounter-&gt;{$outputset};
                $outputsetscounter-&gt;{$outputset}++;
            }            
            foreach my $col (sort {$a &lt;=&gt;$b} (@setofcols)) {
                if (defined($Master-&gt;{$rowno}-&gt;{$col}[$i])) {
                    print OUTPUT "\t".$Master-&gt;{$rowno}-&gt;{$col}[$i];
                }
                else {
                    print OUTPUT "\t";
                }
            }
            print OUTPUT "\n";
        }
    }
    close (OUTPUT);
}
print "[Done]\n";

#Finish up!
print "\n";
print "##########################################################\n";
print "Thank you for normalizing your (SDC) data with Normalizer\n";
print "Please cite any Ed Egan corporate finance paper in your \n";
print "references or otherwise acknowledge this help to your work.\n";
print "(c)Ed Egan, 2007. Enjoy. (US dollars preferred.)\n";    
print "##########################################################\n";

sub yesno {
    my $input=shift(@_);
    if (!$input) {return "No";}
    else {return "Yes";}
}

</pre></body></html>