1

I have an ugly data file that I need to clean up -

Date          User Name          Item Id     Title                                                                             Quantity        Price Total Amount
1/1/2015     name1             461064485   Description One Has Spaces                                                                  1     $899.99              $899.99
         Real Name 1                Real Address With Spaces                                                  Location, Real 55555                    555-555-5555

1/2/2015     name2 has spaces 461222501   Description still has spaces                                                                  1     $229.99              $229.99
         Real Name                 Real Address 2 w spaces                                                   Real Location2 55556                    555-555-5556

This works best thus far:

awk -F "  +" '/^[0-9]\/*[0-9]\// {print $1, $3, $5, $6, $7}' Table.txt > x.txt

but gets hung up username gets too long and is only one space away from the Item ID. In this case, $2 becomes the desired $2+$3.

I have tried conditional splitting and printing like

awk -F "  +" '/^[0-9]*\/*[0-9]\// {if ( length ($2) >= 15 ) brokenfield=$2 split(brokenfield,subfields," "); print $1, subfields[-1], $4, $5, $6} {if (length ($2) < 15) print $1, $3, $5, $6, $7}' Table.txt > x.txt

which works but processes everything, or processes it twice; or

awk -F "  +" '/^[0-9]\/*[0-9]\// {if ( length ($2) >= 15 ) brokenfield=$2 split(brokenfield,subfields," ") print "$1, subfields[-1], $4, $5, $6"; else print "$1, $3, $5, $6, $7" }' Table.txt > x.txt

but I can't get the syntax right without it processing the line twice, processing all the lines (no idea why, since the search algorithm should only process lines beginning with dates), or getting errors about printing or my else statement.

I plan to upgrade to a printf so I have fixed column widths as well, but I can't get my syntax correct.

Desired output (ideally with fixed column widths):

1/1/2015    461064485    1    $899.99    $899.99
1/2/2015    461222501    1    $229.99    $229.99 

1 Answer 1

2

I took one of your versions and modified a bit:

awk -F "  +" -v'OFS=\t' '/^[0-9]+\/[0-9]+\//{ \
     if(length($2)>=15){ \
           brokenfield=$2; n=split(brokenfield,subfields," "); \
           print $1,subfields[n],$4,$5,$6 \
     } \
     else {print $1,$3,$5,$6,$7} \
}' file

Output:

1/1/2015    461064485   1   $899.99 $899.99
1/2/2015    461222501   1   $229.99 $229.99

A few remarks:

  • you forgot to put braces {} around conditional statements
  • I don't think awk supports negative array index (at least not my gawk)
  • split returns number of array elements, so you can use it as an index of the last array element (n in above case)
  • \t is used as output field separator in order to align columns nicely
1
  • Your suggestion resolved 4173 out of 4175 entries in my file - great in my book! Using n for array length is smart regardless of awk's support (mine allows -1 to indicate the last entry of the array). The OFS=\t is a great solution too!
    – C.A.R.
    Commented Dec 25, 2015 at 1:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .