split and negative value 
Author Message
 split and negative value

Good day,

I am trying to correct two unrelated occurrence that appear in several
legacy data file that are under conversion.  The data is in 9 fields,
how ever some times the first field appears to be concatenated with the
second.  I have tried the split function with little success.  The
second problem occurs when a numeric field should have a negative
value.  The minus sign denoting the value of the field appears after the
numeric value, how ever in order to process correctly, the minus sign
needs to precede the numeric portion of the field.

Any and all suggestions welcome.

example;

O4       14      7,521.00        5.0     1,504.20        0.00    0.00
0.00    7,521.00
O4       12      4,127.00        3.0     1,375.66        523.60-
0.00    0.00    3,603.40
O3E16            1,353.00        1.0     1,353.00        0.00    0.00
0.00    1,353.00
W4       26      1,305.00        1.0     1,305.00        0.00    0.00
0.00    1,305.00

desired outcome;

O4       14      7,521.00        5.0     1,504.20        0.00    0.00
0.00    7,521.00
O4       12      4,127.00        3.0     1,375.66        -523.60
0.00    0.00    3,603.40
O3E     16       1,353.00        1.0     1,353.00        0.00    0.00
0.00    1,353.00
W4       26      1,305.00        1.0     1,305.00        0.00    0.00
0.00    1,305.00

Thanks in advance

Eric



Sat, 10 Jan 2004 10:41:25 GMT  
 split and negative value

Quote:

> Good day,

> I am trying to correct two unrelated occurrence that appear in several
> legacy data file that are under conversion.  The data is in 9 fields,
> how ever some times the first field appears to be concatenated with the
> second.  I have tried the split function with little success.  The
> second problem occurs when a numeric field should have a negative
> value.  The minus sign denoting the value of the field appears after the
> numeric value, how ever in order to process correctly, the minus sign
> needs to precede the numeric portion of the field.

> Any and all suggestions welcome.

> example;

> O4       14      7,521.00        5.0     1,504.20        0.00    0.00
> 0.00    7,521.00
> O4       12      4,127.00        3.0     1,375.66        523.60-
> 0.00    0.00    3,603.40
> O3E16            1,353.00        1.0     1,353.00        0.00    0.00
> 0.00    1,353.00
> W4       26      1,305.00        1.0     1,305.00        0.00    0.00
> 0.00    1,305.00

> desired outcome;

> O4       14      7,521.00        5.0     1,504.20        0.00    0.00
> 0.00    7,521.00
> O4       12      4,127.00        3.0     1,375.66        -523.60
> 0.00    0.00    3,603.40
> O3E     16       1,353.00        1.0     1,353.00        0.00    0.00
> 0.00    1,353.00
> W4       26      1,305.00        1.0     1,305.00        0.00    0.00
> 0.00    1,305.00

> Thanks in advance

> Eric

What defines the first field? Need something to do by in order to
extract the first field from the second, eh? >;)
Ok, in any event, so you want to make a series of edits to a text file?
Let's use our old pal sed.
sed -e 's/\([0-9]*\.[0-9][0-9]\)-/-\1/g' temp.txt
where temp.txt is the data give above will do the trick.
Hope this helps you!!


Sat, 10 Jan 2004 13:22:44 GMT  
 split and negative value

Quote:
> What defines the first field? Need something to do by in order to
> extract the first field from the second, eh? >;)
> Ok, in any event, so you want to make a series of edits to a text file?
> Let's use our old pal sed.
> sed -e 's/\([0-9]*\.[0-9][0-9]\)-/-\1/g' temp.txt
> where temp.txt is the data give above will do the trick.
> Hope this helps you!!

Oh, and I also made the assumption that the fields with the misplaced
'-' were dollar amounts. Seemed to follow from your example data.......
Feel free to clarify the problem more, as I already suggested, after
all, you probably care more about this problem than I do >;)


Sat, 10 Jan 2004 13:32:17 GMT  
 split and negative value

Quote:


>>I am trying to correct two unrelated occurrence that appear in several
>>legacy data file that are under conversion.  The data is in 9 fields,
>>how ever some times the first field appears to be concatenated with the
>>second.  I have tried the split function with little success.  The
>>second problem occurs when a numeric field should have a negative
>>value.  The minus sign denoting the value of the field appears after the
>>numeric value, how ever in order to process correctly, the minus sign
>>needs to precede the numeric portion of the field.
...
>>example;

>>O4       14      7,521.00        5.0     1,504.20        0.00    0.00
>>0.00    7,521.00
>>O4       12      4,127.00        3.0     1,375.66        523.60-
>>0.00    0.00    3,603.40
>>O3E16            1,353.00        1.0     1,353.00        0.00    0.00
>>0.00    1,353.00
>>W4       26      1,305.00        1.0     1,305.00        0.00    0.00
>>0.00    1,305.00

>>desired outcome;

>>O4       14      7,521.00        5.0     1,504.20        0.00    0.00
>>0.00    7,521.00
>>O4       12      4,127.00        3.0     1,375.66        -523.60
>>0.00    0.00    3,603.40
>>O3E     16       1,353.00        1.0     1,353.00        0.00    0.00
>>0.00    1,353.00
>>W4       26      1,305.00        1.0     1,305.00        0.00    0.00
>>0.00    1,305.00
...
>What defines the first field? Need something to do by in order to
>extract the first field from the second, eh? >;)

Since it looks like each record has experienced line wrapping diring posting
(since there are 9 fields as the OP states), looks like the first field
should be at most the first 3 nonwhitespace characters, doesn't it?

Quote:
>Ok, in any event, so you want to make a series of edits to a text file?
>Let's use our old pal sed.
>sed -e 's/\([0-9]*\.[0-9][0-9]\)-/-\1/g' temp.txt
>where temp.txt is the data give above will do the trick.

Nah, let's use our smarter friend awk since sed is braindead with regard to
fields.

{
    if (/^[^ \t][^ \t][^ \t]/)
        $0 = substr($0, 1, 3) " " substr($0, 4)  # should rebuild $1..$9 too

    for (i = 2; i <= NF; ++i)
        if ($i ~ /-$/) $i = "-" substr($i, 1, length($i) - 1)

    printf(your_format_string_here, $1, $2, $3, $4, $5, $6, $7, $8, $9)

- Show quoted text -

Quote:
}



Sat, 10 Jan 2004 14:30:36 GMT  
 split and negative value

Quote:

> example;

> O4       12      4,127.00        3.0     1,375.66        523.60-
> 0.00    0.00    3,603.40
> O3E16            1,353.00        1.0     1,353.00        0.00    0.00
> 0.00    1,353.00

> desired outcome;

> O4       12      4,127.00        3.0     1,375.66        -523.60
> 0.00    0.00    3,603.40
> O3E     16       1,353.00        1.0     1,353.00        0.00    0.00
> 0.00    1,353.00

{
    for (nf = 1; nf<=NF; nf++ )
    {
        if ( sub(  /-$/, "", $nf ) == 1 ) # trailing sign?
            $nf = "-" $nf                 # put it in front

        if ( split( substr( $nf, 2 ), ar, /[A-Z]/ ) == 2 )  # embedded letter?
            $nf = substr( $nf, 1, 1 ) "" ar[1] " " ar[2]    # replace by " "
    }
    $1 = $1                                                 # re-arrange fields

Quote:
}

{ print }


Sat, 10 Jan 2004 15:51:32 GMT  
 split and negative value

Quote:

> Good day,

> I am trying to correct two unrelated occurrence that appear in several
> legacy data file that are under conversion.  The data is in 9 fields,
> how ever some times the first field appears to be concatenated with the
> second.  I have tried the split function with little success.  The
> second problem occurs when a numeric field should have a negative
> value.  The minus sign denoting the value of the field appears after the
> numeric value, how ever in order to process correctly, the minus sign
> needs to precede the numeric portion of the field.

> Any and all suggestions welcome.

> example;

> O4       14      7,521.00        5.0     1,504.20        0.00    0.00
> 0.00    7,521.00
> O4       12      4,127.00        3.0     1,375.66        523.60-
> 0.00    0.00    3,603.40
> O3E16            1,353.00        1.0     1,353.00        0.00    0.00
> 0.00    1,353.00
> W4       26      1,305.00        1.0     1,305.00        0.00    0.00
> 0.00    1,305.00

> desired outcome;

> O4       14      7,521.00        5.0     1,504.20        0.00    0.00
> 0.00    7,521.00
> O4       12      4,127.00        3.0     1,375.66        -523.60
> 0.00    0.00    3,603.40
> O3E     16       1,353.00        1.0     1,353.00        0.00    0.00
> 0.00    1,353.00
> W4       26      1,305.00        1.0     1,305.00        0.00    0.00
> 0.00    1,305.00

> Thanks in advance

> Eric

Hello,

not sure if I really understood, but shouldn't this work:

awk '{i=1; do {if ($i~/[0-9]-/) sub("-"," ",$i) && $i=$i*-1;i++}while
(i<=NF)}{print $0}' infile

Michael Heiming



Sun, 11 Jan 2004 04:14:17 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Comparing negative string values

2. Negative values in .ini

3. Negative rotation values

4. help reading negative values in data file - test code and test data

5. Thank you - re:help reading negative values in data file

6. How are negative values stored in COMP?

7. why is a negative shift value illegal?

8. Why is time.clock() returning negative float values (on 64bit Digital UNIX Alpha)

9. Square root of a negative rral value

10. Looking for the greatest negative float value

11. negative values in profile output

12. Gnu Smalltalk with negative value pointer

 

 
Powered by phpBB® Forum Software