Perl with CSV file 3

[Cybershmuck]

Hi,

I’ve got a CSV file with the following information that I want to cycle through each line and split the values up by commas. The first line is the header record

BSB,Account Number, Amount (Gross),Account Name,Memo

62919,10213139,$203.40,A Wiesner,
112879,121473373,$125.55,AJ Bagala,
244000,106685045,"$7,605.40",Dell Australia Pty Ltd,
83091,466155928,$287.10,Digiguard.net Pty Ltd,
,,
,,
,,

How do I skip lines with blanks and how do I strip off trailing commas of each record?
Here’s my attempt of the code:

my $fieldnames = 1;
my $header = <IN> if $fieldnames;
@data =  <IN>;
foreach my $currentline (@data)
{

#Need some code her to strip off trailing commas

next if (index($currentline, ",,") > -1); #Skip any lines with just ,, in it	

($bsb, $acct_num, $amt, $acct_name, $memo) = split /,/,  
$currentline;

Thanks in advance

 

[PaulTEG]

When you've read in the line, then extract the commas

$curr_value=~s/,//g;

HTH
--Paul

cigless ...

 

[Cybershmuck]

Thanks for the responses there.

I've got some code here that looks for a comma after finding an instance of " character and then deletes it by using the substr function. How do I do this for multiple instances of commas?

$tehindex = index($currentline, "\"");
	
if ($tehindex > 0) 
{
substr($currentline, index($currentline, ",",  $tehindex), 1) = '';	
}

 

[PaulTEG]

are you extracting the yellow water ... perhaps???
--Paul

 

[mikevh]

Funny, Paul.

Cybershmuck, here's a subroutine I wrote a while back that does what you want (replace something within delimiters). It works well, with the single caveat noted. Use as is, or it may give you ideas for rolling your own.

sub rpldelimxy {
    # Replace $x within $delim's with string $y.
    # $delim and $x may be strings or regexes.  $y must be a string.
    # Passing an empty string in $y will delete $x within $delims.
    # CAVEATS: Currently no check to see if delimiters are paired.
    #    Once we see an opening $delim, we keep replacing $x with $y until
    #    we see a closing $delim ... 
    my ($str, $delim, $x, $y) = @_;
    my $sawdelim = 0;
    my $leny = length($y);
    my $i = 0;
    while ($i < length($str)) {
        my $s = substr($str, $i);
        if ($s =~ /^($delim)/) {
            $sawdelim = $sawdelim? 0: 1;
            $i += length($1);
        } elsif ($s =~ /^($x)/ && $sawdelim) {
            my $lenx = length($1);
            my $temp = substr($str, 0, $i) . $y;
            if ($i < length($str) - $lenx) {
                $temp .= substr($str, $i + $lenx);
            }
            $str = $temp;
            $i += $leny;
        } else {
            $i++;
        }
    }
    return $str;
}

HTH

 

[cntr]

244000,106685045,"$7,605.40",Dell Australia Pty Ltd,"Hi, Mom!"
244000,106685045,"$1,777,605.40",Dell Australia Pty Ltd,
244000,106685045,"$9,111,777,605.40",Dell Australia Pty Ltd,

becomes

244000,106685045,"$7605.40",Dell Australia Pty Ltd,"Hi Mom!"
244000,106685045,"$1777605.40",Dell Australia Pty Ltd,
244000,106685045,"$9111777605.40",Dell Australia Pty Ltd,

Awk:

{ print rpldelimxy( $0,  "\"",  ",",  "" ) }

function rpldelimxy( str, delim, x, y,   n,i)
{ n = xsplit( str, arry, delim )
  for (i=3; i<n; i+=4)
    gsub( x, y, arry[i] )
  str = ""
  for (i=1;i<=n;i++)   str = str arry[i]
  return str
}

# Like split(), but the resulting array has the
# matching as well as the nonmatching substrings.
function xsplit( s, array, re )
{ gsub( re, "\1&\1", s  )
  return split( s, array, "\1" )
}

 

[cntr]

Oops. Made "arry" global.
Change
function rpldelimxy( str, delim, x, y, n,i
to
function rpldelimxy( str, delim, x, y, n,arry,i)