Using AWK to select rows with specific value in specific column

Question

I have a big csv file, which looks like this:

1,2,3,4,5,6,-99
1,2,3,4,5,6,-99
1,2,3,4,5,6,-99
1,2,3,4,5,6,25178
1,2,3,4,5,6,27986
1,2,3,4,5,6,-99

I want to select only the lines in which the 7th columns is equal to -99, so my output be:

1,2,3,4,5,6,-99
1,2,3,4,5,6,-99
1,2,3,4,5,6,-99
1,2,3,4,5,6,-99

I tried the following:

awk -F, '$7 == -99' input.txt > output.txt
awk -F, '{ if ($7 == -99) print $1,$2,$3,$4,$5,$6,$7 }' input.txt > output.txt

But both of them returned an empty output.txt. Can anyone tell me what I'm doing wrong? Thanks.

Solution 1

The file that you run the script on has DOS line-endings. It may be that it was created on a Windows machine.

Use dos2unix to convert it to a Unix text file.

Alternatively, run it through tr:

tr -d '\r' <input.txt >input-unix.txt

Then use input-unix.txt with your otherwise correct awk code.

---

To modify the awk code instead of the input file:

awk -F, '$7 == "-99\r"' input.txt >output.txt

This takes the carriage-return at the end of the line into account.

Or,

awk -F, '$7 + 0 == -99' input.txt >output.txt

This forces the 7th column to be interpreted as a number, which "removes" the carriage-return.

Similarly,

awk -F, 'int($7) == -99' input.txt >output.txt

would also remove the \r.

Solution 2

awk -F, '{if($7==-99)print $0}'

will do that...

Solution 3

awk -F',' '$7 ~ /-99/ {print $0}' filename.csv > result.csv

1. Please note that ',' defines your separator to be comma.

2. $ defines column. so, $7 defines the column number which you want to have a special value. here 7.

3. ~ /-99/ searches for -99. you might put anything you need.

4. $0 stands for ALL the columns in the file. You could simply write $1","$2","......if you do not want to print only specific columns.(or $1$2... if you do not need comma as separator for your results)

5. > result.csv saves the output instead of printing it in terminal in result.scv file.

Comments

[Sundeep]

the first command works for me, second has a minor typo (closing parenthesis in print stmt) and doesn't set OFS... what is your awk version? probably you have dos style line endings? (but that doesn't cause an issue when I checked)

[Isabela Martins]

How do I know my awk version? Yes, I typed it wrong in here, but it's correct in my script.

[Satō Katsura]

With old awk you could try awk -F, '$7 == "-99"'.

[Sundeep]

for version, try the command awk --version if that doesn't work, check man awk

[Isabela Martins]

My version is 1.2

[Isabela Martins]

These have also returned an empty output.

[Kusalananda]

As would awk -F, '$7 == -99' input.txt (from the question), which means that there's something else going on than just getting the awk code right.

[Isabela Martins]

Empty output...

[Isabela Martins]

I tried the "-99\r" and it worked! That was it. Thank you very much!!

[Stephen Kitt]

Note that Wayne’s approach is simpler still, so this isn’t the simplest solution ;-).

[MoRe]

+1 Thanks for explaining the command for an awk noob like me :-)