Extract column data from csv file based on row values

Question

I am trying to use awk/sed to extract specific column data based on row values. My actual files have 15 columns and over 1,000 rows (From a .csv file.)

Simple EXAMPLE: Input; a cdv file with a total of 5 columns and 100 rows. Output; data from column 2 through 5 based on specific row values from column 2. (I have a specific list of the row values I want the operator to filter out. The values are numbers.)

File looks like this:

"Date","IdNo","Color","Height","Education"
"06/02/16","7438","Red","54","4"
"06/02/16","7439","Yellow","57","3"
"06/03/16","7500","Red","55","3"

Recently Tried in AWK:

#!/usr/bin/awk -f 
#I need to extract a full line when column 2 has a specific 5 digit    value
awk '\
BEGIN { awk -F "," \    
{
    if ( $2 == "19650" ) { \
       {print $1 "," $6} \ 
}
exit }     

chmod u+x PPMDfUN.AWK

The operator response:

/var/folders/1_/drk_nwld48bb0vfvdm_d9n0h0000gq/T/PPMDfUN-     489939602.998.AWK.command ; exit;
/usr/bin/awk: syntax error at source line 3 source file    /private/var/folders/1_/drk_nwld48bb0vfvdm_d9n0h0000gq/T/PPMDfUN-   489939602.997.AWK
context is
awk >>>  ' <<< 
/usr/bin/awk: bailing out at source line 17
logout

Output Example: I want full row lines based if column 2 equals 7439 & 7500.

“Date","IdNo","Color","Height","Education"
"06/02/16","7439","Yellow","57","3"
"06/03/16","7500","Red","55","3"

Your question currently is very unclear. edit it to provide a minimal reproducible example including concise, testable sample input and expected output plus what you have attempted so far. Also make sure your example is accurate, e.g. does the 3rd line of your input really contain '7439',Yellow' (no single quote before Yellow')? — Ed Morton
– Ed Morton, Commented Jul 9, 2016 at 8:16
Your file is comma delimited, '\t' is for tab. Also, the fields are single quoted, not double. Finally, 19650 is a five digit number not four. — karakfa
– karakfa, Commented Jul 10, 2016 at 2:45
The syntax error is because you used some weird curly character (“) instead of the double quotes character (") at the start of the assignment -F “\t" so awk didn't know what to do with it. You also have some other weird curly character (’) interlaced with the single quotes (') in your sample input. In light of this and what @karakfa pointed out previously, edit your question so it makes sense and uses consistent, correct characters so you can help us to understand what you want so we can start to help you with a solution rather than still being on How to Ask the question. — Ed Morton
– Ed Morton, Commented Jul 10, 2016 at 9:41

karakfa · Accepted Answer · 2016-07-12 01:31:23Z

0

here you go...

 $ awk -F, -v q='"' '$2==q"7439"q' file

"06/02/16","7439","Yellow","57","3"

There is not much to explain, other than convenience variable q defined for double quotes helps to eliminate escaping.

edited Jul 12, 2016 at 1:31

answered Jul 11, 2016 at 18:15

karakfa

67.8k8 gold badges45 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BMinnie Over a year ago

This worked, thank you. Now, I have to get to print to an out_put_file (preferably .csv)

Will Over a year ago

While code often speaks for itself, it's good to add some explanation to your code. This popped up in the review queue, as code-only answers tend to.

Claes Wikner · Accepted Answer · 2016-07-12 20:39:56Z

0

awk -F, 'NR<2;$2~/7439|7500/' file
"Date","IdNo","Color","Height","Education"
"06/02/16","7439","Yellow","57","3"
"06/03/16","7500","Red","55","3"

answered Jul 12, 2016 at 20:39

Claes Wikner

1,5271 gold badge9 silver badges8 bronze badges

Collectives™ on Stack Overflow

Extract column data from csv file based on row values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related