Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

switching characters

by kevind0718 (Scribe)
on Jan 11, 2008 at 20:31 UTC ( [id://661977]=perlquestion: print w/replies, xml ) Need Help??

kevind0718 has asked for the wisdom of the Perl Monks concerning the following question:

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

Replies are listed 'Best First'.
Re: switching characters
by pc88mxer (Vicar) on Jan 11, 2008 at 20:44 UTC
    How about just using Text::CSV_PP itself to determine where the headings row is?
    ... open $fh ... $csv = Text::CSV_PP->new(); # create a new CSV parser object $csv->sep_char('|'); # look for the header line my $line = 0; my $col; while ($line < 20 && defined($col = $csv->getline($fh) )) { if ($csv->[0] eq '#') { # found it break; } $line++; } unless ($line < 20 && defined($col)) { die "header line not found\n"; } # process the rest of the file while (defined($col = $csv->getline($fh))) { ... }
      Actually I now see the issue.
      What the regular expression to match the beginning of a string.?
      I do belive $row =~ m/#/; is true for the first row. kd
        you'll want to use m/^#/ to match a # at the beginning of the line.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://661977]
Approved by kyle
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others cooling their heels in the Monastery: (7)
As of 2024-03-19 02:29 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found