2

I have a text file with many lines that I need to reformat, but I'm getting stuck. Each line will contain a HeaderSegment followed by 1 or more Detail Segments which also contain Sub Segments.

I need to read the file, match patterns then "do stuff" (stuff explained below).

Here are 2 sample lines from my text file: (starting with HeaderSegment)

HeaderSegment:1234:989898:51:2101211748:29:DetailSegment:123467:654321:2:20210122112325:C:0:0:Purchased:SubSegment:064:null:Cash:Whaler:DetailSegment:123468:814211:1:20210121233042:N:0:147:Refund:SubSegment:000:null:Check:Everglades:DetailSegment:234569:825455:1:20210121233113:N:0:685:Purchased:SubSegment:000:null:Cash:Key West:DetailSegment:201754:663854:2:20210122012327:P:128:128:Purchased:SubSegment:000:null:null:null
HeaderSegment:1234:989898:22:2101211750:28:DetailSegment:55555:6637948:0:20210122013332:N:0:401:Refund:SubSegment:000:null:Credit:Whaler
HeaderSegment:1234:989898:22:2101211750:28:DetailSegment:55555:6637948:0:20210122013332:N:0:401:Sale:SubSegment:000:null:Credit:Whaler:SubSegment:30757:null:Cash:Whaler:SubSegment:25500:null:Credit:Seavee

The HeaderSegment will always start with Header and contain 5 fields of data.

The DetailSegment will always start with DetailSegment and contain 8 fields of data and will have 1 or more SubSegments attached to it.

The SubSegments will be attached to DetailSegments and will contain 4 fields of data.

There will only be 1 Header per line, but there could be multiple detailsegments and subsegments.

I need to parse each line of the text file and create a new output that contains multiple lines for a line for each detailsegment. The line will contain:

  • HeaderSegment
  • DetailSegment
  • sum of the first field of each SubSegment between the DetailSegment and the next one (if there are 2 in the same line).
  • and the fields separator will need to change to ,

Sample output would be:

1234,989898,51,2101211748,29,123467,654321,2,20210122112325,C,0,0,Purchased,064
1234,989898,51,2101211748,29,123468,814211,1,20210121233042,N,0,147,Refund,000
1234,989898,51,2101211748,29,234569,825455,1,20210121233113,N,0,685,Purchased,000
1234,989898,51,2101211748,29,201754,663854,2,20210122012327,P,128,128,Purchased,000
1234,989898,22,2101211750,28,55555,6637948,0,20210122013332,N,0,401,Refund,000
1234,989898,22,2101211750,28,55555,6637948,0,20210122013332,N,0,401,Sale,56257

I have tried to use awk, but I'm having a hard time breaking out the segments due to lack of awk knowledge.

Hoping someone can offer some guidance (explanations on recommended solutions are very welcome as it will help me "learn" this easier).

2 Answers 2

2
awk -F':?(Header|Detail)Segment:' '
    { sumPos=10;
      for(i=3; i<=NF; i++) { 
          split($i, tmp, ":")
          for(x in tmp) { 
              sum+=tmp[sumPos]; sumPos+=5
          };
          gsub(/:|:SubSegment.*/, ",", $i)
          gsub(/:/, ",", $2)
          printf("%s,%s%.3d\n", $2, $i, sum)
          sum=0
      };
}' infile
1

you mentioned "learn" so here is a rather simple awk script which, i think, solves your problem. it is not optimized neither in speed nor in code length but it is hopefully readable. i will try to explain the parts so that you can learn from it.

here is the codez:

BEGIN {
  FS=":"
  OFS=","
}
{
  pos=readheader()
  sanitycheck(pos)
  printresult()
}
func readheader() {
  r["a"]=$2
  r["b"]=$3
  r["c"]=$4
  r["d"]=$5
  r["e"]=$6
  pos=7
  dcount=0
  while($(pos) == "DetailSegment") {
    pos=readdetail(pos+1, dcount)
    dcount++
  }
  return pos
}
func readdetail(pos, dcount) {
  r["detail"][dcount]["a"]=$(pos+0)
  r["detail"][dcount]["b"]=$(pos+1)
  r["detail"][dcount]["c"]=$(pos+2)
  r["detail"][dcount]["d"]=$(pos+3)
  r["detail"][dcount]["e"]=$(pos+4)
  r["detail"][dcount]["f"]=$(pos+5)
  r["detail"][dcount]["g"]=$(pos+6)
  r["detail"][dcount]["h"]=$(pos+7)
  pos=pos+8
  scount=0
  while($(pos) == "SubSegment") {
    pos=readsub(pos+1, dcount, scount)
    scount++
  }
  return pos
}
func readsub(pos, dcount, scount) {
  r["detail"][dcount]["sub"][scount]["a"]=$(pos+0)
  r["detail"][dcount]["sub"][scount]["b"]=$(pos+1)
  r["detail"][dcount]["sub"][scount]["c"]=$(pos+2)
  r["detail"][dcount]["sub"][scount]["d"]=$(pos+3)
  return pos+4
}
func sanitycheck(pos) {
  if (pos <= NF) {
    print "error line "NR" only parsed "pos" of "NF" fields"
  }
}
func printresult() {
  for(d in r["detail"]) {
    subsum=0
    for(s in r["detail"][d]["sub"]) {
      subsum+=r["detail"][d]["sub"][s]["a"]
    }
    print r["a"],r["e"],r["detail"][d]["a"],r["detail"][d]["h"],subsum
  }
}

save this in a file called filter.awk. and put input in file called input and the type command

$ awk -f filter.awk input

or pipe the input from the source

$ fromwherecomesinput | awk -f filter.awk

this is the output from processing the three sample lines you provided

1234,29,123467,Purchased,64
1234,29,123468,Refund,0
1234,29,234569,Purchased,0
1234,29,201754,Purchased,0
1234,28,55555,Refund,0
1234,28,123468,Refund,0
1234,28,234569,Purchased,0
1234,28,201754,Purchased,0
1234,28,55555,Sale,56257
1234,28,123468,Refund,0
1234,28,234569,Purchased,0
1234,28,201754,Purchased,0

i did not output all of the fields. i was too lazy to type them all out.

i am not sure if i understood your output requirement correctly. maybe i got it wrong. but i try to explain the code so if you understand the rest of the codes you can change the output function as you need.

if a line is not well formed it will output something like this:

error line 3 only parsed 7 of 20 fields

other than this debug output line i made no other code for error handling and reporting.

explanation of the code:

first a bit overview what the code does: awk reads the input line by line. every line is split at colon. then we loop over the fields and look for segments. then we collect the data in a tree like structure. last we iterate over the tree and calculate the wanted output.

thankfully all segments have fixed numbers of fields. this makes looking for segments very easy.

the tree structure is roughly like this: in the root there are the five header variables and a list of details. in each detail there are the eight detail vars and a list of subs. in each sub there are the four sub vars.

at the end i linked a bunch of relevant pages from the fine awk manual. so if you want to learn more about some topic look at the end.

let's begin detailed explanation

BEGIN {
  FS=":"
  OFS=","
}

the BEGIN block is executed by awk before reading first line from input. it is mostly used to initialize variables.

there is also an END block which would be executed after the last line is read. it is commonly used to print end results. in our case we have result per line but no cumulated end result so we have no END block.

FS is the field separator. this tells awk how to split each input line in to the so called fields. this is one of the core strengths of awk. often a suitable field separator value is half of the solution. in this case we set the field separator to colon (:).

OFS is the output field separator. this will be the character between the fields in a print statement. in this case we set to comma (,).

these variables are called controlling variables because they change how awk works.

next is a "for each line" code block

{
  pos=readheader()
  sanitycheck(pos)
  printresult()
}

this code block will be executed for every line (for every record in awk terms). i extracted code out to functions so this block is short and sweet.

(note you can also change the record separator to something else than newline and then a record might be more or less than an input line.)

note that in awk all variables are global (even over code blocks) so the functions are mostly just for structure for the humans. that is why printresult() can print the result without any data passed to it. it just prints the result from the global variables. returning pos from readheader() is also not strictly necessary since it is global but i liked it so i left it in.

note also in awk there are only two types of variables: strings and numbers (and arrays). conversion is implicit. uninitialized variables are always zero or the empty string. this is an oversimplification. read manuals linked at end.

code blocks are often prefixed with something. for example

/foo/ { ... }

or

NR > 1 { ... }

those are conditios. that means the block will only be executed if the current record satisfies the condition.

our code block has no such condition so it is executed for every line.

in awk jargon the condition is called pattern and the code blocks are called actions.

next explanation of the functions:

func readheader() {
  r["a"]=$2
  r["b"]=$3
  r["c"]=$4
  r["d"]=$5
  r["e"]=$6
  pos=7
  dcount=0
  while($(pos) == "DetailSegment") {
    pos=readdetail(pos+1, dcount)
    dcount++
  }
  return pos
}

here the code is starting to look like a normal programming language. remember this function is called from the "for each line" block. so this function is called once per line.

$2 and the other dollar numbers are references to the fields. the fields are the parts of the line after awk did the splitting. in our case the fields are the values between the colons.

(the $ in awk is only used for field variables. and in regexes where they have totally different meaning but that is another story altogether. we have no regexes in this code so none of that here)

$0 is always the entire line.

$1 in our case would always be HeaderSegment so we just skip over that (no error checking). $2 to $6 are the five values of the HeaderSegment.

we store these variables in an array called r. the short name is dangerous because everything is global but we need this variable a lot and i am lazy so i took a short name.

arrays in awk are not arrays like in c or java but rather maps or dictionaries. key value mapping. the keys can be any string or number. if iterated over then order is basically random. the values can be anything including other arrays. these array of arrays is what we use to build the tree.

i used the keys "a" to "e" because i have no better names for the values. you know the semantic meaning of the values and can give them more meaningful names like "customerID" or "froobazzaloopaCount".

$(pos) is a calculated or indirect field variable. first the part in the parens is evaluated. then the field is referenced. so if pos is 7 then $(pos) is $7 and $(pos+1) is $8. this is especially funky if looping over field like we do here looping looking for the next DetailSegment.

in awk jargon this is called Nonconstant Field Numbers.

the other functions (readdetail and readsub) work similar.

the sanitycheck function:

func sanitycheck(pos) {
  if (pos <= NF) {
    print "error line "NR" only parsed "pos" of "NF" fields"
  }
}

NF is the number of the fields in this record. pos was the variable we used to keep track what field we look at next. so if pos is less than NF something was wrong. then we report the error.

NR is the number of the current record. since in our case a record is basically a line this is the line number.

these variables are called information variables because they give information about the state awk is currently in.

the print result function:

func printresult() {
  for(d in r["detail"]) {
    subsum=0
    for(s in r["detail"][d]["sub"]) {
      subsum+=r["detail"][d]["sub"][s]["a"]
    }
    print r["a"],r["e"],r["detail"][d]["a"],r["detail"][d]["h"],subsum
  }
}

no big surprises. this work like most modern languages now do. for(d in r["detail"]) iterates over the keys in the array r["detail"]. the first loop iterates over the details. the second loop iterates over the subs in the detail.

for every detail print the numbers and sum of the first value in subs.

one note about the print statement:

we have here print 1,2,3 (separated by comma) and the output is 1,2,3 (separated by comma). this is because we have OFS (output field separator) set to comma. if OFS were for example # then print 1,2,3 will output 1#2#3.

note print "1,2,3" (quoted) will always be 1,2,3 regardless of OFS because this time the comma is a literal comma.

i hope this helped you to understand how to solve your problem using awk. hopefully i was also able to explain things good enough so you can adapt the code to your further needs.


links to related topics in the fine awk manual

more about the BEGIN and END blocks: https://www.gnu.org/software/gawk/manual/html_node/Using-BEGIN_002fEND.html

more about the field separator (FS): https://www.gnu.org/software/gawk/manual/html_node/Field-Separators.html

more about the "controlling" variables (FS, OFS and more): https://www.gnu.org/software/gawk/manual/html_node/User_002dmodified.html

more about the "information" variables (NR and NF): https://www.gnu.org/software/gawk/manual/html_node/Auto_002dset.html

more about records (which are most commonly lines): https://www.gnu.org/software/gawk/manual/html_node/Records.html

more about variable visibility (everything is global): https://www.gnu.org/software/gawk/manual/html_node/Global-Namespace.html but some can be local https://www.gnu.org/software/gawk/manual/html_node/Variable-Scope.html

more about types of variables (strings and numbers): https://www.gnu.org/software/gawk/manual/html_node/Variable-Typing.html

more about patterns and actions (conditions and code blocks): https://www.gnu.org/software/gawk/manual/html_node/Patterns-and-Actions.html

more about arrays (key value maps or dictionaries): https://www.gnu.org/software/gawk/manual/html_node/Arrays.html

more about field numbers (the dollar number things or field variables): https://www.gnu.org/software/gawk/manual/html_node/Fields.html

more about nonconstant field numbers (calculated or indirect field variables): https://www.gnu.org/software/gawk/manual/html_node/Nonconstant-Fields.html

You must log in to answer this question.

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