9

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.

  • 3
    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) – Sundeep Oct 21 '17 at 15:36
  • How do I know my awk version? Yes, I typed it wrong in here, but it's correct in my script. – Isabela Martins Oct 21 '17 at 15:58
  • With old awk you could try awk -F, '$7 == "-99"'. – Satō Katsura Oct 21 '17 at 15:59
  • for version, try the command awk --version if that doesn't work, check man awk – Sundeep Oct 21 '17 at 16:02
  • My version is 1.2 – Isabela Martins Oct 21 '17 at 16:31
9

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.

| improve this answer | |
  • 1
    I tried the "-99\r" and it worked! That was it. Thank you very much!! – Isabela Martins Oct 21 '17 at 16:42
2
awk -F, '{if($7==-99)print $0}'

will do that...

| improve this answer | |
  • 2
    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. – Kusalananda Oct 21 '17 at 16:31
  • Empty output... – Isabela Martins Oct 21 '17 at 16:35
0

A slight modification to @tonioc's answer

awk '{if($7 == -99){print}}' file > outfile
| improve this answer | |
0

a little late to the party.. but this should do it

awk -F, '$7 ~ /-99/' input.txt > output.txt

your original would also work if you added quotes, like so

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

The default action when using a pattern match is to print, so {print} wont be needed

| improve this answer | |
0
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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.