help with data formatting

Shell Programming and Scripting
1

Hi,

I have data coming in like below. Not all data is like that, these are the problem records that is causing the ETL load to fail. Can you pls help me with combining theese broken records!

001800018000000guyMMAAY~acct name~acct type~~"address part 1
address part2"~city~STATE~ZIP~COUNTRY~(123) 123-1234~~~~~~~~~~~~~~~~~^M
0018000000gwQ63AAE~acct name~acct type~~"address part 1
address part2"~city~state~zip~country~(123) 123-1234~(123) 123-1234~~~~~~~~~~~~~~~~^M

Appreciate your time and effort!

Thanks,

2

What do you mean by broken records? Can you please clarify the problem by giving the desired output?

3

The desired out put is

001800018000000guyMMAAY~acct name~acct type~~"address part 1 address part2"~city~STATE~ZIP~COUNTRY~(123) 123-1234~~~~~~~~~~~~~~~~~^M
0018000000gwQ63AAE~acct name~acct type~~"address part 1 address part2"~city~state~zip~country~(123) 123-1234~(123) 123-1234~~~~~~~~~~~~~~~~^M
4

Assuming that the the area-code (xxx) is always after the 'break point', and is always present, this might work:

awk '
    /\([0-9][0-9][0-9]\)/ {
        if( buffer )
            printf( "%s%s\n", buffer, $0 );
        else
            print;
        buffer = "";
        next;
    }
    { buffer = $0; }
' input-file >fixed-file
5

Hi,

Appreciate the quick response.

It kind of worked on the problem records with the exception that it has combined multiple non problem records into one.

like 459484~~~~~~~~~~~~~~^M001C000000wO3Y8IAK~

Thanks,
varman

6

The problem is that my script assumed there'd be an area code in each complete record.

Assuming that the tilda characters are field seperators, is there a fixed number of fields per good record? That'd be the best way to determine if a record is broken.

7

The number of fields are 12.

Thanks,
Varman

8

Hi,

Cna someone help me getout of this issue, pls.

Thanks,
Varman

9

This might get you going:

awk -F '~' '
    NF < 12 {
            if( !buffer )
                buffer=$0;
            else
            {
                printf( "%s%s\n", buffer, $0 );
                buffer = "";
            }
            next;
     }
    { print; }
'

I do note that your example broken records have more than 12 fields.