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?
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
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.
tr command instead of sed (eg tr '|' '\012') to avoid regex issues with some delimiters.
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).
sed 's/\t/\n/g;q' $1 | nl -ba in my bin dir for as an executable cols file, as a useful utility.
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.
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
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 |
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
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
$
[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:
|foo|bar|is actually 4 fields but the 1st and 4th are empty. To have two fields, you would wantfoo|bar.