Using AWK to select rows with specific value in specific column

The name of the pictureThe name of the pictureThe name of the pictureClash Royale CLAN TAG#URR8PPP











up vote
2
down vote

favorite












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.







share|improve this question


















  • 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














up vote
2
down vote

favorite












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.







share|improve this question


















  • 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












up vote
2
down vote

favorite









up vote
2
down vote

favorite











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.







share|improve this 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.









share|improve this question













share|improve this question




share|improve this question








edited Oct 21 '17 at 16:01









αғsнιη

15.6k92563




15.6k92563










asked Oct 21 '17 at 15:32









Isabela Martins

1313




1313







  • 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












  • 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







3




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




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




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




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




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




My version is 1.2
– Isabela Martins
Oct 21 '17 at 16:31










3 Answers
3






active

oldest

votes

















up vote
2
down vote



accepted










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 == "-99r"' 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.






share|improve this answer






















  • I tried the "-99r" and it worked! That was it. Thank you very much!!
    – Isabela Martins
    Oct 21 '17 at 16:42

















up vote
1
down vote













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


will do that...






share|improve this answer
















  • 1




    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

















up vote
0
down vote













A slight modification to @tonioc's answer



awk 'if($7 == -99)print' file > outfile





share|improve this answer




















    Your Answer







    StackExchange.ready(function()
    var channelOptions =
    tags: "".split(" "),
    id: "106"
    ;
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function()
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled)
    StackExchange.using("snippets", function()
    createEditor();
    );

    else
    createEditor();

    );

    function createEditor()
    StackExchange.prepareEditor(
    heartbeatType: 'answer',
    convertImagesToLinks: false,
    noModals: false,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: null,
    bindNavPrevention: true,
    postfix: "",
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    );



    );













     

    draft saved


    draft discarded


















    StackExchange.ready(
    function ()
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f399560%2fusing-awk-to-select-rows-with-specific-value-in-specific-column%23new-answer', 'question_page');

    );

    Post as a guest






























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes








    up vote
    2
    down vote



    accepted










    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 == "-99r"' 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.






    share|improve this answer






















    • I tried the "-99r" and it worked! That was it. Thank you very much!!
      – Isabela Martins
      Oct 21 '17 at 16:42














    up vote
    2
    down vote



    accepted










    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 == "-99r"' 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.






    share|improve this answer






















    • I tried the "-99r" and it worked! That was it. Thank you very much!!
      – Isabela Martins
      Oct 21 '17 at 16:42












    up vote
    2
    down vote



    accepted







    up vote
    2
    down vote



    accepted






    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 == "-99r"' 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.






    share|improve this answer














    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 == "-99r"' 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.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Oct 21 '17 at 16:43

























    answered Oct 21 '17 at 16:34









    Kusalananda

    105k14209326




    105k14209326











    • I tried the "-99r" and it worked! That was it. Thank you very much!!
      – Isabela Martins
      Oct 21 '17 at 16:42
















    • I tried the "-99r" and it worked! That was it. Thank you very much!!
      – Isabela Martins
      Oct 21 '17 at 16:42















    I tried the "-99r" and it worked! That was it. Thank you very much!!
    – Isabela Martins
    Oct 21 '17 at 16:42




    I tried the "-99r" and it worked! That was it. Thank you very much!!
    – Isabela Martins
    Oct 21 '17 at 16:42












    up vote
    1
    down vote













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


    will do that...






    share|improve this answer
















    • 1




      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














    up vote
    1
    down vote













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


    will do that...






    share|improve this answer
















    • 1




      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












    up vote
    1
    down vote










    up vote
    1
    down vote









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


    will do that...






    share|improve this answer












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


    will do that...







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Oct 21 '17 at 16:20









    tonioc

    1,12457




    1,12457







    • 1




      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












    • 1




      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







    1




    1




    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




    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




    Empty output...
    – Isabela Martins
    Oct 21 '17 at 16:35










    up vote
    0
    down vote













    A slight modification to @tonioc's answer



    awk 'if($7 == -99)print' file > outfile





    share|improve this answer
























      up vote
      0
      down vote













      A slight modification to @tonioc's answer



      awk 'if($7 == -99)print' file > outfile





      share|improve this answer






















        up vote
        0
        down vote










        up vote
        0
        down vote









        A slight modification to @tonioc's answer



        awk 'if($7 == -99)print' file > outfile





        share|improve this answer












        A slight modification to @tonioc's answer



        awk 'if($7 == -99)print' file > outfile






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Apr 27 at 9:46









        Lakshmi KrishnaKumaar

        1




        1



























             

            draft saved


            draft discarded















































             


            draft saved


            draft discarded














            StackExchange.ready(
            function ()
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2funix.stackexchange.com%2fquestions%2f399560%2fusing-awk-to-select-rows-with-specific-value-in-specific-column%23new-answer', 'question_page');

            );

            Post as a guest













































































            Popular posts from this blog

            How to check contact read email or not when send email to Individual?

            Displaying single band from multi-band raster using QGIS

            How many registers does an x86_64 CPU actually have?