AWK multi character delimiter

Question

I'm using Bash on Ubuntu and my issue is the following:

I have a large text file with a header and where the delimiter is #|#.

I'm trying to use AWK to get some information on this file. Right now I would like to compute the sum of column 2 group by values of column 1, with this expression:

awk 'BEGIN { FS="\\#\\|\\#" }{arr[$1]+=$2} END {for (i in arr) {print i,arr[i]}}' myfile.txt

There are two issues with the output I get:

• First, if let's say that column 1 takes two unique values value1 and value2, then AWK forms not 2 but 3 groups: value1, value2 and also name_column1.

  As if it didn't understand the first row of the file was a header...

• The second issue is that my output is:

  value1        0
  value2        0
  name_column1  0
  

  So we know the last line of the output is unexpected (as mentionned previously) so let's focus on the two first ones. Here, both sums are null but I know that at least one of them should be strictly greater than 0 because the command

  awk 'BEGIN { FS="\\#\\|\\#" }{sum1+=2;}END{print sum1;}' myfile.txt
  

  gives me 251597850.

So either there is something wrong with my last command (regular sum), either with the previous one (sum+group by).

Does someone know how to fix this?

EDIT: My file text looks like something like that:

Column1#|#Column2#|#Column3

0300#|#0.00#|#0000

where 0300 is the value1 previously mentioned (it is not a number but a category).

EDIT2:

awk 'BEGIN { FS="\\#\\|\\#" }{sum1+=2;}END{print sum1;}' myfile.txt

gives me 2*(number of rows in file), which is obviously not what I want, so the command should be:

awk 'BEGIN { FS="\\#\\|\\#" }{sum1+=$2;}END{print sum1;}' myfile.txt

EDIT3 :

Turns out both my commands were wrong because of the separator. The right command for the group by therefore is:

awk 'BEGIN { FS="#[|]#" } FNR>1 {arr[$1]+=$2} END { for (i in arr) print i,arr[i] }' file.txt

Answer 1

The brief answer is that the FS variable is a RE (regular expression, or pattern) in this case. So if any of the actual data characters is "special" in the context of a RE, it needs to be escaped in the RE to ensure it is treated as itself, and not as an operator.

In this case, the culprit is |, which is the alternation operator. The items either side of it are alternative REs, any of which will treated as a match. For example, the field separator a|u|o|i|e will split fields at every vowel.

The RE #|# is therefore somewhat redundant: it specifies # as a field separator twice, and ignores the repetition.

The fix is to escape the |. My preferred method is to convert the | into a bracket expression (character class) [|] which demotes the | to represent itself.

Alternatively, characters can be escaped by \, so your separator can be written as #\\|#.

I said the escape is \ -- why did I write it twice? That's another odd rule (and the reason backslashes often cause issues in awk patterns).

There are two ways to write awk REs: as a pattern like /myRE/, or as a string like "myRE".

The /myRE/ form works (by default) as a boolean and can be used alone as a pattern in the pattern { action } model of awk source, or within the action like { if (/myRE/) ...}. In this case, it is matched against the whole line, because there is no syntactic indication as to what else it should be applied. It can also be matched against a more specific target like a field $6 ~ /myRE/ or a variable myVar ~ /myRE/. In this form, a character is escaped by a single \.

However, when an RE is written as a string, awk does not know it may be invoked later as an RE. It is parsed twice: the first time for the usual string escapes in the original source (things like \t for Tab, \n for newline, and \\ for backslash): and then again when it is used with a ~ operator, or in the match() or split() functions.

Declaring FS is treated as a string, so any backslash must be doubled. This is true whether you declare FS with -F or -v FS= on the command line, or like BEGIN { FS = "myRE" }.

I mentioned "brief answer", and such things are nearly always wrong. There is an exception, and an exception to that exception.

It is hard to write a one-character regular expression, because the special operators need something to operate on. So any one-character value for FS is treated as itself literally. You can write '-F|' or -v 'FS=|' or BEGIN { FS = "|" } and have your fields split by a pipe symbol.

The exception to the one-character rule is an FS consisting of single space (which is the default). This simply makes each word on your line into a field. Being awk, simple is a comparative term:

(1) The separator is "white-space", defined as any continuous mixed sequence of ASCII Space, Horizontal Tab, and NewLine. (You will only see Newline if an alternative Record Separator is in force.)

(2) Leading and Trailing whitespace, on the line as a whole, is not a field separator. (If any other FS is at the beginning or end of a line, there is an implied extra blank field before or after it, respectively.)

My go-to reference is the GNU/awk online manual.

Although this answer is itself ridiculously long and complex, the manual devotes around 600 lines to Section 3 -- Regular Expressions, and another 250 to Section 4.5 -- Specifying How Fields Are Separated.