20

Suppose I have a pipe separated file like:

|Sr|Fruits|Colors|
|1 |apple |red|
|2 |orange |orange
|3 |grapes |purple|

Here it is evident using awk that, $2 is Fruits and $3 is the colors column.

In future if the order of the columns change, is it possible to determine the column number using the string?

I.e Colors is $3 and Fruits is $2?

1
  • 6
    For future information, with the exception of the 3rd line, your file has 5 fields, not 3. A field is defined by the field separator so |foo|bar| is actually 4 fields but the 1st and 4th are empty. To have two fields, you would want foo|bar.
    – terdon
    Commented May 30, 2015 at 17:05

7 Answers 7

18

It feels a little bit clumsy but I always find the column numbers using the following line of code:

head -1 file | sed 's/delimiter/\n/g' | nl
Here I take the header row of the file and pipe it to sed replacing the delimiter with \n. The result is that every column header is now on a new line. Finally I pipe this to nl which adds row numbers which correspond to the original column numbers.

4
  • 1
    You may wish to consider a tr command instead of sed (eg tr '|' '\012') to avoid regex issues with some delimiters. Commented Aug 18, 2016 at 20:27
  • It may feel clumsy, but it's actually shorter and simpler than any other answer. Commented Mar 20, 2017 at 1:06
  • 2
    As long as you’re using sed, you can eliminate the head and do sed 's/|/\n/g;q' file | nl (which is even shorter). Also, consider using nl -ba to number the blank lines (fields). Commented Oct 2, 2020 at 8:21
  • 1
    I have now saved sed 's/\t/\n/g;q' $1 | nl -ba in my bin dir for as an executable cols file, as a useful utility.
    – Glubbdrubb
    Commented Apr 11, 2022 at 8:22
10

You can try:

$ awk -F'|' '
{
  for(i=1;i<=NF;i++) {
    if($i == "Fruits")
      printf("Column %d is Fruits\n", i-1)
    if($i == "Colors")
      printf("Column %d is Colors\n", i-1)
  }
  exit 0
}
' file
Column 2 is Fruits
Column 3 is Colors

A note that the actually column for Fruits and Colors are $3 and $4.

0
8

Maybe it is better to print all the columns present in the first row, in order to check not only for these two fields, but also detecting new columns, their names, order changes, etc.

awk -F'|' ' { for (i = 1; i <= NF; ++i) print i, $i; exit } ' file

output:

1
2 Sr
3 Fruits
4 Colors
5
0
2

List all column headers:

awk 'BEGIN{ FS="|" }
     { for(fn=1;fn<=NF;fn++) {print fn" = "$fn;}; exit; }
    ' file

Output:

$1 = 
$2 = Sr
$3 = Fruits
$4 = Colors
$5 = 

Using the label-text, print the colmns of your choice, in the order you choose:

awk 'BEGIN{ FS="|" }
     NR==1 { split(columns, c) 
             for(fn=1;fn<=NF;fn++) hdr[$fn]=fn; next; }
     { printf( FS ); for (text in c) printf( "%s", $hdr[c[text]]FS ); print "" }
    ' columns="Colors|Fruits" file

Output:

|red|apple |
|orange|orange |
|purple|grapes |
1

Another possibility is to treat the field separator | as a record separator, and then process the first line.

In colnum.awk:

BEGIN {
  RS = "|" 
}
/^Fruits$|^Colors$/ {
  print $0, NR - 1
}
$ head -n1 fruits.txt | awk -f colnum.awk
Fruits 2
Colors 3
1

Use tr to convert | to a new line. Then calculate the line with nl

$ head -1 file.txt | tr '|' '\n' | nl
       
     1  Sr
     2  Fruits
     3  Colors
       
$ 
1
  • Unix is awesome, and so is this answer
    – Paul
    Commented Oct 3, 2022 at 21:37
0
[me@mine May31TestAchive]$ cat file.csv
|Sr|Fruits|Colors|
|1 |apple |red|
|2 |orange |orange
|3 |grapes |purple|
[me@mine May31TestAchive]$ grep  -oi "^|\([a-z]*|\)\+Colors" file.csv | tr -cd "|" | wc -c
3

It says:

  1. Return the only the part of the first matching line that goes "|" letters ...(repeats?) ..."Color".
  2. Convert that output, delete everything that isn't a "|"
  3. Take that output and then count the number of characters.

You must log in to answer this question.

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