Hello Monks:
I have a simple coding question. Maybe I do not understand how the IO::File and Text::CSV objects behave. I am parsing a text file, at the top of the file there a couple of line that can be ignored. The line immediatly before the data contains column headings. I want to check the column headings. If the headings checkout start parsing the real data. My issue is that the code: while (defined($col = $csv->getline($fh) )) Starts over at the beginning of the file. Not what I expected, I want it to continue with the line after the column heading. My code is below.
Thanks for thinking about my issue.
kd
# Perl Program to read the Imagine Position files
# Based upon reading the file the IMAGINEPOSITIONS table
# will be updated in the database
# Declare the subroutine
use Win32::ODBC;
use Text::CSV_PP;
use Data::Dumper;
use IO::File;
sub trim($);
if (! @ARGV == 2 ) {
print "Invalid Number of Command Line Parameters \n" ;
print "Two Parameters Expected \n " ;
die;
}
print " Imagine Position File: " . $ARGV[0] . "\n";
print " Properties File: " . $ARGV[1] . ".\n";
open (ODBCProps, $ARGV[1]) || die("Could NOT open " . $ARGV[1] . ".\n
+");
@ODBCFileData = <ODBCProps> ;
print "size of ODBCFileData: " . @ODBCFileData . ".\n";
foreach $row (@ODBCFileData) {
chomp($row);
($propName, $propValue) = split /=/, $row;
if ( trim(uc($propName)) eq 'DSN' ) {
$DSN = $propValue;
}
elsif (trim(uc($propName)) eq 'SERVER') {
$Server = $propValue ;
}
elsif (trim(uc($propName)) eq 'DATABASE') {
$Database = $propValue ;
}
elsif (trim(uc($propName)) eq 'USERID') {
$userid = $propValue ;
}
elsif (trim(uc($propName)) eq 'PWORD') {
$pword = $propValue ;
}
} # foreach
print "Database Connection Properties \n";
print "\t DSN = " . $DSN . "\n" ;
print "\t Server = " . $Server . "\n" ;
print "\t Database = " . $Database . "\n" ;
print "\t User Id = " . $userid . "\n" ;
print "\t PWord = " . $pword . "\n" ;
close ODBCProps;
$connStr = " Driver={MySQL ODBC 3.51 Driver}; Server=" . $Server . "
+;Database=" . $Database . ";UID=". $userid . ";PWD=". $pword . ";"
+;
print $connStr . "\n" ;
$db = new Win32::ODBC( $connStr ) or die "couldn't ODBC Connection b
+ecause ", Win32::ODBC::Error(), "\n" ;
$SQLStr1 = " insert into imaginepositions ( lineno , acct , extSecC
+ode , IssuerCode , IssuerName , " .
" qty, SMdescription, USYM, Security, ISIN, SEDOL, CUSIP, type,
+ marketPrice , value ) VALUES ( ";
# ** ** **
$fh = new IO::File;
if ($fh->open("< $ARGV[0]")) {
#print <$fh>;
#** ** ** ** ** ** **
# find the beginning of the data
while(defined($row = <$fh>)) {
chomp($row);
print "current row = " . $. . " ";
print $row . "\n";
if ( $. >= 20 || $row =~ m/#/) {
last;
}
}
if ( $. >= 20 ) {
print " ** ** Error ** ** \n " ;
die("Did NOT find column headings for data in the file " . $ARGV
+[0] . ".\n");
}
else {
if ( !$row eq '#|Acct|Ext Sec Code|Issuer Code|Issuer Name|Quan
+tity|SM Description|Usym|Security|Isin|Sedol|Cusip|Type|Mkt|$ Value'
+) {
print " ** ** Error ** ** \n " ;
die("Column Headings do NOT match expected values in the file
+ " . $ARGV[0] . " .\n");
}
}
$csv = Text::CSV_PP->new(); # create a new CSV parser ob
+ject
$csv->sep_char('|');
while (defined($col = $csv->getline($fh) )) {
print "\n\n** ** ** ** ** ** ** ** ** ** ** \n ";
# print Dumper $columns;
$secDesc = $col->[6];
$secDesc =~ s/'/`/g;
$SQLValues = "'@{[trim($col->[0])]}', " ; #line no
$SQLValues .= "'@{[trim($col->[1])]}', " ; #acct
$SQLValues .= "'@{[trim($col->[2])]}', " ; #Ext Sec Code
$SQLValues .= "'@{[trim($col->[3])]}', " ; #Issuer Code
$SQLValues .= "'@{[trim($col->[4])]}', " ; #Issuer Name
$SQLValues .= " @{[trim($col->[5])]}, " ; #QTY
$SQLValues .= "'$secDesc', " ; #SM Descriptio
+n
$SQLValues .= "'@{[trim($col->[7])]}', " ; #USYM
$SQLValues .= "'@{[trim($col->[8])]}', " ; #Security
$SQLValues .= "'@{[trim($col->[9])]}', " ; #ISIN
$SQLValues .= "'@{[trim($col->[10])]}', " ; #SEDOL
+
$SQLValues .= "'@{[trim($col->[11])]}', " ; #CUSIP
+
$SQLValues .= "'@{[trim($col->[12])]}', " ; #TYPE
$SQLValues .= " @{[trim($col->[13])]} , " ; #Market Price
+
$SQLValues .= " @{[trim($col->[14])]} ) " ; #Value
#** print $SQLStr1 . "\n";
#** print $SQLValues . "\n";
$SQLInsert = $SQLStr1. $SQLValues;
if ( $db->Sql( $SQLInsert ) ) {
print "** ** ** At row: " . $. . " Error on Insert: " . $db
+->Error() . "\n ";
print $SQLInsert . "\n\n" ;
}
else {
$insertCount += 1;
}
} #while
sample data
Portfolio|Hedge|Hedge Price|# Holdings|Cash Balance|GAV~|$Val Long|$Va
+l Short
PORTLIST|None|0.0|22947.0|123466.00|1234566.|9876.9957|9876.00
#|Acct|Ext Sec Code|Issuer Code|Issuer Name|Quantity|SM Description|Us
+ym|Security|Isin|Sedol|Cusip|Type|Mkt|$ Value
1|QQ1234|Y.A|Y.A|DEPOS RECPTS STK|22.0|Y.A S|Y.A|Y.A|US78462QF|2840
+215|78462QF|S|1.69|-10
2|QQ1234|X|X.A|SEL SEC SPDR|-250.0|X.A O 20080118 76.00 AC|X
+.A|XA.U-08|||AX|O|665|25.0
3|QQ1234|SY|S.A|RECPTS STK|500.0|S.A O 20080118 155.00 AC|S.A
+|SA.U-08|||00078462Q9zz|O|20|10.0