splicing, splitting and joining a field

gogirl · Jan 9, 2003

I am trying to split up a field that contains a client matter number. This number is joined together by a period like this 999999.99999 with 6 numbers to the left of the '.' and 5 to the right. I am trying to:
1. splice the field from an array of strings //field #8
2. split the client/matter#
3. join the client/matter# with a comma
4. pushing it back onto the array

I'm not convinced that this is the best method but I haven't had any luck using a loop with string matching. If you have any suggestions I would really appreciate it.

FYI -- The data is coming out of an ASCII file and being written back to another ASCII file.

Also, what is the simplest way to write out a file?

Here's my code:

use strict;

open(DAT,"newPBX_DATA.TXT&quot

|| die("Cannot Open File!&quot

;
while (<DAT>)
{
chomp;
my @val = split (/,/, $_, 10000);
$climat = splice(@val, 8, 1);
#remove clientmatter from list and
#store it in $climat
$temp = split(/./, $climat, 50);
$new = join(',', $temp[0], $temp[1]);
push($new);
print join (",", @val) . "\n"; # print out.
}
close(DAT);
exit (0);

goBoating · Jan 9, 2003

show us what a typical line from newPBC_DATA.txt looks like, where the client/matter number is in the line, and exactly what you want the output to look like.

'hope this helps

If you are new to Tek-Tips, please use descriptive titles, check the FAQs, and beware the evil typo.

MrGabe · Jan 9, 2003

I liked your breakdown/rebuild idea, and I support it 100%for splitting/joining arrays like you did.
But for string substitions, replacing "in place" is really good.
It was kinda tricky with the period, since is has significance that requires escaping (twice!).
Using your code, I came up with the following answer.
Given a data filename for $dat, the script produces a converted version of the data file, leaving the original data file intact.

Try this:

#Using strict didn't work for me, so I commented it out
#use strict;

$dat = "newPBX_DATA.TXT"; #data filename
$tmp = "tmpPBX_DATA.TXT"; #tmp filename

open(DAT,"< $dat&quot

|| die("Cannot Open File!&quot

;
open(TMP, "> $tmp&quot

|| die("Cannot Open File!&quot

;

$period = "\\."; #for substution, the period (.) has to have \\ before it.
$comma = ","; #this is for easy substituting like '$climat =~ s/$period/$comma/;'
$intclimat = 8; #this for a number representing the element in the array which contains the client-matter number.
$newline;

while (<DAT>)
{
chomp;
my @val = split (/,/, $_, 10000);

#the line below does period to comma change
@val[$intclimat] =~ s/$period/$comma/; #be sure to get the right element!!

#assign the resulting array to string
$newline = join (",", @val) . "\n";

print TMP $newline; # print out to TMP.
}
close(DAT);
close(TMP);
rename($tmp, "$dat.converted.txt&quot

or die "can't rename $tmp : $!";
exit (0);

gogirl · Jan 10, 2003

MrGabe,

Your Awesome! Thank you so much for your help. Your use of variables makes the script easier to read and I really appreciate the comments too. I have one other question for you though. After running my script and looking at my text
file, I realized that the quotes were still surrounding the
client matter number like this: "999999,99999". In order to get it into a mysql database I need to have it look like this: 999999,99999 or "999999","99999". Do you have any
suggestions on how to do this?

I tried substituting the quote(&quot

with whitespace(/s) but it didn't like that. I've tried a few other things but
they haven't worked.

Thanks Again!

MrGabe · Jan 10, 2003

I was a sysadmin/database dude at a law firm for a while, and remember having to deal with Client-Matter numbers in our systems, so I can empathize. I didn't know any Perl then and had to make do with Excel/Word/VBA/VB. I think it's great that you're learning Perl to get your work done. There is a learning curve, but anything you learn can really help you out a bunch.

The simplest change is going from "999999.99999" to "999999","99999" This is because we can do it with one substition, using the code we already have, and just changing the string value of the $comma variable...

CHANGE: $comma = ",";
TO: $comma = "\",\""; #the \ makes the " literal

Give it a whirl. And good luck to you.

-Gabe

gogirl · Jan 10, 2003

Hey Gabe,

I tried that already and it doesn't work. In fact it replaces the whole element with ",". Got any other tricks up your sleeve?

Cara

MrGabe · Jan 10, 2003

Cara, this is very mysterious!

hmmm...

It's working without issue on my side with some test data:

Filename= newPBX_DATA.TXT

"jones", "022550.00001", "abc"
"smith", "035700.02000", "dce"

And here's the script I'm using:
NOTE: I hard code the array element that carries the client Matter number

#use strict;

$dat = "newPBX_DATA.TXT";
$tmp = "tmpPBX_DATA.TXT";

open(DAT,"< $dat&quot

|| die("Cannot Open File!&quot

;
open(TMP, "> $tmp&quot

|| die("Cannot Open File!&quot

;

$period = "\\."; #for substitution, the period (.) has to have \\ before it.
$comma = "\",\""; #this for easy substituting like '$climat =~ s/$period/$comma/;'
$newline;

while (<DAT>) {
chomp;
my @val = split (/,/, $_, 10000);

#the line below does period to comma change
@val[1] =~ s/$period/$comma/; #be sure to get the right element!!

#assign the resulting array to string
$newline = join (",", @val) . "\n";

print TMP $newline; # print out to TMP.
}
close(DAT);
close(TMP);
rename($tmp, $dat."conv.txt&quot

or die "can't rename $tmp: $!";
exit (0);

If you can't see the problem (difference between yours and mine), post a dummy sample line form your data, and I'll look into it.

-Gabe

gogirl · Jan 10, 2003

I had to add a loop to allow for cases where there was no client matter #. I can't see why it would be causing a
problem if I did what you said but for some reason it
didn't work. I tried having two variables one called
$comma1 = "\",\""; #with value
$comma2 = ","; # without value

Here is the code I am using now:

$dat = "newtmpPBX_DATA.TXT"; #data filename
$tmp = "PBX_DATA.TXT"; #tmp filename

open(DAT,"<$dat&quot

|| die("Cannot Open File!&quot

;
open(TMP,">$tmp&quot

|| die("Cannot Open File!&quot

;

$period = "\\."; #for substitution, the period (.) has to have \\ before it.
$comma = ","; #this is for easy substituting like '$climat =~ s/$period/$comma/;
$intclimat = 8; #this for a number representing the element in the array
#which contains the client-matter number.
#$newline;

while (<DAT>)
{
chomp;
my @val = split (/,/, $_, 10000); # split, preserving all fields
for my $i (0 .. @val - 1)
{
my $val = $val[$i];
if($val[$intclimat] =~ /\s+\d+\D\d+/)
{
#the line below does period to comma change
$val[$intclimat] =~ s/$period/$comma/;
}
else
{
$val[$intclimat] = $comma,;
}
}

#assign the resulting array to string
$newline = join (",", @val) . "\n"; # print out.

print TMP $newline; #print out to tmp

}
close(DAT);
close(TMP);
#rename($tmp, "$dat.converted.txt&quot

or die("Can't rename $tmp : $!&quot

;
exit (0);

Here is text after I run this script:

2003-01-07,"18:33","00:00:06", 224, 171," - - - 0","OPERATOR LOC.", 0.00,,,"OPERATOR LOCAL ","OUT "
2003-01-07,"18:34","00:01:24", 224, 171," - -707-8244","AUSTIN TX", 0.00,,,"LOCAL ","OUT "
2003-01-07,"18:34","00:00:42", 462, 173," 1-512-542-0087","AUSTIN TX", 0.05," 013383,00010","LD DIRECT DIAL ","OUT "
2003-01-07,"18:36","00:00:12", 310, 171," -512-473-0244","AUSTIN TX", 0.00,,,"INCOMING ANI ","IN "
2003-01-07,"18:37","00:12:36", 573, 173," 1-915-545-4625","EL PASO TX", 2.35,,,"LD DIRECT DIAL ","OUT "

Could I do something like this if I placed it before
the substitution?:

$val[$intclimat] =~ ((/d+)(/D)(/d+));
# $1 = client#
# $2 = ","
# $3 = matter#

$2 = "\",\"";

$val[intclimat] =~ sprintf ($1, $2, $3);

MrGabe · Jan 10, 2003

Cara, thanks for giving me a reason to have the regex lesson. Poof, that was tricky!

I set this up for three cases:
1) you have "" in the data instead of client-matter number
2) you have nothing between the commas for a client matter number
3) you have a client matter number (I did not check for validity, just assuming...)

You get "999999","99999" for @val[8]if there is a climat#
You get "","" @val[8]if there isn't a climat#, whether it is an empty string or null.

The uniformity of the output should make it easy to import into any database.

here it is:

$dat = "newtmpPBX_DATA.TXT"; #data filename
$tmp = "PBX_DATA.TXT"; #tmp filename

open(DAT,"<$dat&quot

|| die("Cannot Open File!&quot

;
open(TMP,">$tmp&quot

|| die("Cannot Open File!&quot

;

$period = "\\."; #for substitution, the period (.) has to have \\ before it.

$comma1 = "\"\",\"\""; # this creates the empty string vals for 'client' and 'matter' ex:
# changing <data>,,<data> into <data>,"","",<data>

$comma2 = "\",\""; #this is for a "," sequence

$intclimat = 8; #this for a number representing the element in the array
#which contains the client-matter number.
$newline;

while (<DAT>)
{
chomp;
my @val = split (/,/, $_, 10000); # split, preserving all fields
for my $i (0 .. @val - 1)
{
my $val = @val[$i];
if(!$val[$intclimat]=~ /.+/ ) #check for null vals: ...,data,<nothing>,data,...
{
$val[$intclimat] = $comma1;
}
elsif($val[$intclimat]=~ /""/) # check for empty string: ...,data,"",data...
{
$val[$intclimat] = $comma1;
}
else
{
#the line below does period to comma change
$val[$intclimat] =~ s/$period/$comma2/;
}
}

#assign the resulting array to string
$newline = join (",", @val) . "\n"; # print out.

print TMP $newline; #print out to tmp

}
close(DAT);
close(TMP);
rename($tmp, "$dat.converted.txt&quot

or die("Can't rename $tmp : $!&quot

;
exit (0);

try it out. hope it works. My guess is that this is already taking more time than you wanted to spend on it.

Cheers,
-Gabe

gogirl · Jan 10, 2003

Hey Gabe,

It worked! [thumbsup2]

What a fine ending to a Friday! Thanks soooo much for your help. I really do appreciate it.

Best Wishes!!!!

Cara

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

splicing, splitting and joining a field

gogirl

MIS

goBoating

Programmer

MrGabe

MIS

gogirl

MIS

MrGabe

MIS

gogirl

MIS

MrGabe

MIS

gogirl

MIS

MrGabe

MIS

gogirl

MIS

Similar threads

Part and Inventory Search

Sponsor